恆逸講師Tips分享-為什麼Oracle 不使用我的索引(1)

作　　者：申建忠精誠資訊恆逸教育訓練中心資深講師
技術分類：Oracle Database

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

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

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.

您可在課程中了解更多的技術…

相關學習資源︰

10gSTW：Oracle 10g資料庫之SQL效能調整實作演練