[20201224]order by欄位順序與查詢條件為NULL.txt

[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條件上判斷存在某種不同。
--//當然這些僅僅是細節問題，僅僅引起在工作中注意。