cursor_sharing和substr函式索引

當cursor_sharing設定為similar的時候substr的函式索引將不會起作用,例如函式是substr(column,0,3),cursor_sharing為similar或者force的時候，這個條件會被解析成SUBSTR(column,:SYS_B_0,:SYS_B_1)=:SYS_B_2，這是函式索引將不會起作用,oracle認為是unsafe的,因為substr(column,0,1)和substr(column,1,2)解析後的格式都是一樣的，例如:

SQL> create table test_function as select * from dba_objects;
表已建立。

SQL> show parameter cursor_sharing
NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
cursor_sharing                       string
SIMILAR
SQL> set autot trace
SQL>  create index i_test_function on test_function(substr(object_name,0,3));
索引已建立。
SQL> exec dbms_stats.gather_table_stats(user,'TEST_FUNCTION',method_opt=>'for all columns size 254')
;
PL/SQL 過程已成功完成。
SQL> set autot trace
SQL> select /*+ aa */ * from test_function where substr(object_name,0,3)='aaa';
未選定行

執行計劃
----------------------------------------------------------
Plan hash value: 3877315499
--------------------------------------------------------------------------------
---------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%C
PU)| Time     |
--------------------------------------------------------------------------------
---------------
|   0 | SELECT STATEMENT            |                 |   103 |  9991 |    34
(0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_FUNCTION   |   103 |  9991 |    34
(0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_TEST_FUNCTION |   103 |       |     1
(0)| 00:00:01 |
--------------------------------------------------------------------------------
---------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(SUBSTR("OBJECT_NAME",0,3)='aaa')

統計資訊
----------------------------------------------------------
       1464  recursive calls
          0  db block gets
       1086  consistent gets
          1  physical reads
          0  redo size
        996  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         96  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select sql_text,sql_id from v$sql where sql_text like '%/*+ aa */%';
SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID
-------------
EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select /*+ aa */ * from test_
function where substr(object_name,0,3)='aaa'
cgcr4m0jju12u
select /*+ aa */ * from test_function where substr(object_name,:"SYS_B_0",:"SYS_
B_1")=:"SYS_B_2"
905r3hh7vk6sy

SQL> select * from table(dbms_xplan.display_cursor('905r3hh7vk6sy'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  905r3hh7vk6sy, child number 0
-------------------------------------
select /*+ aa */ * from test_function where
substr(object_name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"
Plan hash value: 3258248351
--------------------------------------------------------------------------------
---
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
  |
--------------------------------------------------------------------------------
---
|   0 | SELECT STATEMENT  |               |       |       |   160 (100)|
  |
|*  1 |  TABLE ACCESS FULL| TEST_FUNCTION |   499 | 48403 |   160   (5)| 00:00:0
2 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SUBSTR("OBJECT_NAME",:SYS_B_0,:SYS_B_1)=:SYS_B_2)

已選擇19行。