[20241222]21c下测试是否可以使用相关索引2.txt

来源：这里教程网时间：2026-03-03 21:05:24 作者：

[20241222]21c下测试是否可以使用相关索引2.txt --//优化sql语句遇到的问题，做一个简单测试，验证建立相关索引是否有效，结果遇到意外情况，做一个记录： 1.环境： SCOTT@book01p> @ver2 ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 21.0.0.0.0 BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. 2.测试例子建立： SCOTT@book01p> create table t as select OBJECT_ID,CREATED SCHEDULED_DATE_TIME,CREATED START_DATE_TIME,LAST_DDL_TIME IN_DATE_TIME from all_objects; Table created. SCOTT@book01p> @ desc t Name Null? Type ------------------- -------- ------ 1 OBJECT_ID NOT NULL NUMBER 2 SCHEDULED_DATE_TIME NOT NULL DATE 3 START_DATE_TIME NOT NULL DATE 4 IN_DATE_TIME NOT NULL DATE SCOTT@book01p> create index i_t_SCHEDULED_DATE_TIME on t(SCHEDULED_DATE_TIME); Index created. SCOTT@book01p> create index i_t_START_DATE_TIME on t(START_DATE_TIME); Index created. SCOTT@book01p> create index i_t_IN_DATE_TIME on t(IN_DATE_TIME); Index created. SCOTT@book01p> alter table t modify in_date_time null ; Table altered. SCOTT@book01p> alter table t modify SCHEDULED_DATE_TIME null ; Table altered. SCOTT@book01p> alter table t modify START_DATE_TIME null ; Table altered. --//避免遇到约束相关问题，也就是与真实的生产环境一致。 SCOTT@book01p> @ desczz t 1 eXtended describe of t DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER.TABLE_NAME <filters> SAMPLE : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value ----- ---------- ----------- ------------------- ---- -------------------- ---------- --------- ------------ -------------- ---------- --------- ----------- ---------------------------------------- -------------------- SCOTT T 69994 2024-12-21 16:24:16 1 OBJECT_ID NOT NULL NUMBER(,) 69994 .00001428694 0 1 2 91340 69994 2024-12-21 16:24:16 2 SCHEDULED_DATE_TIME DATE(7) 1151 .00086880973 0 1 2021-07-27 19:10:29 2024-12-19 16:49:06 69994 2024-12-21 16:24:16 3 START_DATE_TIME DATE(7) 1151 .00086880973 0 1 2021-07-27 19:10:29 2024-12-19 16:49:06 69994 2024-12-21 16:24:16 4 IN_DATE_TIME DATE(7) 1240 .00080645161 0 1 2008-11-18 10:54:01 2024-12-19 16:49:13 --//建立测试脚本： $ cat v1.txt variable STARTTIME VARCHAR2(32) variable ENDTIME VARCHAR2(32) begin :STARTTIME := '2024/12/19 00:00:00'; :ENDTIME := '2024/12/20 00:00:00'; null; end; / alter session set statistics_level=all; select t.object_id ,SCHEDULED_DATE_TIME ,CASE WHEN IN_DATE_TIME IS NULL THEN SCHEDULED_DATE_TIME ELSE IN_DATE_TIME END AS START_DATE_TIME from t where ((SCHEDULED_DATE_TIME >= :startTime AND SCHEDULED_DATE_TIME < :endTime) OR (START_DATE_TIME >= :startTime AND START_DATE_TIME <= :endTime) OR (IN_DATE_TIME >= :startTime AND IN_DATE_TIME <= :endTime)); --//注意查询START_DATE_TIME被重新定义。 3.测试： SCOTT@book01p> @ v1.txt .. --//输出略。 SCOTT@book01p> @ dpc '' '' '' Plan hash value: 1356840866 ------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 10 (100)| | 356 |00:00:00.01 | 63 | | 1 | VIEW | VW_ORE_1B35BA0F | 1 | 265 | 8215 | 10 (0)| 00:00:01 | 356 |00:00:00.01 | 63 | | 2 | UNION-ALL | | 1 | | | | | 356 |00:00:00.01 | 63 | |* 3 | FILTER | | 1 | | | | | 122 |00:00:00.01 | 14 | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 100 | 2100 | 3 (0)| 00:00:01 | 122 |00:00:00.01 | 14 | |* 5 | INDEX RANGE SCAN | I_T_SCHEDULED_DATE_TIME | 1 | 100 | | 2 (0)| 00:00:01 | 122 |00:00:00.01 | 4 | |* 6 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 10 | |* 7 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 100 | 2900 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 10 | |* 8 | INDEX RANGE SCAN | I_T_START_DATE_TIME | 1 | 100 | | 2 (0)| 00:00:01 | 122 |00:00:00.01 | 2 | |* 9 | FILTER | | 1 | | | | | 234 |00:00:00.01 | 39 | |* 10 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 65 | 1885 | 4 (0)| 00:00:01 | 234 |00:00:00.01 | 39 | |* 11 | INDEX RANGE SCAN | I_T_IN_DATE_TIME | 1 | 65 | | 2 (0)| 00:00:01 | 356 |00:00:00.01 | 5 | ------------------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SET$BB614FD2 / "VW_ORE_1B35BA0F"@"SEL$1B35BA0F" 2 - SET$BB614FD2 3 - SET$BB614FD2_1 4 - SET$BB614FD2_1 / "T"@"SET$BB614FD2_1" 5 - SET$BB614FD2_1 / "T"@"SET$BB614FD2_1" 6 - SET$BB614FD2_2 7 - SET$BB614FD2_2 / "T"@"SET$BB614FD2_2" 8 - SET$BB614FD2_2 / "T"@"SET$BB614FD2_2" 9 - SET$BB614FD2_3 10 - SET$BB614FD2_3 / "T"@"SET$BB614FD2_3" 11 - SET$BB614FD2_3 / "T"@"SET$BB614FD2_3" Peeked Binds (identified by position): -------------------------------------- 1 - (VARCHAR2(30), CSID=852): '2024/12/19 00:00:00' 2 - (VARCHAR2(30), CSID=852): '2024/12/20 00:00:00' 3 - (VARCHAR2(30), CSID=852, Primary=1) 4 - (VARCHAR2(30), CSID=852, Primary=2) 5 - (VARCHAR2(30), CSID=852, Primary=1) 6 - (VARCHAR2(30), CSID=852, Primary=2) Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(TO_DATE(:ENDTIME)>TO_DATE(:STARTTIME)) 5 - access("SCHEDULED_DATE_TIME">=:STARTTIME AND "SCHEDULED_DATE_TIME"<:ENDTIME) 6 - filter(TO_DATE(:ENDTIME)>=TO_DATE(:STARTTIME)) 7 - filter((LNNVL("SCHEDULED_DATE_TIME">=:STARTTIME) OR LNNVL("SCHEDULED_DATE_TIME"<:ENDTIME))) 8 - access("START_DATE_TIME">=:STARTTIME AND "START_DATE_TIME"<=:ENDTIME) 9 - filter(TO_DATE(:ENDTIME)>=TO_DATE(:STARTTIME)) 10 - filter(((LNNVL("SCHEDULED_DATE_TIME">=:STARTTIME) OR LNNVL("SCHEDULED_DATE_TIME"<:ENDTIME)) AND (LNNVL("START_DATE_TIME">=:STARTTIME) OR LNNVL("START_DATE_TIME"<=:ENDTIME)))) 11 - access("IN_DATE_TIME">=:STARTTIME AND "IN_DATE_TIME"<=:ENDTIME) --//仔细计划竟然可以使用3个索引，说明谓词里面查询条件START_DATE_TIME是真实的表字段，而不是select定义的START_DATE_TIME。 --//建议开发以后写代码不要使用表中出现的字段作为表达式的输出别名，这样非常容易出现歧义。 --//一个很简单的验证修改v1.txt如下，START_DATE_TIME替换成START_DATE_TIME11，执行会报错。 SCOTT@book01p> select t.object_id,SCHEDULED_DATE_TIME 2 ,CASE WHEN IN_DATE_TIME IS NULL THEN SCHEDULED_DATE_TIME ELSE IN_DATE_TIME END AS START_DATE_TIME1 3 from t where 4 ((SCHEDULED_DATE_TIME >= :startTime AND SCHEDULED_DATE_TIME < :endTime) 5 OR (START_DATE_TIME1 >= :startTime AND START_DATE_TIME1 <= :endTime) 6 OR (IN_DATE_TIME >= :startTime AND IN_DATE_TIME <= :endTime)); OR (START_DATE_TIME1 >= :startTime AND START_DATE_TIME1 <= :endTime) * ERROR at line 5: ORA-00904: "START_DATE_TIME1": invalid identifier --//感觉像前面的情况oracle应该执行报错才对。 4.如果继续： --//如果修改如下： $ cat v1.txt variable STARTTIME VARCHAR2(32) variable ENDTIME VARCHAR2(32) begin :STARTTIME := '2024/12/19 00:00:00'; :ENDTIME := '2024/12/20 00:00:00'; null; end; / alter session set statistics_level=all; select t.object_id ,SCHEDULED_DATE_TIME ,CASE WHEN IN_DATE_TIME IS NULL THEN SCHEDULED_DATE_TIME ELSE IN_DATE_TIME END AS START_DATE_TIME from t where ((SCHEDULED_DATE_TIME >= :startTime AND SCHEDULED_DATE_TIME < :endTime) --OR (START_DATE_TIME >= :startTime AND START_DATE_TIME <= :endTime) OR (CASE WHEN IN_DATE_TIME IS NULL THEN SCHEDULED_DATE_TIME ELSE IN_DATE_TIME END >= :startTime AND CASE WHEN IN_DATE_TIME IS NULL THEN SCHEDULED_DATE_TIME ELSE IN_DATE_TIME END <= :endTime) OR (IN_DATE_TIME >= :startTime AND IN_DATE_TIME <= :endTime)); --//或者修改如下： $ cat v3.txt variable STARTTIME VARCHAR2(32) variable ENDTIME VARCHAR2(32) begin :STARTTIME := '2024/12/19 00:00:00'; :ENDTIME := '2024/12/20 00:00:00'; null; end; / alter session set statistics_level=all; select * from ( select t.object_id,SCHEDULED_DATE_TIME,in_date_time ,CASE WHEN IN_DATE_TIME IS NULL THEN SCHEDULED_DATE_TIME ELSE IN_DATE_TIME END AS START_DATE_TIME from t ) where ((SCHEDULED_DATE_TIME >= :startTime AND SCHEDULED_DATE_TIME < :endTime) OR (START_DATE_TIME >= :startTime AND START_DATE_TIME <= :endTime) OR (IN_DATE_TIME >= :startTime AND IN_DATE_TIME <= :endTime)); --//执行计划如下： Plan hash value: 1601196873 ----------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | ----------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 101 (100)| | 356 |00:00:00.10 | 335 | 330 | |* 1 | TABLE ACCESS FULL| T | 1 | 340 | 7140 | 101 (6)| 00:00:01 | 356 |00:00:00.10 | 335 | 330 | ----------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / "T"@"SEL$1" Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('21.1.0') DB_VERSION('21.1.0') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T"@"SEL$1") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 1 - (VARCHAR2(30), CSID=852): '2024/12/19 00:00:00' 2 - (VARCHAR2(30), CSID=852): '2024/12/20 00:00:00' 5 - (VARCHAR2(30), CSID=852, Primary=1) 6 - (VARCHAR2(30), CSID=852, Primary=2) Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter((("SCHEDULED_DATE_TIME">=:STARTTIME AND "SCHEDULED_DATE_TIME"<:ENDTIME) OR (CASE WHEN "IN_DATE_TIME" IS NULL THEN "SCHEDULED_DATE_TIME" ELSE "IN_DATE_TIME" END >=:STARTTIME AND CASE WHEN "IN_DATE_TIME" IS NULL THEN "SCHEDULED_DATE_TIME" ELSE "IN_DATE_TIME" END <=:ENDTIME) OR ("IN_DATE_TIME">=:STARTTIME AND "IN_DATE_TIME"<=:ENDTIME))) --//注意看谓词条件。当然实际上中间的查询条件是多余的。 --//实际上仔细看发现这个条件(START_DATE_TIME >= :startTime AND START_DATE_TIME <= :endTime)是多余的。 --//IN_DATE_TIME是null 等于SCHEDULED_DATE_TIME，其他情况是IN_DATE_TIME。 --//这样已经涵盖在如下条件里面，完全可以取消这个条件。 (SCHEDULED_DATE_TIME >= :startTime AND SCHEDULED_DATE_TIME < :endTime) (IN_DATE_TIME >= :startTime AND IN_DATE_TIME <= :endTime) --//尝试建立如下索引： SCOTT@book01p> create index if_t_start_date_time on t(CASE WHEN IN_DATE_TIME IS NULL THEN SCHEDULED_DATE_TIME ELSE IN_DATE_TIME END); Index created. --//执行计划如下： Plan hash value: 2110115266 ------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 15 (100)| | 356 |00:00:00.01 | 88 | | 1 | VIEW | VW_ORE_1B35BA0F | 1 | 340 | 10540 | 15 (0)| 00:00:01 | 356 |00:00:00.01 | 88 | | 2 | UNION-ALL | | 1 | | | | | 356 |00:00:00.01 | 88 | |* 3 | FILTER | | 1 | | | | | 122 |00:00:00.01 | 14 | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 100 | 2200 | 3 (0)| 00:00:01 | 122 |00:00:00.01 | 14 | |* 5 | INDEX RANGE SCAN | I_T_SCHEDULED_DATE_TIME | 1 | 100 | | 2 (0)| 00:00:01 | 122 |00:00:00.01 | 4 | |* 6 | FILTER | | 1 | | | | | 234 |00:00:00.01 | 39 | |* 7 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 175 | 3850 | 8 (0)| 00:00:01 | 234 |00:00:00.01 | 39 | |* 8 | INDEX RANGE SCAN | IF_T_START_DATE_TIME | 1 | 315 | | 2 (0)| 00:00:01 | 356 |00:00:00.01 | 5 | |* 9 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 35 | |* 10 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 65 | 1885 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 35 | |* 11 | INDEX RANGE SCAN | I_T_IN_DATE_TIME | 1 | 65 | | 2 (0)| 00:00:01 | 356 |00:00:00.01 | 3 | ------------------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SET$BB614FD2 / "VW_ORE_1B35BA0F"@"SEL$1B35BA0F" 2 - SET$BB614FD2 3 - SET$BB614FD2_1 4 - SET$BB614FD2_1 / "T"@"SET$BB614FD2_1" 5 - SET$BB614FD2_1 / "T"@"SET$BB614FD2_1" 6 - SET$BB614FD2_2 7 - SET$BB614FD2_2 / "T"@"SET$BB614FD2_2" 8 - SET$BB614FD2_2 / "T"@"SET$BB614FD2_2" 9 - SET$BB614FD2_3 10 - SET$BB614FD2_3 / "T"@"SET$BB614FD2_3" 11 - SET$BB614FD2_3 / "T"@"SET$BB614FD2_3" Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('21.1.0') DB_VERSION('21.1.0') ALL_ROWS OUTLINE_LEAF(@"SET$BB614FD2_3") OUTLINE_LEAF(@"SET$BB614FD2_2") OUTLINE_LEAF(@"SET$BB614FD2_1") OUTLINE_LEAF(@"SET$BB614FD2") OUTLINE_LEAF(@"SEL$49E1C21B") OR_EXPAND(@"SEL$1" (1) (2) (3)) OUTLINE(@"SEL$1") NO_ACCESS(@"SEL$49E1C21B" "VW_ORE_1B35BA0F"@"SEL$1B35BA0F") INDEX_RS_ASC(@"SET$BB614FD2_1" "T"@"SET$BB614FD2_1" ("T"."SCHEDULED_DATE_TIME")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$BB614FD2_1" "T"@"SET$BB614FD2_1") INDEX_RS_ASC(@"SET$BB614FD2_2" "T"@"SET$BB614FD2_2" "IF_T_START_DATE_TIME") BATCH_TABLE_ACCESS_BY_ROWID(@"SET$BB614FD2_2" "T"@"SET$BB614FD2_2") INDEX_RS_ASC(@"SET$BB614FD2_3" "T"@"SET$BB614FD2_3" ("T"."IN_DATE_TIME")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$BB614FD2_3" "T"@"SET$BB614FD2_3") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 1 - (VARCHAR2(30), CSID=852): '2024/12/19 00:00:00' 2 - (VARCHAR2(30), CSID=852): '2024/12/20 00:00:00' 5 - (VARCHAR2(30), CSID=852, Primary=1) 6 - (VARCHAR2(30), CSID=852, Primary=2) Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(TO_DATE(:ENDTIME)>TO_DATE(:STARTTIME)) 5 - access("SCHEDULED_DATE_TIME">=:STARTTIME AND "SCHEDULED_DATE_TIME"<:ENDTIME) 6 - filter(TO_DATE(:ENDTIME)>=TO_DATE(:STARTTIME)) 7 - filter((LNNVL("SCHEDULED_DATE_TIME">=:STARTTIME) OR LNNVL("SCHEDULED_DATE_TIME"<:ENDTIME))) 8 - access("T"."SYS_NC00005$">=:STARTTIME AND "T"."SYS_NC00005$"<=:ENDTIME) 9 - filter(TO_DATE(:ENDTIME)>=TO_DATE(:STARTTIME)) 10 - filter(((LNNVL("SCHEDULED_DATE_TIME">=:STARTTIME) OR LNNVL("SCHEDULED_DATE_TIME"<:ENDTIME)) AND (LNNVL(CASE WHEN "IN_DATE_TIME" IS NULL THEN "SCHEDULED_DATE_TIME" ELSE "IN_DATE_TIME" END >=:STARTTIME) OR LNNVL(CASE WHEN "IN_DATE_TIME" IS NULL THEN "SCHEDULED_DATE_TIME" ELSE "IN_DATE_TIME" END <=:ENDTIME)))) 11 - access("IN_DATE_TIME">=:STARTTIME AND "IN_DATE_TIME"<=:ENDTIME) --//也就是建立函数索引是可行的。只不过21c采用 OR_EXPAND(@"SEL$F5BB74E1" (1) (2) (3))的形式。 5.总结： --//一点小建议就是开发写代码应该规避这类情况，这样在优化中避免踩坑。

编辑推荐：

返回列表

[20241222]21c下测试是否可以使用相关索引2.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推