基于函數的索引

以下內容摘自《Oracle SQL 高級編程》 第12.4.2章節-基于函數的索引[其中代碼部分被修改,原始請參考書籍]

如果一個謂語在索引列上應用了函數，則優化器不會選用該列上的索引。例如，對于謂語to_char(CYRQ, 'YYYY-MM-DD') = '2014-01-21',不會選用CYRQ列上的索引,因為在索引列上應用了to_char函數。這個限制可以通過表達式to_char(CYRQ)在創建基于函數的索引來克服。基于函數的索引預存函數的結果。謂語中所聲明的表達式必須基于函數的索引所聲明的表達式想匹配。

    基于函數的索引也可以建立在用戶自定義函數上，但這個函數必須定義為確定性函數，也就是說對于這個函數的每一次執行必須返回一致的值。不遵守這一規則的用戶自定義函數不能用來創建基于函數的索引。

    在代碼清單12-14中，SELECT 語句使用to_char(CYRQ, 'YYYY-MM-DD') = '2014-01-21'子句來訪問CK10_GHDJ表。如果沒有基于函數的索引，優化器會選擇全表掃描訪問計劃。通過表達式to_char(CYRQ, 'YYYY-MM-DD')增加了基于函數的索引INDEX_CK10_GHDJ_CYRQ2之后，優化器就為該SELECT語句選用了基于索引的訪問路徑。

CREATE INDEX INDEX_CK10_GHDJ_CYRQ2 ON CK10_GHDJ(TO_CHAR(CYRQ,'YYYY-MM-DD'));

SELECT COUNT(0)
  FROM CK10_GHDJ G
 WHERE TO_CHAR(G.CYRQ, 'YYYY-MM-DD') = '2014-01-21';

注意代碼清單12-14中最后所打印出來的訪問謂語“SYS_NC00009$”=’1000’。關于基于函數索引的一些實現上的細節列于代碼清單12-15。基于函數的索引加入了一個虛擬列，所聲明的表達式值作為默認值，然后在這個虛擬列上建立索引。這個虛擬列可從dba_tab_cols視圖中可見，并且dba_tab_cols.data_default列顯示了用來填充虛擬列的表達式。進一步的dba_ind_columns視圖顯示對虛擬列進行了索引。

SELECT DATA_DEFAULT, HIDDEN_COLUMN, VIRTUAL_COLUMN
  FROM DBA_TAB_COLS
 WHERE TABLE_NAME = 'CK10_GHDJ'
   AND VIRTUAL_COLUMN = 'YES';

    在增加了基于函數的索引后收集表的統計信息是很重要的。如果不收集，新的虛擬列就沒有統計信息，這有可能會導致性能異常。腳本analyze_table_sfp.sql被用來收集表的統計信息并設置cascade=>true。代碼12-16給出了analyze_talbe_sfp.sql腳本的內容。

代碼清單12-16 Analyze_table_sfp.sql腳本

begin
  dbms_stats.gather_table_stats(ownname          => user,
                                tabname          => 'CK10_GHDJ',
                                estimate_percent => 30,
                                cascade          => true);
end;
/

         基于函數的索引也可以顯示使用虛擬列來實現。在這個虛擬列上也可以增加索引。這種方法額外的好處就是你還可以使用虛擬列作為分區鍵來應用分區方案。在代碼清單12-17中，使用virtual 關鍵字在表中加入了一個新的虛擬列cyrq_char。然后在cyrq_char列上建立了全局分區索引。SELECT語句的執行計劃顯示表使用新建的索引來訪問，并且謂語to_char(CYRQ, 'YYYY-MM-DD') = '2014-01-21'被重寫為謂語cyrq_char=’2014-01-21’以使用虛擬列。