oracle函數索引小結

函數索引的概念：
基於函數的索引也是8i以來的新產物，它有索引計算資料行的能力，它便於使用並且提供計算好的值，在不修改應用程式的邏輯上提高了查詢效能。使用基於函數的索引有幾個先決條件：

(1)
必須擁有QUERY REWRITE（本模式下）或GLOBAL QUERY REWRITE（其他模式下）許可權。
(2)
必須使用基於成本的最佳化器，基於規則的最佳化器將被忽略。
(3)
必須設定以下兩個系統參數：

1. QUERY_REWRITE_ENABLED=TRUE
2. QUERY_REWRITE_INTEGRITY=TRUSTED

可以通過alter system set,alter session set在系統級或線程級設定，也可以通過在init.ora添加實現。

這裡舉一個基於函數的索引的例子：

函數索引建立及資料準備：

SQL> create index test.ind_fun on test.testindex(upper(a));

索引已建立。

SQL> insert into testindex values('a',2);

已建立 1 行。

SQL> commit;

提交完成。

強制使用基於規則的最佳化器，所以不會使用函數索引：

SQL> select /*+ RULE*/*  FROM test.testindex where upper(a)='A';
A　　     B
-- ----------
a　　     2
Execution Plan
----------------------------------------------------------
0　　SELECT STATEMENT Optimizer=HINT: RULE
1    0   TABLE ACCESS (FULL) OF 'TESTINDEX'

(最佳化器選擇了全表掃描)
--------------------------------------------------------------------

使用函數索引：

SQL> select *  FROM test.testindex where upper(a)='A';
A　　     B
-- ----------
a　　     2
Execution Plan
----------------------------------------------------------
0　　SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=2 Card=1 Bytes=5)
2    1     INDEX (RANGE SCAN) OF 'IND_FUN' (NON-UNIQUE) (Cost=1 Card=1)
(使用了ind_fun索引)

此處有一種方案，不使用函數索引，通過sql上的技巧來使用原有列上的索引。不過我覺得還不如建立函數索引來的方便。