为什么不走INDEX FAST FULL SCAN呢

时间:2021-08-28 10:43:45
 INDEX FULL SCAN 索引全扫描。单块读 。它扫描的结果是有序的,因为索引是有序的。它通常发生在
下面几种情况(注意:即使SQL满足以下情况 不一定会走索引全扫描)
1. SQL语句有order by选项,并且order by 的列都包含
在索引中,并且order by 后列顺序必须和索引列顺序一致。
2. 在进行SORT MERGE JOIN的时候,如果要查询的列通过索
引就能获得,那就不必进行全表扫描了,另外也避免了排
序,因为INDEX FULL SCAN返回的结果已经排序。
3. 当查询中有GROUP BY,并且GROUP BY 的列包含在索引中。
等待事件:db file sequential read
HINT: INDEX(表名/别名 索引名)
INDEX FAST FULL SCAN 索引快速全扫描。多块读 。当SQL要查询的数据能够完全从索引中获得,那么
Oracle就不会走全表扫描了,就会走索引快速全
扫描。索引快速全扫描类似全表扫描,它可以多块
读,并且可以并行扫描。
等待事件:db file scattered read
HINT:INDEX_FFS(表名/别名 索引名) 测试INDEX FAST FULL SCAN:
create table test as select * from dba_objects; create index test_idx1 on test(object_name); BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'HR',
tabname => 'TEST',
estimate_percent => 30,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END; SQL> select object_name from test where object_name is not null and owner='SYS'; Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020 --------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40056 | 1212K| 4689 (1)| 00:00:57 |
|* 1 | TABLE ACCESS FULL| TEST | 40056 | 1212K| 4689 (1)| 00:00:57 |
-------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter("OWNER"='SYS' AND "OBJECT_NAME" IS NOT NULL) 为什么不走INDEX FAST FULL SCAN呢? 因为test_idx1只存放了(object_name)的数据,但是没有存放OWNER的数据,索引无法过滤OWENR='SYS'部分数据,只能访问表来过滤。 create index test_idx2 on test(object_name,owner) SQL> select object_name from test where object_name is not null and owner='SYS'; Execution Plan
----------------------------------------------------------
Plan hash value: 2636476231 ----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40056 | 1212K| 1822 (1)| 00:00:22 |
|* 1 | INDEX FAST FULL SCAN| TEST_IDX2 | 40056 | 1212K| 1822 (1)| 00:00:22 |
---------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter("OWNER"='SYS' AND "OBJECT_NAME" IS NOT NULL 此时走INDEX FAST FULL SCAN