【SQL】查詢資料的方式 (二)
如果表查詢中所有的列都包括在了索引中，就有可能使用FFS，當然也可以使用hint來選擇FFS。下面看看使用不同方式訪問索引的例子：
SQL> create index idx_un_tset_ownid_dataid on un_test (owner,data_object_id);
Index created.
Elapsed: 00:00:00.96
SQL> select owner ,data_object_id from un_test
2 where data_object_id >5;
6891 rows selected.
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 287826149
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6913 | 55304 | 32 (4)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_UN_TSET_OWNID_DATAID | 6913 | 55304 | 32 (4)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DATA_OBJECT_ID">5)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
602 consistent gets
135 physical reads
0 redo size
..........
0 sorts (memory)
0 sorts (disk)
6891 rows processed
SQL> select owner ,data_object_id from un_test
2 where data_object_id =5;
no rows selected
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1786681723
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 21 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IDX_UN_TSET_OWNID_DATAID | 1 | 8 | 21 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DATA_OBJECT_ID"=5)
filter("DATA_OBJECT_ID"=5)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
。。。。。。
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select object_id from un_test where object_id = 5;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 333614268
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| I_TEST_UNI | 1 | 5 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------
1 - access("OBJECT_ID"=5)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
。。。。。
0 sorts (disk)
1 rows processed
SQL> select /*+ index_ffs(a)*/ owner,data_object_id from un_test a
2 where wner = 'SYS';
23021 rows selected.
Elapsed: 00:00:00.12
Execution Plan
----------------------------------------------------------
Plan hash value: 287826149
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2668 | 21344 | 32 (4)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_UN_TSET_OWNID_DATAID | 2668 | 21344 | 32 (4)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1674 consistent gets
0 physical reads
。。。。。。。
0 sorts (memory)
0 sorts (disk)
23021 rows processed
----------------
SQL> SELECT /* INDEX(A)*/ OWNER,DATA_OBJECT_ID FROM UN_TEST A
2 WHERE WNER ='SYS';
23021 rows selected.
Elapsed: 00:00:00.12
Execution Plan
----------------------------------------------------------
Plan hash value: 3998526102
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2668 | 21344 | 8 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_UN_TSET_OWNID_DATAID | 2668 | 21344 | 8 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1586 consistent gets
0 physical reads
。。。。。
0 sorts (disk)
23021 rows processed
----------------------------
SQL> select /*+ index (a idx_un_test_ownid_dataid)*/ count(*) from un_test a
2 where data_object_id = 5;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1636324484
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 21 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | INDEX SKIP SCAN| IDX_UN_TSET_OWNID_DATAID | 1 | 2 | 21 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DATA_OBJECT_ID"=5)
filter("DATA_OBJECT_ID"=5)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
。。。。。
0 sorts (disk)
1 rows processed
---點陣圖索引。。
SQL> select /*+ index(a idx_type_bit)*/ * from un_test a
2 where a.object_type ='TABLE';
2921 rows selected.
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 4191918535
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1779 | 161K| 194 (0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID | UN_TEST | 1779 | 161K| 194 (0)| 00:00:03 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | IDX_TYPE_BIT | | | | |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
347 consistent gets
1 physical reads
0 sorts (memory)
0 sorts (disk)
2921 rows processed
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-673844/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle資料庫sql查詢檢視第二次查詢很慢Oracle資料庫SQL
- SQL Server 跨資料庫查詢SQLServer資料庫
- SQL Server實戰四：查詢資料庫的資料SQLServer資料庫
- Android SQL資料庫查詢方法 query( )AndroidSQL資料庫
- SQL查詢的：子查詢和多表查詢SQL
- SQL Server中Table字典資料的查詢SQL示例程式碼SQLServer
- SQL的資料庫操作：新增、更新、刪除、查詢SQL資料庫
- [SQL Server玩轉Python] 二.T-SQL查詢表格值及Python實現資料分析SQLServerPython
- SQL Server 查詢資料庫中所有表資料條數SQLServer資料庫
- MySQL資料庫：7、SQL常用查詢語句MySql資料庫
- ElasticSearch - 分頁查詢方式二 【scroll】滾動查詢（kibana、Java示例）ElasticsearchJava
- CDA資料分析師 - SQL資料庫基礎 查詢&連線SQL資料庫
- MySQL關於根據日期查詢資料的sql語句MySql
- SQL server資料庫with as子句與遞迴查詢的實現SQLServer資料庫遞迴
- SQL Server 資料庫查詢死鎖的處理步驟SQLServer資料庫
- 關係型資料庫查詢語言 SQL 和圖資料庫查詢語言 nGQL 對比資料庫SQL
- SQL Server資料庫————模糊查詢和聚合函式SQLServer資料庫函式
- SQL server資料庫表碎片比例查詢語句SQLServer資料庫
- Query.js - 類SQL前端資料查詢類庫JSSQL前端
- 概括SQL Server實時查詢Oracle資料庫WSSQLServerOracle資料庫
- go 連線資料庫 - GORM學習 - sql查詢Go資料庫ORMSQL
- 樹狀資料結構儲存方式——查詢篇資料結構
- 使用sql語句查詢平均值,使用sql語句查詢資料總條數, not in 篩選語句的使用SQL
- Flask——資料的查詢Flask
- 原生SQL查詢SQL
- SQL--查詢SQL
- SQL 聚合查詢SQL
- SSH：hiberate實現資料的查詢（單查詢和全查詢）
- MySQL - 資料查詢 - 簡單查詢MySql
- 一條SQL完成跨資料庫例項Join查詢SQL資料庫
- Mysql 資料庫 -------- SQL語句進階查詢 ------- 前部分MySql資料庫
- JAVA 中使用 SQL 語句查詢 EXCEL 檔案資料JavaSQLExcel
- YonBuilder低程式碼實戰：YonQL資料查詢小Case，讓SQL查詢變簡單UISQL
- 查詢——二分查詢
- ElasticSearch的查詢（二）Elasticsearch
- Microsoft SQL Server 2005中查詢優化器使用的統計資訊二(zt)ROSSQLServer優化
- Java ——MongDB 插入資料、 模糊查詢、in查詢Java
- 資料庫高階查詢之子查詢資料庫
- SQL的基礎查詢案例SQL