Oracle/Tuning INDEX 的建法

今天幫一個客戶調校DB時看到了下面的 TOP SQL

DISK_READS	EXECUTIONS	READS_ PER_EXEC	SQL_TEXT
512612961	137344	3732.32876	select * from FepHeader where DocNO=:"SYS_B_0"
221859622	59437	3732.6854	select * from FepHeader where UseOrgSeqNo=:"SYS_B_0" AND DocNO=:"SYS_B_1"
211739049	56748	3731.21606	select * from FepHeader where UseOrgSeqNo=:"SYS_B_0" AND DocNO=:"SYS_B_1"
148596452	40358	3681.95778	SELECT FepSeqNO,DiFile FROM FepHeader WHERE UseOrgSeqNO=:"SYS_B_0" and DocNO=:"SYS_B_1"

很明顯的是沒有建立應有的索引, 查了一下果然只有 P.K 而已 (由UseOrgSeqNO 以及另一個欄位組成)  我就把它加了一個索引 : index1 (由 DocNO 及 UseOrgSeqNO 組成)

這裡要特別注意的是, AP有時會 "只用"  DocNo 做查詢的條件, 所以 DocNo 最好放在前面, 以因應在這時候還可以跑 Index Range Scan , 而不用跑 Full Table Scan , 也不需用提示做 Full Index Scan

因為 USER 的環境是 Oracle 9i on Solaris , 所以理論上只要用採用 Cost Base Optimizer , 它就會使用 Index Skip Scan 效果會比  Full Index Scan 還好, 如果在沒有統計資料的情形下, 可以使用 hint, 例如 : select /*+ index_ss (Tab1 Ind1) */ ...

這時還有一點時間, 我測試建立兩個 index , 分別各用一個欄位, 這時 Oracle 9i 預設會使用 and_equal 結合兩個 index 的傳回值, 我再改用 hint  /*+ index_combine (tab1 ind1 ind2) */ 發現效果會更好 .

不要用客戶的資料, 改用自己的測試資料

create table t1 as select * from dba_objects ;

select count(*) from t1;

  COUNT(*)
----------
    973280

將近有一百萬筆的記錄, 先使用複合索引, 效果超級強

create index i1 on t1 (owner, object_type) ;

select count(*) from t1 where owner = 'PERFSTAT' and object_type = 'INDEX' ;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets


但 where 條件中少了 leading column 後

select count(*) from t1 where owner = 'PERFSTAT' and object_type = 'INDEX' ;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T1'


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      13208  consistent gets

強迫使用 full index scan

select /*+ index(t1 i1) */ count(*) from t1 where object_type = 'INDEX' ;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1 Bytes=11)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FULL SCAN) OF 'I1' (NON-UNIQUE) (Cost=26 Card=107
          79 Bytes=118569)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3404  consistent gets

改用 index skip scan , 效果也超強

select /*+ index_ss(t1 i1) */ count(*) from t1 where object_type = 'INDEX'

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=1 Bytes=11)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (SKIP SCAN) OF 'I1' (NON-UNIQUE) (Cost=11 Card=338
           Bytes=3718)


Statistics
----------------------------------------------------------
         42  recursive calls
          0  db block gets
        173  consistent gets


改用單獨的兩個索引, 先做個複合查詢

drop index i1 ;
create index i1 on t1( owner) ;
create index i2 on t1 (object_type) ;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     AND-EQUAL
   3    2       INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE)
   4    2       INDEX (RANGE SCAN) OF 'I2' (NON-UNIQUE)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2585  consistent gets


再測試 index_combine, 效果真是好的沒話說. 但還是比複合式索引差了一大截

select /*+ index_combine (t1 i1 i2) */ count(*) from t1 where owner = 'PERFSTAT' and object_type = 'INDEX' ;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=28)
   1    0   SORT (AGGREGATE)
   2    1     BITMAP CONVERSION (COUNT)
   3    2       BITMAP AND
   4    3         BITMAP CONVERSION (FROM ROWIDS)
   5    4           INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE) (Cost=1)
   6    3         BITMAP CONVERSION (FROM ROWIDS)
   7    6           INDEX (RANGE SCAN) OF 'I2' (NON-UNIQUE) (Cost=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        114  consistent gets


再來只做 non-leading colume 查詢

select count(*) from t1 where owner = 'PERFSTAT' and object_type = 'INDEX' ;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'I2' (NON-UNIQUE)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        103  consistent gets



總結: 很多user不會使用  index skip scan (因為不是用 CBO也沒有 hint) 所以除了建立一個複合索外外, 又對 non-leading column 再加第二個索引, 浪費空間

Oracle 10g 之後又對 and_equal 停止改善 (定位如同 RBO一般) . 所以Oracle原廠文件建議使使 複合式索引  並搭配 CBO , 不要再使用兩個索引了, 要不然, 就使用 Hint : index_combine