[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条件上判断存在某种不同。
--//当然这些仅仅是细节问题，仅仅引起在工作中注意。