Function Based Index 與 Cursor_sharing 的搭配

今天有人問我，為何他所建立的Function Based Index並沒有被使用,即便使用Hint也依然使用Full Table Scan
我向他要了相關的資訊後,使用下列的範例來解釋這個問題
在使用function based index時要注意cursor_sharing的設定值,將可能造成無法使用索引的問題

1.首先建立測試環境
SQL> create table t1 as select * from all_objects;
SQL> create index t1_fidx on t1(substr(object_name,3,6);
SQL> execute dbms_stats.gather_table_stats('HR','T1',cascade=>true);

2.開始測試
SQL> select substr(object_name,3,6) from t1 where object_id=10000;

SUBSTR(OBJEC
------------
ER_ADV

SQL> select object_name,object_id from t1 where substr(object_name,3,6)='ER_ADV';
查詢結果省略

3.使用sql_id找出真正使用的執行計畫,如果使用explain plan或set autotrace有時候會與真正的執行計畫不同
SQL> select sql_id from v$sqlarea
2 where sql_text like 'select object_name,object_id from t1%';

SQL_ID
-------------
fyt5sd2rt1zv6

SQL> select * from table(dbms_xplan.display_cursor('fyt5sd2rt1zv6'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID fyt5sd2rt1zv6, child number 0
-------------------------------------
select object_name,object_id from t1 where
substr(object_name,3,6)='ER_ADV'

Plan hash value: 2441016827

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 3 | 114 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_FIDX | 3 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

2 - access("T1"."SYS_NC00016$"='ER_ADV')


20 rows selected.
由以上的結果可以看出,Function based Index有被使用


4.現在將cursor_sharing設為force,在進行相關的測試
SQL> select substr(object_name,3,6) from t1 where object_id=20000;

SUBSTR(OBJEC
------------
565853

SQL> alter session set cursor_sharing=force;

Session altered.

SQL> select object_name,object_id from t1
2 where substr(object_name,3,6)='565853';

OBJECT_NAME OBJECT_ID
------------------------------ ----------
/25658537_StoreInstruction 20000
/25658537_StoreInstruction 39336

SQL> select substr(object_name,3,6) from t1 where object_id=30000;

SUBSTR(OBJECT_NAME,3,6)
------------------------------------------------------------
6d7f7a

SQL> select object_name,object_id from t1
2 where substr(object_name,3,6)='6d7f7a';

OBJECT_NAME OBJECT_ID
------------------------------ ----------
/86d7f7a5_ProfileErrorsText_el 30000
/86d7f7a5_ProfileErrorsText_el 49335

SQL> select sql_id,sql_text from v$sqlarea where
2 sql_text like 'select object_name,object_id from t1%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
fyt5sd2rt1zv6
select object_name,object_id from t1 where substr(object_name,3,6)='ER_ADV'

6w6hd3w7ju6v9
select object_name,object_id from t1 where substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"
注意現在的where條件已經被cursor_sharing=force的功能修改為substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"
與所執行的where substr(object_name,3,6)不同
所以才會造成下面的結果,使用full table scan,而不使用function based index

SQL> select * from table(dbms_xplan.display_cursor('6w6hd3w7ju6v9'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6w6hd3w7ju6v9, child number 0
-------------------------------------
select object_name,object_id from t1 where
substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 230 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 538 | 16678 | 230 (2)| 00:00:03 |
--------------------------------------------------------------------------

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

1 - filter(SUBSTR("OBJECT_NAME",:SYS_B_0,:SYS_B_1)=:SYS_B_2)


19 rows selected.

5.即便加上hint,強迫使用index,也無法使用
SQL> select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1
2 where substr(object_name,3,6)='6d7f7a';

OBJECT_NAME OBJECT_ID
------------------------------ ----------
/86d7f7a5_ProfileErrorsText_el 30000
/86d7f7a5_ProfileErrorsText_el 49335

SQL> select sql_id,sql_text from v$sqlarea where
2 sql_text like 'select % from t1 where substr%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
9hnjcn7h3t35d
select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1 where substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"
SYS_B_2"

fyt5sd2rt1zv6
select object_name,object_id from t1 where substr(object_name,3,6)='ER_ADV'

6w6hd3w7ju6v9
select object_name,object_id from t1 where substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------


SQL> select * from table(dbms_xplan.display_cursor('9hnjcn7h3t35d'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 9hnjcn7h3t35d, child number 0
-------------------------------------
select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1 where
substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 230 (100)| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| T1 | 538 | 16678 | 230 (2)| 00:00:03 |
--------------------------------------------------------------------------

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

1 - filter(SUBSTR("OBJECT_NAME",:SYS_B_0,:SYS_B_1)=:SYS_B_2)


19 rows selected.

6.如果將cursor_sharing設為exact,則hint就可以發揮效果,當然若不使用hint在目前的環境,也是可以使用function based index
SQL> alter session set cursor_sharing=exact;

Session altered.

SQL> select substr(object_name,3,6) from t1 where object_id=40000;

SUBSTR(OBJEC
------------
b93801

SQL> select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1
2 where substr(object_name,3,6)='b93801';

OBJECT_NAME OBJECT_ID
------------------------------ ----------
/7b938016_ProtocolVersion 20664
/7b938016_ProtocolVersion 40000

SQL> select sql_id,sql_text from v$sqlarea where
2 sql_text like 'select % from t1 where substr%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------
bbhcgkhrysz5s
select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1 where substr(object_name,3,6)='b93801'

9hnjcn7h3t35d
select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1 where substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"
SYS_B_2"

fyt5sd2rt1zv6
select object_name,object_id from t1 where substr(object_name,3,6)='ER_ADV'

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------

0uywm521n21jj
select sql_id,sql_text from v$sqlarea where sql_text like 'select % from t1 where substr%'

6w6hd3w7ju6v9
select object_name,object_id from t1 where substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"


SQL> select * from table(dbms_xplan.display_cursor('bbhcgkhrysz5s'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID bbhcgkhrysz5s, child number 0
-------------------------------------
select /*+INDEX(t1_fidx t1)*/ object_name,object_id from t1 where
substr(object_name,3,6)='b93801'

Plan hash value: 2441016827

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 3 | 114 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_FIDX | 3 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

2 - access("T1"."SYS_NC00016$"='b93801')


20 rows selected.