函式索引的使用細節——常數表示式

 

在筆者之前的Blog中，已經多次對函式索引Functional Index進行介紹。其中包括函式索引的原理、使用和應用場景。針對兩個使用細節，補充一下這個知識點。

 

 

SQL語句是一種描述性語言，我們只需要進行資料描述，資料庫系統DBMS就會生成執行計劃路徑和操作。同樣的資料操作需求，SQL語句書寫可能會有一定的差異。

 

DBMS的SQL引擎在處理SQL語句之前，通常都會進行一些改寫處理。如表示式計算、謂詞推進和檢視化等等。其中要注意表示式計算的一個特性：“改寫運算是不會跨過等號”的。下面透過一個例項來觀察：

 

首先還是環境準備：

 

 

SQL> desc t;

Name           Type          Nullable Default Comments

-------------- ------------- -------- ------- --------

OWNER          VARCHAR2(30)  Y                        

OBJECT_NAME    VARCHAR2(128) Y                         

SUBOBJECT_NAME VARCHAR2(30)  Y                        

OBJECT_ID      NUMBER        Y                        

（篇幅原因，無關內容省略……）

 

SQL> create index idx_t_id on t(object_id);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

 

我們先觀察條件“object_id<1000/5”的情況。

 

 

SQL> explain plan for select * from t where object_id<1000/5;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 514881935

--------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |   179 | 17363 |     7   (0)| 00

|   1 |  TABLE ACCESS BY INDEX ROWID| T        |   179 | 17363 |     7   (0)| 00

|*  2 |   INDEX RANGE SCAN          | IDX_T_ID |   179 |       |     2   (0)| 00

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"<200)

 

14 rows selected

 

 

注意上面實驗的兩個細節。首先，SQL引擎在處理語句形成執行計劃之前，1000/5的常數運算條件已經被處理計算。輸入到最佳化器中的SQL命令就是200。其次就是該SQL語句順利的執行了索引路徑。

 

下面我們對該SQL進行簡單的改寫，而且是等價運算改寫。

 

 

SQL> explain plan for select * from t where object_id*5<1000;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  3614 |   342K|   281   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T    |  3614 |   342K|   281   (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OBJECT_ID"*5<1000)

 

13 rows selected

 

 

數學上的等價運算並不意味著Oracle SQL最佳化方案的等價。從filter(“object_id*5<1000”)就可以看出，SQL改寫過程並沒有智慧的將5轉移到等號右邊。而是保留在左側。

 

隨著帶來的副作用是，原有的索引路徑不能使用，取代的是全表掃描。

 

解決該方法的思路有兩個，一個就是改寫原始碼，將“*5”變化為“/5”，該方法是上策。不會影響到原有的索引等最佳化方案。但是很多時候，DBA們是不能夠拿到和改寫原始碼的，這時候退而求其次的手段就是使用函式索引functional index。

 

函式索引functional index雖然主要針對的是函式，但是針對表示式也是有效的。

 

//新增函式索引

SQL> create index idx_t_fid on t(object_id*5);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

 

此時再次生成執行計劃。

 

 

SQL> explain plan for select * from t where object_id*5<1000;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1561469142

--------------------------------------------------------------------------------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |           |   179 | 18258 |     7   (0)| 0

|   1 |  TABLE ACCESS BY INDEX ROWID| T         |   179 | 18258 |     7   (0)| 0

|*  2 |   INDEX RANGE SCAN          | IDX_T_FID |   179 |       |     2   (0)| 0

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"*5<1000)

14 rows selected

 

 

問題得到了解決，訪問方式沒有變化，SQL語句相同。Oracle選擇了函式索引路徑執行。CPU成本從原來的281減少到了7，最佳化效果明顯。

 

根據Oracle最佳化文件中，對錶達式常量計算的解析如下：

 

 

Note: The optimizer does not simplify expressions across comparison operators: In the preceding examples, the optimizer does not simplify the third expression into the second. For this reason, application developers write conditions that compare columns with constants whenever possible, rather than conditions with expressions involving columns.

 

 

由於最佳化器對於表示式的簡化操作是不能跨過比較運算子（=，>，<等）。所以應用程式的開發者首先要保證書寫健康可最佳化的SQL語句，將常量運算儘可能處理掉。

 

對運維人員來說，如果遇到這類問題，很多時候函式索引可能是最好的可接受方案。

 

 

最後，還是重申一下筆者對函式索引的基本觀點。大多數應用函式索引的SQL場景，都可以在開發階段透過SQL改寫、欄位分割處理等方法加以迴避。從價效比角度看，函式索引不是一個很好的最佳化方案型別。絕大多數情況下的函式索引使用環節，是在運維領域。