[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已经支持这个功能。不知道那个隐含参数控制这种引为。