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 readHINT: INDEX(表名/別名 索引名)INDEX FAST FULL SCAN 索引快速全掃描。多塊讀 。當SQL要查詢的數據能夠完全從索引中獲得，那么Oracle就不會走全表掃描了，就會走索引快速全掃描。索引快速全掃描類似全表掃描，它可以多塊 讀，并且可以并行掃描。等待事件：db file scattered readHINT：INDEX_FFS(表名/別名 索引名) 測試INDEX FAST FULL SCAN: create table test as select * from dba_objects;create index test_idx1 on test(object_name);BEGINDBMS_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

?

轉載于:https://www.cnblogs.com/zhaoyangjian724/p/3798084.html

總結

以上是生活随笔為你收集整理的为什么不走INDEX FAST FULL SCAN呢的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。