[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.总结：
--//一点小建议就是开发写代码应该规避这类情况，这样在优化中避免踩坑。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3058754/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
