ITPub博客

首页 > 数据库 > Oracle > [20201224]order by字段顺序与查询条件为NULL.txt

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

原创 Oracle 作者:lfree 时间:2020-12-25 08:47:02 0 删除 编辑

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






来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2745167/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2823
  • 访问量
    6620806