oracle 實現基于函數的索引

使用場景：
當一個查詢運行很慢。通過檢查where子句，發現其中的一列應用了sql lower函數，lower函數
阻止使用該列上現有的索引。你想要創建一個基于函數索引來支持這個查詢，如下

SQL> select index_name,column_name from user_ind_columns where table_name='T1';

INDEX_NAME                COLUMN_NAME
------------------------- ------------------------------
T1_PK                     OBJECT_ID

SQL> set autotrace trace explain;
SQL> select * from t1 where lower(object_name)='i_undo1';

執行計劃
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   908 |   101K|   436   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   908 |   101K|   436   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(LOWER("OBJECT_NAME")='i_undo1')
從以上可以看出即使該表中有索引也沒有使用。

解決方案
1、創建一個基于函數的索引
2、如果使用oracle database 11g或更高版本，創建一個索引虛擬列

下面實現基于函數的索引

創建索引可以通過以下方式來估計索引所使用空間以及所需要分配的空間

SQL> set serveroutput on
SQL> var used_bytes number;
SQL> var allo_bytes number;
SQL> exec dbms_space.create_index_cost('create index t1_object_name on t1(lower(object_name))',:used_bytes,:allo_bytes);
 
PL/SQL procedure successfully completed
used_bytes
---------
2269350
allo_bytes
---------
4194304

SQL>create index idx_lower on t1(lower(object_name)) tablespace index_nocompress;

SQL> select * from t1 where lower(object_name)='i_undo1';

執行計劃
----------------------------------------------------------
Plan hash value: 2274688371

-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |   908 |   101K|   193   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1        |   908 |   101K|   193   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_LOWER |   363 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(LOWER("OBJECT_NAME")='i_undo1')

注意：不能直接修改一個創建了基于函數索引的列。需要先刪除索引，然后修改列，最后再重建索引。不然會報ora-30556錯誤(在要修改的列上已定義函數索引或位圖聯接索引)

查看基于函數的索引定義dba/all/user_ind_expressions

SQL> select index_name,column_expression from user_ind_expressions;

INDEX_NAME                COLUMN_EXPRESSION
------------------------- --------------------------------------------------
IDX_LOWER                 LOWER("OBJECT_NAME")

接著實現在虛擬列創建一個索引

使用場景

現在正使用一個基于函數的索引，但想要獲得更好的性能，想將基于函數的索引替換為一個虛擬列，然后在虛擬列上創建索引（需要11g環境或更高版本）。

SQL>alter table t1 add(lower_object_name generated always as (lower(object_name)) virtual);
SQL>create index idx_lower on t1(lower_object_name) tablespace index_nocompress;