Oracle效能分析6：資料訪問方式之索引掃描，oracle索引

Oracle效能分析6：資料訪問方式之索引掃描，oracle索引

這節將介紹各種索引掃描方式，在瞭解了各種索引掃描方式的特點後，你就可以判斷你的執行計畫中使用的掃描方式是否正確，並可以針對擷取的資訊作出改進。

索引唯一掃描

在下面的情境中使用相等條件時，資料庫使用索引唯一掃描。
 1）查詢條件中包含唯一索引中的所有列時；
 2）查詢條件使用主鍵約束列時。
下面是一個實際的例子，在表historyalarm中建立如下唯一索引：

create unique index idx_historyalarm$queryid on historyalarm(queryid) tablespace uep4x_fm_index

然後在表上執行查詢：

select * from historyalarm where queryid = 3

該查詢符合上面的第一種情況，會使用索引唯一掃描，該查詢的執行計畫如下：

DESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS TABLE ACCESS BY GLOBAL INDEX ROWIDHISTORYALARM  INDEX UNIQUE SCANIDX_HISTORYALARM$QUERYID

這裡Oracle首先通過唯一索引掃描找到索引節點，然後使用索引節點中包含的rowid來訪問表中的資料。

索引範圍掃描

當查詢條件可能會返回一定範圍的資料時就會選用索引範圍掃描，索引可以是唯一索引或者不唯一索引，但如果查詢條件包含的資料範圍太大，也有可能導致全表掃描。查詢條件中使用<、>、LIKE、BETWEEN、=等都可能使用索引範圍掃描，需要注意單個=條件在唯一索引或者主鍵上將導致索引唯一掃描。
下面是一個索引範圍掃描的例子，在上面的histroyalarm中執行一個範圍查詢：

select * from caffm4x.historyalarm where queryid < 10

這裡查詢的資料是一個範圍，且使用了queryid列，在queryid列上有唯一索引，但任然會導致索引範圍掃描：

DESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS TABLE ACCESS BY GLOBAL INDEX ROWIDHISTORYALARM  INDEX RANGE SCANIDX_HISTORYALARM$QUERYID

索引範圍掃描從索引的根節點出發，找到第一個匹配的條目所在的葉子資料區塊開始遍曆索引結構，首先從索引條目中取出rowid然後取出對應的表資料區塊（通過rowid訪問資料表），接下來葉子索引塊會被再次訪問並讀取下一個索引條目並擷取rowid，這樣反覆直到整個葉子索引塊被的索引條目全部被讀出。因此排除索引根節點和中間節點，每行資料讀取需要讀取兩個資料區塊，我們可以通過blevel來得到索引高度，通過索引高度和擷取的資料行數就能得到需要讀取的資料區塊數，例如：如果blevel為3，讀取5行資料，則總的需要訪問的資料區塊次數將是（5*2) + 3 = 13（注意只有根節點時blevel為0）。
如果在讀取了整個葉子索引塊之後，還需要訪問下一個葉子索引塊，在當前的葉子索引塊中有指向下一個葉子索引塊的指標（也含有指向上一個葉子索引塊的指標）。
使用索引範圍掃描的另一個優勢就在於排序，由於索引的節點是有序的，因此如果查詢的結果需要按照索引列排序（升序或者降序），那麼使用索引範圍掃描則可以很好的避免排序操作，例如：

select * from historyalarm where queryid > 10

由於queryid大於10的資料量佔總資料量的99%，因此Oracle的最佳化器選擇了全表掃描：

DESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS PARTITION RANGE ALL  TABLE ACCESS FULLHISTORYALARM

如果我們在查詢時對資料指定排序，如下：

select * from historyalarm where queryid > 10 order by queryid

執行計畫如下：

DESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS TABLE ACCESS BY GLOBAL INDEX ROWIDHISTORYALARM  INDEX RANGE SCANIDX_HISTORYALARM$QUERYID

最佳化器改為使用了索引範圍掃描。由於當資料量很大時，排序的代價是很大的（可能導致物理排序），這時使用索引範圍掃描將是一個很好的選擇，特別是當你排序後選擇部分資料的情況下（rownum < n）。

索引全掃描

索引全掃描會讀取索引上的所有條目，下面幾種情況可能導致索引全掃描：

