subquery statement can not unnest

huangtingzhong 发布于 2014-10-29 21:12调优抢沙发

下面是测试其中一种subquery语句不能正常的unnest，测试来至于朋友发的一条SQL语句，消耗了大量的逻辑读，猜测是由于subquery没有unnest导致走FILTER，消耗大量的逻辑。这里只是简单的测试一下不能嵌套，并没有考虑性能问题。

1，环境介绍

www.htz.pw > select * from V$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 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视图来创建2张测试表

www.htz.pw > create table htz.test1 as select * from dba_objects;
Table created.
www.htz.pw > create table htz.test2 as select * from dba_objects;
Table created.

3，测试SQL语句

3.1 原始SQL语句

www.htz.pw > explain plan for select *
  2    from htz.test1 a
  3   where (a.owner = 'SCOTT' or exists
  4          (select 1
  5             from htz.test2 b
  6            where a.owner = b.owner
  7              and b.object_id < 1000))
  8     and a.object_id < 1000
  9  ;

Explained.

www.htz.pw > @plan_by_explain.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 1896454807

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |    91 | 18837 |   298   (1)| 00:00:04 |
|*  1 |  FILTER            |       |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TEST1 |  1521 |   307K|   298   (1)| 00:00:04 |
|*  3 |   TABLE ACCESS FULL| TEST2 |     1 |    30 |   298   (1)| 00:00:04 |
----------------------------------------------------------------------------
这里看到了FILTER这个关键字
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"."OWNER"='SCOTT' OR  EXISTS (SELECT 0 FROM
              "HTZ"."TEST2" "B" WHERE "B"."OWNER"=:B1 AND "B"."OBJECT_ID"<1000))
   2 - filter("A"."OBJECT_ID"<1000)
   3 - filter("B"."OWNER"=:B1 AND "B"."OBJECT_ID"<1000)

3.2 改写SQL语句

下面将SQL语句改写成UNION ALL，生产环境需要开发他们确认一下是否等效

www.htz.pw > explain plan for
  2  select *
  3    from htz.test1 a
  4   where (a.owner = 'SCOTT')
  5     and a.object_id < 1000
  6  union all
  7  select *
  8    from htz.test1 a
  9   where a.object_id < 1000
 10     and lnnvl(a.owner = 'SCOTT')
 11     and exists (select 1
 12            from htz.test2 b
 13           where a.owner = b.owner
 14             and b.object_id < 1000);

Explained.

www.htz.pw > @plan_by_explain.sql
www.htz.pw > set lines 170
www.htz.pw > set pages 1000
www.htz.pw > select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2697857779

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |   726 |   167K|   895  (67)| 00:00:11 |
|   1 |  UNION-ALL            |       |       |       |            |          |
|*  2 |   TABLE ACCESS FULL   | TEST1 |    35 |  7245 |   298   (1)| 00:00:04 |
|*  3 |   HASH JOIN RIGHT SEMI|       |   691 |   159K|   597   (1)| 00:00:08 |
|*  4 |    TABLE ACCESS FULL  | TEST2 |    12 |   360 |   298   (1)| 00:00:04 |
|*  5 |    TABLE ACCESS FULL  | TEST1 |  1521 |   307K|   298   (1)| 00:00:04 |
-------------------------------------------------------------------------------

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

   2 - filter("A"."OWNER"='SCOTT' AND "A"."OBJECT_ID"<1000)
   3 - access("A"."OWNER"="B"."OWNER")
   4 - filter("B"."OBJECT_ID"<1000)
   5 - filter("A"."OBJECT_ID"<1000 AND LNNVL("A"."OWNER"='SCOTT'))

3.3 使用with来改写SQL

使用WITH来改写SQL性能不一定高，但是多数情况下还是可以接受的

www.htz.pw > explain plan for with tt as
  2   (select /*+ materialize */
  3     b.owner
  4      from htz.test2 b
  5     where b.object_id < 1000)
  6  select *
  7    from htz.test1 a
  8   where (a.owner = 'SCOTT' or exists
  9          (select 1 from tt where a.owner = tt.owner))
 10     and a.object_id < 1000;

Explained.

www.htz.pw > @plan_by_explain.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1918954582

--------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |    91 | 18837 |   596   (1)| 00:00:08 |
|   1 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |          |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6601_5F1475 |       |       |            |          |
|*  3 |    TABLE ACCESS FULL       | TEST2                     |    12 |   360 |   298   (1)| 00:00:04 |
|*  4 |   FILTER                   |                           |       |       |            |          |
|*  5 |    TABLE ACCESS FULL       | TEST1                     |  1521 |   307K|   298   (1)| 00:00:04 |
|*  6 |    VIEW                    |                           |    12 |   204 |     2   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6601_5F1475 |    12 |   204 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

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

   3 - filter("B"."OBJECT_ID"<1000)
   4 - filter("A"."OWNER"='SCOTT' OR  EXISTS (SELECT 0 FROM  (SELECT /*+ CACHE_TEMP_TABLE
              ("T1") */ "C0" "OWNER" FROM "SYS"."SYS_TEMP_0FD9D6601_5F1475" "T1") "TT" WHERE
              "TT"."OWNER"=:B1))
   5 - filter("A"."OBJECT_ID"<1000)
   6 - filter("TT"."OWNER"=:B1)

subquery unnest