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