索引失效系列——隱式型別轉換

 

索引是我們進行最佳化的一種重要方式。實際工作中，一個簡單的索引，可能就會大大提升提高關鍵業務作業效率，最終提升使用者滿意度。在CBO時代，DBA和開發人員經常為索引為什麼不出現在執行計劃中而困惑。

 

問題提出

 

下面是一個模擬的開發場景。

 

//構建資料表

 

SQL> create table t as select * from dba_objects ;

 

Table created

 

SQL> create index idx_t_id on t(object_id);

 

Index created

 

SQL> create index idx_t_staus on t(status);

 

Index created

 

SQL> update t set status=to_char(length(owner));

 

51367 rows updated

 

SQL> commit;

 

Commit complete

 

SQL> desc t

Name           Type          Nullable Default Comments

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

（篇幅所限，有省略…）

SUBOBJECT_NAME VARCHAR2(30)  Y                        

OBJECT_ID      NUMBER        Y                        

STATUS         VARCHAR2(7)   Y                          

 

//資料分佈

SQL> select status, count(*) from t group by status;

 

STATUS    COUNT(*)

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

3            23655

6            24178

18               8

21             296

10              10

8              139

5             1358

7              787

14             381

2              554

4                1

 

 

下面我們執行一個簡單的select查詢，觀察執行計劃方案。

 

 

SQL> explain plan for select * from t where status=14;

 

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  |      |     1 |    89 |   161   (4)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |    89 |   161   (4)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   1 - filter(TO_NUMBER("STATUS")=14)

 

13 rows selected

 

SQL> rollback;

 

Rollback complete

 

注意這個實驗結果，我們在對應的status列上加入了索引，卻沒有執行索引路徑。

 

隱式型別轉換

 

此時，我們注意到實驗的select語句中，where條件“status=14”，而資料表上該列的型別為varchar2(7)。那麼，是不是這個原因引起的索引路徑計劃問題呢？

 

 

SQL> explain plan for select * from t where status='14';

 

Explained

 

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1853254432

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

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

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

|   0 | SELECT STATEMENT            |             |   324 | 28836 |    11   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |   324 | 28836 |    11   (0)|

|*  2 |   INDEX RANGE SCAN          | IDX_T_STAUS |   324 |       |     2   (0)|

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

Predicate Information (identified by operation id):

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

   2 - access("STATUS"='14')

 

14 rows selected

 

SQL> rollback;

 

Rollback complete

 

 

進行簡單的SQL修改之後，我們發現執行計劃變化為我們希望的方式。原因是如何呢？在之前的執行計劃中，存在“1 - filter(TO_NUMBER("STATUS")=14)”的部分。這說明在進行條件搜尋的時候，Oracle發現型別不匹配，隱式的將資料列加入了一個to_number函式。這樣，Oracle就需要一個如函式索引的索引列來支援搜尋路徑，於是索引idx_t_status的搜尋成本就大大增加。經過試算，Oracle認為全表掃面的成本相對較低。

 

 

顯然，這種情況是我們開發人員不希望看到的。我們已經付出了成本來構建維護索引，對關鍵用例功能不能支援，應該是我們避免的。其實，解決的方案也很容易，就是注意細節。在where條件書寫的時候明確清楚屬性列型別，這樣就可以避免這種情況發生。

 

 

那麼，是不是發生型別轉換就一定不走索引呢？我們看下一個例子。

 

 

SQL> explain plan for select * from t where object_id='1000';

 

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            |          |     1 |    89 |     2   (0)| 00

|   1 |  TABLE ACCESS BY INDEX ROWID| T        |     1 |    89 |     2   (0)| 00

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

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID"=1000)

 

14 rows selected

 

SQL> rollback;

 

Rollback complete

 

例外出現了。Object_id型別為number，如果根據剛才我們的理論，where條件中出現的“object_id=’1000’”就不應該出現索引路徑。這個是怎麼回事呢？

 

我們觀察到搜尋的訪問條件“2 - access("OBJECT_ID"=1000)”，說明語句生成執行計劃的時候，輸入條件已經轉化為數字型別1000。所以生成的執行計劃是不會被隱式型別轉化所困擾。

 

那麼，筆者猜想是在Oracle接受到查詢語句之後，會有一個SQL改寫的過程。在其中根據一些規則條件，對SQL進行改寫最佳化。當Oracle發現這樣簡單的隱式型別轉化後，會自主的將字串1000轉化為型別匹配的數字型別1000。這個例子就告訴我們，一些簡單的隱式型別轉化也是會走索引的。

 

 

最後要說一下發生隱式型別轉化的開發場景。在開發中，通常我們要避免出現隱式型別轉換，要把SQL語句的細節準備好。無論是前端程式碼開發，還是後臺大作業編寫，都要把握好型別匹配的情況。避免出現潛在的效能風險。