当前位置: 首页 > 调优 > 正文

分析函数改写not exists的语句优化

     下面是某电信公司一条SQL的优化,由于当时时间很急,只忙于SQL信息的收集,没有具体看怎么优化,下面以dba_objects来模拟一下。客户环境是11.2.0.4,我模拟的环境是11.2.0.3,没有修改过其它的参数。其它的环境不保证会得到相同的结果。

1,环境介绍

www.htz.pw > select * from v$version where rownum<3;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production

www.htz.pw > !lsb_release -a
LSB Version:    :core-3.0-amd64:core-3.0-ia32:core-3.0-noarch:graphics-3.0-amd64:graphics-3.0-ia32:graphics-3.0-noarch
Distributor ID: RedHatEnterpriseAS
Description:    Red Hat Enterprise Linux AS release 4 (Nahant Update 8)
Release:        4
Codename:       NahantUpdate8

2,创建测试环境

dba_objects来创建表

create table scott.htz1 as select * from dba_objects;

www.htz.pw > @desc scott.htz1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)

www.htz.pw > update scott.htz1 set object_id=1 where data_object_id<5000;

1177 rows updated.

www.htz.pw > update scott.htz1 set object_id=2  where data_object_id<6000 and data_object_id>5000;

313 rows updated.

www.htz.pw >  update scott.htz1 set object_id=3  where data_object_id<7000 and data_object_id>6000;

451 rows updated.

www.htz.pw > commit;

Commit complete.

3,原始SQL

这里由于客户环境中SQL走的全表扫瞄,所以这里我没有创建任何的索引。

www.htz.pw > SELECT *
  2    FROM (  SELECT *
  3              FROM scott.htz1 f
  4             WHERE     f.OWNER = 'SYS'
  5                   AND f.OBJECT_TYPE = 'TABLE'
  6                   AND NOT EXISTS
  7                              (SELECT 1
  8                                 FROM scott.htz1 a
  9                                WHERE     a.NAMESPACE = f.NAMESPACE
 10                                      AND a.STATUS = f.STATUS
 11                                      AND a.object_id < f.object_id
 12                                      AND f.OWNER = 'SYS')
 13          ORDER BY f.object_id)
 14   WHERE ROWNUM < 100;

99 rows selected.

Elapsed: 00:00:04.18

Execution Plan
----------------------------------------------------------
Plan hash value: 1058026417

-------------------------------------------------------------------------------------------
| Id  | Operation               | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |    99 | 20493 |       |   673   (1)| 00:00:09 |
|*  1 |  COUNT STOPKEY          |         |       |       |       |            |          |
|   2 |   VIEW                  |         |  1396 |   282K|       |   673   (1)| 00:00:09 |
|*  3 |    SORT ORDER BY STOPKEY|         |  1396 |   331K|   376K|   673   (1)| 00:00:09 |
|*  4 |     HASH JOIN ANTI      |         |  1396 |   331K|       |   598   (1)| 00:00:08 |
|*  5 |      TABLE ACCESS FULL  | HTZ1    |  1403 |   283K|       |   298   (1)| 00:00:04 |
|   6 |      VIEW               | VW_SQ_1 | 75521 |  2655K|       |   299   (1)| 00:00:04 |
|   7 |       TABLE ACCESS FULL | HTZ1    | 75521 |  2286K|       |   299   (1)| 00:00:04 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<100)
   3 - filter(ROWNUM<100)
   4 - access("ITEM_1"="F"."NAMESPACE" AND "ITEM_2"="F"."STATUS" AND
              "F"."OWNER"="ITEM_4")
       filter("ITEM_3"<"F"."OBJECT_ID")
   5 - filter("F"."OBJECT_TYPE"='TABLE' AND "F"."OWNER"='SYS')

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2140  consistent gets
          0  physical reads
          0  redo size
       5475  bytes sent via SQL*Net to client
        589  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         99  rows processed

这里由于有rownum<100,为了验证数据结果是否一致,所以这里我们取消rownum<100的现象,但是在这里多执行计划核心的部分没有影响。

