[20220414]Function based indexes and cursor sharing2.txt

来源：这里教程网时间：2026-03-03 17:34:14 作者：

[20220414]Function based indexes and cursor sharing2.txt --//以前的测试：http://blog.itpub.net/267265/viewspace-2737440/ =>[20202117]Function based indexes and cursor sharing.txt --//也就是从19c开始如果你设置cursor_sharing=force; 一些函数里面带有参数的依旧可以使用索引。但是最近我优化遇到的问题，不 --//知道为什么不能使用索引。 1.环境： > @ pr ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 19.0.0.0.0 BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.9.0.0.0 BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. 2.分析： > @ash/ash_index_helper % XXXXXX_YYY.TEMP_HSDJ &day 1 1 -- Santa's Little (Index) Helper BETA v0.5 - by Tanel Poder ( https://tanelpoder.com ) SECONDS AAS CPU WAIT Accessed_Table Plan_Operation PLAN_CARD TABLE_ROWS FILTER_PCT SQL_EXECS ELA_SEC/EXEC PREDICATES SQL_ID MODULE1 ---------- ------- ----- ----- -------------------- ---------------------------------------- --------- ---------- ---------- ---------- ------------ ------------------------------------------------------------ ------------- -------- 50 .0 100% 0% TEMP_HSDJ TABLE ACCESS FULL [XXXXXX_YYY.TEMP_HSDJ] 1552 156391 .992384472 1040 0.059 [F:] (TO_CHAR(INTERNAL_FUNCTION("T"."DJDATE"),:SYS_B_38)=:S 8y1j8tf28h2w8 w3wp.exe YS_B_39 AND "T"."DJSTATUS" IS NULL) > @ sql_id 8y1j8tf28h2w8 --SQL_ID = 8y1j8tf28h2w8 select * from (select ... from temp_hsdj t where t.djstatus is null and to_char(t.djdate,:"SYS_B_38") = :"SYS_B_39" order by djdate desc ) where :"SYS_B_40"=:"SYS_B_41" and rownum < = :"SYS_B_42" ; > @ bind_cap 8y1j8tf28h2w8 :sys_B_3 SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING ------------- ------------ --- ---------------------------------------- ---------- ---------- ------------------- --------------- ------------ 8y1j8tf28h2w8 1 YES :SYS_B_38 39 32 2022-04-13 17:34:04 VARCHAR2(32) yyyy-mm-dd YES :SYS_B_39 40 32 2022-04-13 17:34:04 VARCHAR2(32) 2022-04-13 --//注太长我仅仅列出where部分。 --//我已经建立了函数索引。 CREATE INDEX XXXXXX_YYY.IF_TEMP_HSDJ_DJDATE ON XXXXXX_YYY.TEMP_HSDJ (TO_CHAR("DJDATE",'yyyy-mm-dd')) LOGGING TABLESPACE XXXXXX_YYY PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) NOPARALLEL COMPRESS 1; --//为了测试需要，我简单改写脚本如下，便于重复测试： $ cat 8y1j8tf28h2w8.sql9_x set termout off alter session set current_schema=XXXXXX_YYY; alter session set statistics_level=all; alter session set cursor_sharing = force; select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' ; set termout on @dpc '' outline '' rollback; alter session set current_schema=SYS ; PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID fatkcpsnxa2g8, child number 0 ------------------------------------- select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,:"SYS_B_0") = :"SYS_B_1" Plan hash value: 1883277554 ----------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 30 (100)| | 1219 |00:00:00.01 | 60 | |* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEMP_HSDJ | 1 | 1084 | 32520 | 30 (0)| 00:00:01 | 1219 |00:00:00.01 | 60 | |* 2 | INDEX RANGE SCAN | IF_TEMP_HSDJ_DJDATE | 1 | 1086 | | 2 (0)| 00:00:01 | 1221 |00:00:00.01 | 16 | ----------------------------------------------------------------------------------------------------------------------------------------------------- --//你可以发现确实可以使用我建立的函数索引，注意看sql语句输出部分，说明cursor_sharing = force是起作用的。 --//按照生产系统的执行条件修改如下： $ cat 8y1j8tf28h2w8.sql9_x $ cat 8y1j8tf28h2w8.sql9_x set termout off alter session set current_schema=XXXXXX_YYY; alter session set statistics_level=all; alter session set cursor_sharing = force; --select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' ; select * from (select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' order by djdate desc ) where 1=1 and rownum < = 30 ; set termout on @dpc '' outline '' rollback; alter session set current_schema=SYS ; --//执行计划如下： Plan hash value: 1191506493 ---------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 1079 (100)| | 30 |00:00:00.06 | 3967 | | | | |* 1 | COUNT STOPKEY | | 1 | | | | | 30 |00:00:00.06 | 3967 | | | | | 2 | VIEW | | 1 | 1562 | 128K| 1079 (1)| 00:00:01 | 30 |00:00:00.06 | 3967 | | | | |* 3 | SORT ORDER BY STOPKEY| | 1 | 1562 | 42174 | 1079 (1)| 00:00:01 | 30 |00:00:00.06 | 3967 | 20480 | 20480 |18432 (0)| |* 4 | FILTER | | 1 | | | | | 1219 |00:00:00.06 | 3967 | | | | |* 5 | TABLE ACCESS FULL | TEMP_HSDJ | 1 | 1562 | 42174 | 1078 (1)| 00:00:01 | 1219 |00:00:00.06 | 3967 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$2 / from$_subquery$_001@SEL$1 3 - SEL$2 5 - SEL$2 / T@SEL$2 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('19.1.0') DB_VERSION('19.1.0') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$1") NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1") FULL(@"SEL$2" "T"@"SEL$2") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 5 - (NUMBER): 30 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=:SYS_B_4) 3 - filter(ROWNUM<=:SYS_B_4) 4 - filter(:SYS_B_2=:SYS_B_3) 5 - filter((TO_CHAR(INTERNAL_FUNCTION("T"."DJDATE"),:SYS_B_0)=:SYS_B_1 AND "T"."DJSTATUS" IS NULL)) --//我仅仅加了order by以及取前面几行的语句，导致无法使用函数索引，到底是那里导致该问题。 $ cat 8y1j8tf28h2w8.sql9_x set termout off alter session set current_schema=XXXXXX_YYY; alter session set statistics_level=all; alter session set cursor_sharing = force; --select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' ; --select * from (select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' order by djdate desc ) where 1=1 and rownum < = 30 ; select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' order by djdate desc ; set termout on @dpc '' outline '' rollback; alter session set current_schema=SYS ; Plan hash value: 3530033302 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 31 (100)| | 1219 |00:00:00.01 | 35 | | | | | 1 | SORT ORDER BY | | 1 | 1084 | 41192 | 31 (4)| 00:00:01 | 1219 |00:00:00.01 | 35 | 106K| 106K|96256 (0)| |* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TEMP_HSDJ | 1 | 1084 | 41192 | 30 (0)| 00:00:01 | 1219 |00:00:00.01 | 35 | | | | |* 3 | INDEX RANGE SCAN | IF_TEMP_HSDJ_DJDATE | 1 | 1086 | | 2 (0)| 00:00:01 | 1221 |00:00:00.01 | 3 | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//排除了order by djdate desc 可能性。 $ cat 8y1j8tf28h2w8.sql9_x set termout off alter session set current_schema=XXXXXX_YYY; alter session set statistics_level=all; alter session set cursor_sharing = force; --select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' ; --select * from (select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' order by djdate desc ) where 1=1 and rownum < = 30 ; --select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' order by djdate desc ; select * from (select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' order by djdate ) where rownum < = 30 ; set termout on @dpc '' outline '' rollback; alter session set current_schema=SYS ; Plan hash value: 4209826087 ---------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 1079 (100)| | 30 |00:00:00.06 | 3967 | | | | |* 1 | COUNT STOPKEY | | 1 | | | | | 30 |00:00:00.06 | 3967 | | | | | 2 | VIEW | | 1 | 1562 | 128K| 1079 (1)| 00:00:01 | 30 |00:00:00.06 | 3967 | | | | |* 3 | SORT ORDER BY STOPKEY| | 1 | 1562 | 42174 | 1079 (1)| 00:00:01 | 30 |00:00:00.06 | 3967 | 4096 | 4096 | 4096 (0)| |* 4 | TABLE ACCESS FULL | TEMP_HSDJ | 1 | 1562 | 42174 | 1078 (1)| 00:00:01 | 1219 |00:00:00.06 | 3967 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------- --//可以发现只要在外面加入rownum < = 30 限制条件，oracle就不再使用函数索引。 $ cat 8y1j8tf28h2w8.sql9_x set termout off alter session set current_schema=XXXXXX_YYY; alter session set statistics_level=all; alter session set cursor_sharing = force; --select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' ; --select * from (select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' order by djdate desc ) where 1=1 and rownum < = 30 ; --select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' order by djdate desc ; --select * from (select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' order by djdate ) where rownum < = 30 ; select * from (select /*+ MATERIALIZE index(t if_temp_hsdj_djdate) */ t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' order by djdate ) where rownum < = 30 ; set termout on @dpc '' outline '' rollback; alter session set current_schema=SYS ; --//依旧不使用我建立的索引。修改约束限制DJDATE not null呢？ Plan hash value: 3895191266 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 4235 (100)| | 30 |00:00:00.10 | 4239 | | | | |* 1 | COUNT STOPKEY | | 1 | | | | | 30 |00:00:00.10 | 4239 | | | | | 2 | VIEW | | 1 | 1562 | 128K| 4235 (1)| 00:00:01 | 30 |00:00:00.10 | 4239 | | | | |* 3 | SORT ORDER BY STOPKEY | | 1 | 1562 | 42174 | 4235 (1)| 00:00:01 | 30 |00:00:00.10 | 4239 | 4096 | 4096 | 4096 (0)| |* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TEMP_HSDJ | 1 | 1562 | 42174 | 4234 (1)| 00:00:01 | 1219 |00:00:00.10 | 4239 | | | | | 5 | INDEX FULL SCAN | IF_TEMP_HSDJ_DJDATE | 1 | 156K| | 243 (1)| 00:00:01 | 156K|00:00:00.02 | 243 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//可以使用，但是操作=INDEX FULL SCAN,执行效率低下。 --//如果我使用with改写: $ cat 8y1j8tf28h2w8.sql9_x set termout off alter session set current_schema=XXXXXX_YYY; alter session set statistics_level=all; alter session set cursor_sharing = force; --select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' ; --select * from (select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' order by djdate desc ) where 1=1 and rownum < = 30 ; --select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' order by djdate desc ; --select * from (select t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' order by djdate ) where rownum < = 30 ; --select * from (select /*+ MATERIALIZE INDEX_ASC(t if_temp_hsdj_djdate) */ t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' order by to_char(t.djdate,'yyyy-mm-dd') desc) where rownum < = 30 ; with a as (select /*+ MATERIALIZE INDEX_ASC(t if_temp_hsdj_djdate) */ t.section ,t.brxm from temp_hsdj t where t.djstatus is null and to_char(t.djdate,'yyyy-mm-dd') = '2022-04-13' order by t.djdate desc) select * from a where rownum < = 30 ; set termout on @dpc '' outline '' rollback; alter session set current_schema=SYS ; Plan hash value: 138295244 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 34 (100)| | 30 |00:00:00.01 | 37 | | | | | 1 | TEMP TABLE TRANSFORMATION | | 1 | | | | | 30 |00:00:00.01 | 37 | | | | | 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D8104_1930D14 | 1 | | | | | 0 |00:00:00.01 | 36 | 1024 | 1024 | | | 3 | SORT ORDER BY | | 1 | 1084 | 41192 | 31 (4)| 00:00:01 | 1219 |00:00:00.01 | 35 | 106K| 106K|96256 (0)| |* 4 | TABLE ACCESS BY INDEX ROWID BATCHED | TEMP_HSDJ | 1 | 1084 | 41192 | 30 (0)| 00:00:01 | 1219 |00:00:00.01 | 35 | | | | |* 5 | INDEX RANGE SCAN | IF_TEMP_HSDJ_DJDATE | 1 | 1086 | | 2 (0)| 00:00:01 | 1221 |00:00:00.01 | 3 | | | | |* 6 | COUNT STOPKEY | | 1 | | | | | 30 |00:00:00.01 | 0 | | | | | 7 | VIEW | | 1 | 1084 | 91056 | 3 (0)| 00:00:01 | 30 |00:00:00.01 | 0 | | | | | 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D8104_1930D14 | 1 | 1084 | 18428 | 3 (0)| 00:00:01 | 30 |00:00:00.01 | 0 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//取消MATERIALIZE ，INDEX_ASC(t if_temp_hsdj_djdate)提示看到如下： Plan hash value: 2137499364 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 31 (100)| | 30 |00:00:00.01 | 35 | | | | |* 1 | COUNT STOPKEY | | 1 | | | | | 30 |00:00:00.01 | 35 | | | | | 2 | VIEW | | 1 | 1084 | 91056 | 31 (4)| 00:00:01 | 30 |00:00:00.01 | 35 | | | | |* 3 | SORT ORDER BY STOPKEY | | 1 | 1084 | 41192 | 31 (4)| 00:00:01 | 30 |00:00:00.01 | 35 | 20480 | 20480 |18432 (0)| |* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TEMP_HSDJ | 1 | 1084 | 41192 | 30 (0)| 00:00:01 | 1219 |00:00:00.01 | 35 | | | | |* 5 | INDEX RANGE SCAN | IF_TEMP_HSDJ_DJDATE | 1 | 1086 | | 2 (0)| 00:00:01 | 1221 |00:00:00.01 | 3 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//最终我也不知道问题在那里。实际上最佳的方式提示开发在where条件里面使用to_char,trunc之类的函数尽可能避免，可以开发一直 --//没长大，不断反反复复范这类错误。 SELECT /*+ gather_plan_statistics */ * FROM ( SELECT t.section, t.brxm FROM temp_hsdj t WHERE t.djstatus IS NULL AND t.djdate BETWEEN TO_DATE ('2022-04-13', 'yyyy-mm-dd') AND TO_DATE ('2022-04-13', 'yyyy-mm-dd') + 86359 / 86400 ORDER BY djdate DESC) WHERE 1 = 1 AND ROWNUM <= 30; ----------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 30 |00:00:00.01 | 4 | |* 1 | COUNT STOPKEY | | 1 | | | | | 30 |00:00:00.01 | 4 | | 2 | VIEW | | 1 | 30 | 2520 | 3 (0)| 00:00:01 | 30 |00:00:00.01 | 4 | |* 3 | TABLE ACCESS BY INDEX ROWID | TEMP_HSDJ | 1 | 1023 | 27621 | 3 (0)| 00:00:01 | 30 |00:00:00.01 | 4 | |* 4 | INDEX RANGE SCAN DESCENDING| I_TEMP_HSDJ_DJDATE | 1 | 30 | | 2 (0)| 00:00:01 | 31 |00:00:00.01 | 2 | ----------------------------------------------------------------------------------------------------------------------------------------------- --//你可以发现操作id=4 充分利用了 INDEX RANGE SCAN DESCENDING ,降序读取的方式。前面的读取IF_TEMP_HSDJ_DJDATE是3(注意我还建立的索引是压缩的) --//而前面I_TEMP_HSDJ_DJDATE的逻辑读是2。最终的逻辑读更小。 3.总结： --//再次提醒开发在谓词条件里面在使用to_char,trunc,lower,upper之类的函数要小心，不要反反复复的范低级错误，看上去一辈子也长不大。 --//至少对于自己所从事的职业附上一点点责任，看上去更加专业一点。