1）沒有條件但是所需擷取列的列表可以通過其中一列的索引來獲得；

select id from t3DESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS INDEX FULL SCANIDX_T3_ID

由於id列帶有索引，因此這裡最佳化器選擇了索引全掃描。

2）查詢條件中包含排序操作

select * from historyalarm order by queryidDESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS INDEX FULL SCANIDX_HISTORYALARM$QUERYID

索引全掃描讀取單個資料區塊，讀取每個條目的rowid，再通過rowid取出資料行，由於索引已經排序，所以不必執行排序操作。如果查詢只請求了索引列，資料庫將跳過表訪問，只通過訪問索引得到資料。
索引全掃描的另一個優勢在計算最大、最小值時：

select min(queryid) from historyalarm DESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS SORT AGGREGATE  INDEX FULL SCAN (MIN/MAX)IDX_HISTORYALARM$QUERYID

由於索引本身已經排序，因此在計算最大最小值時只需要很小的代價。

索引跳躍掃描

當查詢條件中帶有符合索引中的列，但是不包含前置列時，就可能導致索引跳躍掃描。資料庫將一個複合索引拆分為多個邏輯子索引，符合索引前置列的不同值決定邏輯子索引的數量，即前置列的不同值越少，索引跳躍式掃描的效能就越好。

select value from t3 where value = 'test'DESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS INDEX SKIP SCANIDX_T3_COMBINE

索引快速全掃描

當索引本身包含查詢中指定的所有列時，Oracle執行索引快速全掃描。索引快速全掃描和索引全掃描的區別在於：索引全掃描使用單塊讀操作，而索引快速全掃描使用多塊讀。這種掃描不能用於避免排序，因為資料區塊是通過無序的多塊讀取來讀取的。

select queryid from historyalarmDESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS INDEX FAST FULL SCANIDX_HISTORYALARM$QUERYID

 

 

Oracle資料庫表做表分析、索引分析的命令是什？

analyze table 表名 compute statistics
analyze index 索引ID compute statistics

如果想分析所有的表名和index名可以從視圖user_tables，user_indexes取得相關的資訊，自動產生SQL命令
 
Oracle中不使用索引的幾種情況分析

Oracle不使用b*tree索引的情況大致如下1：where條件中和null比較可能導致不使用索引2：count，sum，ave，max，min等聚集操作時可能導致不使用索引3：顯示或者隱式的函數轉換導致不使用索引4：在cbo模式下，統計資訊過於陳舊導致不使用索引5：複合式索引中沒有使用前置列導致沒有使用索引6：訪問的資料量超過一定的比例導致不使用索引下面就其中的幾點做一些說明一：Null可以使用索引嗎一般情況下，where條件中和null比較將會導致fulltablescan，實際上，如果table中索引建列的值都為null，那麼該行在索引（此處指b*tree，位元影像索引和聚簇索引可以有空值）中就不會存在，因此oracle為了保證查詢結構的準確性，就會用full table scan代替index scan，這樣理解，不走索引也就在情理之中。當然，如果某個索引列上有定義為not null，在這種情況下，不存在所有索引列都為空白的情況，所以此種情況下，是可以走index scan的，因此，對於where條件中含有類似is null，=null的情況，是否走索引，還是要看索引建中是否有某個列定義為not null。具體實驗如下：SQL> create table t(x char(3),y char(5));SQL> insert into t(x,y) values ('001','xxxxx');SQL> insert into t(x,y) values ('002',null);SQL> insert into t(x,y) values (null,'yyyyy');SQL> insert into t(x,y) values (null,null);SQL> commit;SQL> create unique index t_idx on t(x,y);SQL> analyze table t compute statistics for table for all indexes;SQL> select blevel,leaf_blocks,num_rows from user_indexes where index_name=upper('t_idx'); BLEVEL LEAF_BLOCKS NUM_ROWS---------- ----------- ---------- 0 1 3isnert四條記錄，但索引只儲存3條，最後一條沒有儲存在索引中SQL> set autotrace traceonly explain;SQL> select * from t where x is null;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=8) 1 0 TABLE ACCESS (FULL)OF 'T' (Cost=2 Card=1 Bytes=8) SQL> create table t1(x char(3),y char(5) not null);SQL> insert into t1(x,y) values ('001','xx......餘下全文>>