www.htz.pw > SELECT *
  2    FROM (  SELECT *
  3              FROM scott.htz1 f
  4             WHERE     f.OWNER = 'SYS'
  5                   AND f.OBJECT_TYPE = 'TABLE'
  6                   AND NOT EXISTS
  7                              (SELECT 1
  8                                 FROM scott.htz1 a
  9                                WHERE     a.NAMESPACE = f.NAMESPACE
 10                                      AND a.STATUS = f.STATUS
 11                                      AND a.object_id < f.object_id
 12                                      AND f.OWNER = 'SYS')
 13          ORDER BY f.object_id);

456 rows selected.

Elapsed: 00:00:04.15

Execution Plan
----------------------------------------------------------
Plan hash value: 3726387066

----------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |  1396 |   331K|       |   673   (1)| 00:00:09 |
|   1 |  SORT ORDER BY       |         |  1396 |   331K|   376K|   673   (1)| 00:00:09 |
|*  2 |   HASH JOIN ANTI     |         |  1396 |   331K|       |   598   (1)| 00:00:08 |
|*  3 |    TABLE ACCESS FULL | HTZ1    |  1403 |   283K|       |   298   (1)| 00:00:04 |
|   4 |    VIEW              | VW_SQ_1 | 75521 |  2655K|       |   299   (1)| 00:00:04 |
|   5 |     TABLE ACCESS FULL| HTZ1    | 75521 |  2286K|       |   299   (1)| 00:00:04 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_1"="F"."NAMESPACE" AND "ITEM_2"="F"."STATUS" AND
              "F"."OWNER"="ITEM_4")
       filter("ITEM_3"<"F"."OBJECT_ID")
   3 - filter("F"."OBJECT_TYPE"='TABLE' AND "F"."OWNER"='SYS')

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2140  consistent gets
          0  physical reads
          0  redo size
      19640  bytes sent via SQL*Net to client
        853  bytes received via SQL*Net from client
         32  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        456  rows processed

这里看到整个SQL结果的行为456

下面理解一下此SQL想表达的意思,不过在生产环境中,建议去找开发了解。此SQL想返回htz1表中owner=’SYS’时按namespace,status分组中取object_id的最小值,并且f.object_type=’TABLE’

4,改写的SQL

改写后的SQL:

www.htz.pw > SELECT *
  2    FROM (SELECT a.*,
  3                 RANK ()
  4                 OVER (PARTITION BY a.namespace, a.status ORDER BY a.object_id)
  5                    rownum1
  6            FROM scott.htz1 a
  7           WHERE a.owner = 'SYS') b
  8   WHERE b.object_type = 'TABLE' AND rownum1 = 1;

456 rows selected.

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 886518679

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      | 36297 |  7798K|       |  1934   (1)| 00:00:24 |
|*  1 |  VIEW                    |      | 36297 |  7798K|       |  1934   (1)| 00:00:24 |
|*  2 |   WINDOW SORT PUSHED RANK|      | 36297 |  7337K|  8808K|  1934   (1)| 00:00:24 |
|*  3 |    TABLE ACCESS FULL     | HTZ1 | 36297 |  7337K|       |   299   (1)| 00:00:04 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("B"."OBJECT_TYPE"='TABLE' AND "ROWNUM1"=1)
   2 - filter(RANK() OVER ( PARTITION BY "A"."NAMESPACE","A"."STATUS" ORDER BY
              "A"."OBJECT_ID")<=1)
   3 - filter("A"."OWNER"='SYS')

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1070  consistent gets
          0  physical reads
          0  redo size
      19707  bytes sent via SQL*Net to client
        853  bytes received via SQL*Net from client
         32  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        456  rows processed

5,效果对比

下面是优化前后的对表,由于表小,对比的效果不是很明显,如果在几十G的表的时候,效果会很明显的。

逻辑读

消耗时间

优化前

2140

04.15

优化后

1070

00.06

本文固定链接: http://www.htz.pw/2015/04/24/%e5%88%86%e6%9e%90%e5%87%bd%e6%95%b0%e6%94%b9%e5%86%99%e4%b8%80%e6%9d%a1not-exists%e8%af%ad%e5%8f%a5%e4%bc%98%e5%8c%96.html | 认真就输

该日志由 huangtingzhong 于2015年04月24日发表在 调优 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: 分析函数改写not exists的语句优化 | 认真就输
关键字: