Oracle 19c中基於函式的索引

      Oracle的SQL語句中使用了函式的列可以建立索引，使用函式返回值查詢。我們可以使用一個函式或者表示式來建立一個基於函式的索引。基於函式的索引中，函式可以是Oracle內建函式，也可以是使用者自定義函式或表示式。自定義的函式每一次執行必須返回一致的值。

基於函式的索引的優點：

1、提前計算結果提升查詢效能。資料庫將提前計算涉及一列或多列的函式的值，並把該值儲存在建立的基於函式的索引中。因此查詢可以透過索引來查詢預先計算的值，而不是在查詢時運輸函式或表示式的值，所以這個所以可以提升查詢效能。

2、基於函式的索引在多數情況下使最佳化器有更多可能使用更有效的索引範圍掃描，而不使用代價高的全表掃描。

3、如確認建立基於函式的索引可以大幅提升查詢效能，應用程式邏輯和程式碼不需要修改，對應用是透明的。

下面我們看一個例子

1、執行一個查詢語句 where條件後面指定upper(last_name)

SQL> explain plan for select first_name,last_name,phone_number
from employees
where UPPER(last_name) = UPPER('xiaofei');  
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    30 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    30 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("LAST_NAME")='XIAOFEI')
13 rows selected.

2、檢查該列上是否有索引

SQL> select index_name,column_name from user_ind_columns
  2   where table_name='EMPLOYEES';
INDEX_NAME           COLUMN_NAME
-------------------- --------------------
EMP_EMAIL_UK         EMAIL
EMP_EMP_ID_PK        EMPLOYEE_ID
EMP_DEPARTMENT_IX    DEPARTMENT_ID
EMP_NAME_IX          FIRST_NAME
EMP_JOB_IX           JOB_ID
EMP_MANAGER_IX       MANAGER_ID
EMP_NAME_IX          LAST_NAME
7 rows selected.
SQL>

       從以上執行計劃可以看到，最佳化器沒有使用LAST_NAME列上的索引，執行了全表掃描。如謂語在索引列上使用了函式，則最佳化器不會選擇該列上的索引。

雖然LAST_NAME列上有索引，但是最佳化器沒有選擇，是因為上面SQL語句中是以UPPER(LAST_NAME)作為的查詢條件，而不是以LAST_NAME為查詢條件。

如果讓查詢使用索引，就需要在查詢條件表示式上建立索引，即基於函式的索引。

3、在upper(last_name)列建立一個基於函式的索引

SQL> create index emp_up_name on employees (upper(last_name));
Index created.
SQL> explain plan for select first_name,last_name,phone_number
from employees
where UPPER(last_name) = UPPER('xiaofei');  
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1395557483
---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |     1 |    44 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES   |     1 |    44 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_UP_NAME |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(UPPER("LAST_NAME")='XIAODFEI')
14 rows selected.
SQL>

建立基於函式索引後，我們再次執行查詢語句，資料庫將使用基於函式的索引進行查詢，執行計劃 INDEX RANGE SCAN 。

4、檢視基於函式的索引

SQL> select index_name,column_expression from user_ind_expressions;
INDEX_NAME           COLUMN_EXPRESSION
-------------------- --------------------------------------------------------------------------------
EMP_UP_NAME          UPPER("LAST_NAME")
SQL>

summary：基於函式的索引可以是B樹索引，唯一索引或點陣圖索引。基於函式的索引和虛擬索引可以結合使用。有些場景及時建立基於函式的索引，最佳化器仍然會走全表掃描；基於函式的索引在早期部分版本會出現錯誤結果集。

-the end-