深入理解Oracle索引(2)：INDEX UNIQUE SCAN，INDEX FULL SCAN和INDEX FAST FULL SCAN

        ㈠ INDEX UNIQUE SCAN
       
        如果表上有唯一索引, 搜尋索引列時會用上INDEX UNIQUE SCAN
       
       原來Index Unique Scan和Index Range Scan在B Tree上的搜尋路徑是一樣的
       只是Index Unique Scan在找到應該含有要找的Index Key的block後便停止了搜尋，因為該鍵是唯一的
       而Index Range Scan還要循著指標繼續找下去直到條件不滿足時
       
       Oracle9i Database Performance Tuning Guide and Reference提到：
       This access path is used when all columns of a unique (B-tree) index are specified with equality conditions

       下面測試一下這句話的真實性：  

hr@ORCL> create table t (id number,name varchar2(10));hr@ORCL> create unique index ind_t on t (id);hr@ORCL> insert into t values(1,'a');hr@ORCL> insert into t values(2,'b');hr@ORCL> commit;hr@ORCL> set autot trace exphr@ORCL> select * from t where id=1;Execution Plan----------------------------------------------------------Plan hash value: 1366100657-------------------------------------------------------------------------------------| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |       |     1 |    20 |     1   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    20 |     1   (0)| 00:00:01 ||*  2 |   INDEX UNIQUE SCAN         | IND_T |     1 |       |     1   (0)| 00:00:01 |-------------------------------------------------------------------------------------hr@ORCL> drop index ind_t;hr@ORCL> create index ind_t on t (id,name);hr@ORCL> analyze index ind_t compute statistics；hr@ORCL> select * from t where id=1;Execution Plan----------------------------------------------------------Plan hash value: 3131770069--------------------------------------------------------------------------| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT |       |     1 |    20 |     1   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| IND_T |     1 |    20 |     1   (0)| 00:00:01 |--------------------------------------------------------------------------

       由此，B-Tree結構的unique index上的所有索引列都要被指定時，才會使用index unique scan來訪問

       ㈡ INDEX FULL SCAN和 INDEX FAST  FULL SCAN
          
          在一句SQL中，如果我們想搜尋的列都包含在索引裡面的話
          那麼Index Full Scan 和 Index Fast Full Scan 都可以被採用代替Full Table Scan
          有索引排序的時候,最佳化器可能會偏向於Index Full Scan
          
          如果Select 列表中資料都可來自於索引中包含的欄位，則通常容易選擇Index Fast Full Scan
          這樣出來的資料是根據索引 的 extent 為單元，無順序的
          掃描block包含了所有枝節點，不區分是否葉子節點。可以做多塊順序掃描，一個io 包含多個block
          
          Index Full Scan 是根據索引的葉子節點順序擷取葉子節點資訊然後獲得資料（通常還去表中擷取資料）
          這樣出來的資料是有序的，並且除了定位第一個葉子節點走了根和枝節點block外
          其他葉子節點只由上一個葉子節點的next 指標獲得，這樣一個索引葉子block就可產生一個io，是離散讀
          
          10g在Index Full Scan階段就把不符合記錄的資料先給過濾掉了,最後只有合格rowid做回表
          而9i下,Oracle就比較傻了,Index Full Scan的階段實際上什麼也沒做
          只是把所有的rowid都給得到,然後根據得到的rowid從表中取出資料,再過濾掉不合格記錄
          
          INDEX_FFS使用多塊讀，所以在大部分時候,INDEX_FFS速度應該會比單塊讀的INDEX_FS要快
          
          Index Full Scan將會按邏輯的順序訪問各個索引節點，結果集已經按照索引索引值順序排序，不需要單獨排序
          Index Fast Full Scan時索引塊通過多塊進行讀取，結果集是無序的
          所以，如果結果集需要排序那會使用INDEX_FS,如果結果集不需要排序則會使用INDEX_FFS
         
          
          INDEX FULL SCAN  
          HINT寫法：INDEX(表名 索引名)或者 INDEX_FS(表名 索引名）
          原理：Oracle定位到索引的ROOT BLOCK，然後到BRANCH BLOCK（如果有的話），再定位到第一個LEAF BLOCK
                    然後根據LEAF BLOCK的雙向鏈表順序讀取。它所讀取的塊都是有順序的，也是經過排序的
          
          
          INDEX FAST FULL SCAN    
          HINT寫法：INDEX_FFS(表名 索引名）
          原理：從段頭開始，讀取包含位元影像塊，ROOT BLOCK，所有的BRANCH BLOCK，LEAF BLOCK
                     讀取的順序完全有實體儲存體位置決定，並採取多塊讀，每次讀取DB_FILE_MULTIBLOCK_READ_COUNT個塊
                     IFFS的成本計算公式是：
                     leaf_blocks/k （k依賴於db_file_multiblock_read_count，leaf_blocks依賴於索引統計資訊）
                    查詢某個表記錄總數的時候，往往基於PRIMARY KEY的INDEX FAST FULL SCAN是最有效

sys@ORCL> select index_name,blevel,leaf_blocks from user_indexes where table_name=upper('T');INDEX_NAME                         BLEVEL LEAF_BLOCKS------------------------------ ---------- -----------IDX_T                                   1         282

          FAST是多塊讀，結果集無順序，如果需要排序則會多一步sort order by；FULL SCAN是單塊讀，有順序，可避免ORDER BY成本    
          Performance Tuning Guide中描述如下：
          Index Full Scan可以避免排序操作，在如下情況下，最佳化器將使用IFS
            1.若謂詞引用了索引中的欄位
            2.若查詢中的所有欄位都包含在索引中，並且索引欄位中至少有一個欄位非空
     
          Index Fast Full Scan
          查詢中的所有欄位都包含在索引中且至少一個索引列有非空約束的情況下，才會進行IFFS
          IFFS只訪問索引，不需要訪問資料表，通過讀取多個資料區塊的方式讀取整個索引，並不是根據索引的索引值進行排序
          如果加個ORDER BY，則FAST會變成IFS，如果索引中沒有完全包含要查詢的列，或可能INDEX FULL SCAN，或可能直接存取表
          IFFS通過讀取多個資料區塊的方式讀取整個索引，且可進行平行處理，相關資料並不是根據索引的索引值進行排序
          因此，速度比IFS快，但它不能用於避免排序操作