在實際應用中，當條件列使用函數運算進行數據匹配時，即使該列建立了索引，索引也不會被使用。
如下示例，其中在owner列上建立一個普通b-tree索引,觀察兩種查詢方式（不使用UPPER函數及使用UPPER函數）的執行計劃的區別。
--查看表上的數據分布情況，可以確定，對於索引列owner，針對scott以及bi的普通查詢，一定會使用索引。
Yumiko@Sunny >select owner,count(*) from test01 group by owner; OWNER COUNT(*) ------------------------------ ---------- SCOTT 11 BI 8 SYS 22909
--為owner列創建普通b-tree索引 Yumiko@Sunny >create index btree_owner on test01(owner); Index created.
--驗證創建的索引 Yumiko@Sunny >select a.INDEX_NAME INDEX_NAME,b.INDEX_TYPE INDEX_TYPE,a.TABLE_NAME TABLE_NAME,COLUMN_NAME,STATUS 2 from user_ind_columns a,user_indexes b 3 where a.INDEX_NAME=b.INDEX_NAME and a.table_name='TEST01'; INDEX_NAME INDEX_TYPE TABLE_NAME COLUMN_NAME STATUS --------------- --------------- --------------- -------------------- -------- BTREE_OWNER NORMAL TEST01 OWNER VALID --利用索引列，針對列值為BI，進行普通查詢
--與預想一樣，這裡用到了索引掃描 Yumiko@Sunny >select * from test01 where owner='BI'; 8 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 725909888 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 92 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST01 | 1 | 92 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | BTREE_OWNER | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"='BI') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 16 physical reads 0 redo size 2010 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 8 rows processed
--清空buffer_cache緩沖區，避免影響後續操作對於物理讀的觀察。 Yumiko@Sunny >alter system flush buffer_cache; System altered.
--使用UPPER函數進行條件過濾，並觀察執行計劃 --通過執行計劃，可以明顯看出，未使用索引掃描，進而導致大量的物理讀操作。
Yumiko@Sunny >select * from test01 where UPPER(owner)='BI'; 8 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 262542483 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 229 | 21068 | 158 (1)| 00:00:02 | |* 1 | TABLE ACCESS FULL| TEST01 | 229 | 21068 | 158 (1)| 00:00:02 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(UPPER("OWNER")='BI') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 697 consistent gets 692 physical reads 0 redo size 1583 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 8 rows processed
通過上面的示例可以看到，即使條件列建立了索引，當索引列上使用函數進行條件匹配，執行計劃將不會選擇索引掃描。
為了避免由於在條件匹配時引入函數，導致執行計劃不再使用索引，oracle提供了基於函數的索引，進而解決上述問題，提高訪問效率。
需要注意的是：
在使用函數索引時，SQL語句中的條件表達式必須與函數索引的表達式完全一致，空格、關鍵字大小寫的可以忽略。如果不完全一致，則無法利用函數索引。
緊接上面的例子，這裡針對上面示例中，條件出現的函數運算UPPER(owner)建立函數索引。
--在索引列上建立函數索引
Yumiko@Sunny >create index func_owner on test01(UPPER(owner)); Index created.
--查看並驗證建立的函數索引
--需要注意的，由於此索引是基於函數建立的，因此columns一列無法顯示真正的列名，可以通過user_ind_expressions視圖查看 Yumiko@Sunny >select a.INDEX_NAME INDEX_NAME,b.INDEX_TYPE INDEX_TYPE,a.TABLE_NAME TABLE_NAME,COLUMN_NAME,STATUS 2 from user_ind_columns a,user_indexes b 3 where a.INDEX_NAME=b.INDEX_NAME and a.table_name='TEST01'; INDEX_NAME INDEX_TYPE TABLE_NAME COLUMN_NAME STATUS --------------- ------------------------- --------------- -------------------- -------- FUNC_OWNER FUNCTION-BASED NORMAL TEST01 SYS_NC00014$ VALID BTREE_OWNER NORMAL TEST01 OWNER VALID Yumiko@Sunny >select * from user_ind_expressions where INDEX_NAME='FUNC_OWNER'; INDEX_NAME TABLE_NAME COLUMN_EXPRESSION COLUMN_POSITION --------------- --------------- ------------------------------ --------------- FUNC_OWNER TEST01 UPPER("OWNER") 1
--打開會話追蹤 Yumiko@Sunny >set autotrace trace;
--清空buffer_cache緩沖區 Yumiko@Sunny >alter system flush buffer_cache; System altered. --再次使用UPPER函數進行條件查詢，此時執行計劃使用索引掃描，進而物理讀明顯降低。 Yumiko@Sunny >select * from test01 where upper(owner)='BI'; 8 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 939299437 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 229 | 21068 | 19 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST01 | 229 | 21068 | 19 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | FUNC_OWNER | 92 | | 16 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(UPPER("OWNER")='BI') Statistics ---------------------------------------------------------- 32 recursive calls 0 db block gets 8 consistent gets 19 physical reads 0 redo size 1583 bytes sent via SQL*Net to client 469 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 8 rows processed
通過上面的示例可以看到，由於建立了函數索引，執行計劃重新選擇了索引掃描，物理讀（physical reads）明顯降低。
較為重要的oracle索引視圖如下：
dba_indexes
user_indexes
dba_ind_columns
user_indexes
dba_expressions
user_expressions
其中：
dba_indexes與user_indexes視圖，主要涵蓋了索引的參數、狀態以及關聯的表信息，但不包含具體的列信息。
dba_ind_columns與user_ind_columns視圖，主要涉及具體的索引列的信息。
dba_expressions與user_expressions視圖，主要針對函數索引，可以查看具體的函數信息。
