[20202117]Function based indexes and cursor sharing.txt

[20202117]Function based indexes and cursor sharing.txt

--//昨天測試給sql打補丁在11g，根據連結介紹https://hourim.wordpress.com/2020/10/24/function-based-indexes-and-cursor-sharing/
--//視乎從19c開始即使設定CURSOR_SHARING=FORCE,對於函式索引可能不需要我介紹的這樣操作。
--//自己測試看看，加強記憶：

1.環境：
TTT@192.168.2.7:1521/orcl> select banner_full from v$version;
BANNER_FULL
----------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

2.建立測試例子：

create table t1
    as
        select
            rownum               n1,
            'xy' || rownum         vc1,
            mod(rownum, 10)       n2
        from
            dual
        connect by
            level <= 1e4;

create index idx_t1 on t1(substr(vc1,3,1));
alter session set cursor_sharing=force;
select * from t1 where substr(vc1,3,1)='5';

TTT@192.168.2.7:1521/orcl> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  djypd1v8qjawh, child number 0
-------------------------------------
select * from t1 where substr(vc1,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"
Plan hash value: 3491035275
-----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |        |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1     |    100 |  1400 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_T1 |     40 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T1@SEL$1
   2 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."SYS_NC00004$"=:SYS_B_2)

--//確實如此，對於一些實際生產系統確實是一個大的進步。

TTT@192.168.2.7:1521/orcl> alter session set optimizer_features_enable='12.2.0.1';
Session altered.

TTT@192.168.2.7:1521/orcl> select * from t1 where substr(vc1,3,1)='z';
no rows selected

TTT@192.168.2.7:1521/orcl> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  djypd1v8qjawh, child number 1
-------------------------------------
select * from t1 where substr(vc1,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"
Plan hash value: 3617692013
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |    10 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |    100 |  1400 |    10   (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SUBSTR("VC1",:SYS_B_0,:SYS_B_1)=:SYS_B_2)

--//全表掃描，無法使用函式索引。12c還沒有改進。18c已經支援這個功能。不知道那個隱含引數控制這種引為。