[20201224]order by字段顺序与查询条件为NULL.txt

来源：这里教程网时间：2026-03-03 16:18:30 作者：

[20201224]order by字段顺序与查询条件为NULL.txt --//在做优化遇到的问题，在测试环境做一个分析。 1.环境： SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> create table t1 as select * from dba_objects ; Table created. SCOTT@book> create index i_t1_owner_SUB_NAME_CREATED on t1(owner,SUBOBJECT_NAME,CREATED ) ; Index created. 2.测试： SCOTT@book> alter session set statistics_level = all; Session altered. select * from (select last_ddl_time from t1 where owner='SYS' and subobject_name is null order by owner,subobject_name,created) where rownum<=5; select * from (select last_ddl_time from t1 where owner='SYS' and subobject_name is null order by subobject_name,created,owner) where rownum<=5; select * from (select last_ddl_time from t1 where owner='SYS' and subobject_name is null order by subobject_name,owner,created) where rownum<=5; --//如果order by字段顺序如上，上面3条语句，执行计划如下： Plan hash value: 2602283460 ------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 5 |00:00:00.01 | 6 | |* 1 | COUNT STOPKEY | | 1 | | | | | 5 |00:00:00.01 | 6 | | 2 | VIEW | | 1 | 5 | 45 | 4 (0)| 00:00:01 | 5 |00:00:00.01 | 6 | | 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 5 | 120 | 4 (0)| 00:00:01 | 5 |00:00:00.01 | 6 | |* 4 | INDEX RANGE SCAN | I_T1_OWNER_SUB_NAME_CREATED | 1 | 2884 | | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 4 | ------------------------------------------------------------------------------------------------------------------------------------------------------- select * from (select last_ddl_time from t1 where owner='SYS' and subobject_name is null order by created,owner,subobject_name) where rownum<=5; select * from (select last_ddl_time from t1 where owner='SYS' and subobject_name is null order by created,subobject_name,owner) where rownum<=5; select * from (select last_ddl_time from t1 where owner='SYS' and subobject_name is null order by owner,created,subobject_name) where rownum<=5; --//如果order by字段顺序如上，上面3条语句，执行计划如下： --//规律就是subobject_name在created的后面. Plan hash value: 2513547123 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 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 | | | 102 (100)| | 5 |00:00:00.04 | 1131 | | | | |* 1 | COUNT STOPKEY | | 1 | | | | | 5 |00:00:00.04 | 1131 | | | | | 2 | VIEW | | 1 | 2884 | 25956 | 102 (1)| 00:00:02 | 5 |00:00:00.04 | 1131 | | | | |* 3 | SORT ORDER BY STOPKEY | | 1 | 2884 | 69216 | 102 (1)| 00:00:02 | 5 |00:00:00.04 | 1131 | 2048 | 2048 | 2048 (0)| | 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 2884 | 69216 | 101 (0)| 00:00:02 | 37663 |00:00:00.03 | 1131 | | | | |* 5 | INDEX RANGE SCAN | I_T1_OWNER_SUB_NAME_CREATED | 1 | 2884 | | 13 (0)| 00:00:01 | 37663 |00:00:00.01 | 129 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//这种情况无法利用后面的限制rownum<=5的条件.逻辑读上升。 --//如果是等值查询,order by 任何顺序都没有问题. select * from (select last_ddl_time from t1 where owner='SYSTEM' and subobject_name ='P_LESSTHAN100' order by owner,subobject_name,created) where rownum<=5; select * from (select last_ddl_time from t1 where owner='SYSTEM' and subobject_name ='P_LESSTHAN100' order by subobject_name,created,owner) where rownum<=5; select * from (select last_ddl_time from t1 where owner='SYSTEM' and subobject_name ='P_LESSTHAN100' order by subobject_name,owner,created) where rownum<=5; select * from (select last_ddl_time from t1 where owner='SYSTEM' and subobject_name ='P_LESSTHAN100' order by created,owner,subobject_name) where rownum<=5; select * from (select last_ddl_time from t1 where owner='SYSTEM' and subobject_name ='P_LESSTHAN100' order by created,subobject_name,owner) where rownum<=5; select * from (select last_ddl_time from t1 where owner='SYSTEM' and subobject_name ='P_LESSTHAN100' order by owner,created,subobject_name) where rownum<=5; Plan hash value: 2602283460 ------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 5 |00:00:00.01 | 6 | |* 1 | COUNT STOPKEY | | 1 | | | | | 5 |00:00:00.01 | 6 | | 2 | VIEW | | 1 | 5 | 45 | 4 (0)| 00:00:01 | 5 |00:00:00.01 | 6 | | 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 5 | 120 | 4 (0)| 00:00:01 | 5 |00:00:00.01 | 6 | |* 4 | INDEX RANGE SCAN | I_T1_OWNER_SUB_NAME_CREATED | 1 | 51 | | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 4 | ------------------------------------------------------------------------------------------------------------------------------------------------------- --//你可以发现order by无论什么顺序，都不影响执行计划。对比前面主要差异在 subobject_name is null的条件。 --//视乎在等值与is null条件上判断存在某种不同。 --//当然这些仅仅是细节问题，仅仅引起在工作中注意。