使用with as优化SQL

开发人员丢来一个sql，说是这个SQL跑了半个多小时了，还没有出结果，让优化优化
以下SQL涉及的表已经改过名字
先来看一下这个SQL的执行计划
SQL> set lines 120 pagesize 999
SQL> explain plan for select o.id ,o.user_id ,  o.amount , o.ordertime ,o.paytime
  2    from goolen o
  3  where  o.paywayid  not in (188,182  )and  o.goolentatusid = 1
  4     and (select count(id) from goolen oo where o.user_id = oo.user_id and oo.ordertime < o.ordertime and oo.goolentatusid <>1 
  5     and oo.paywayid  in (188,182) 
  6     and  oo.ordertime > to_date('2014-11-27','yyyy-mm-dd') and oo.ordertime < to_date('2014-12-28','yyyy-mm-dd')   )>0
  7     and o.ordertime > to_date('2014-11-27','yyyy-mm-dd') and o.ordertime < to_date('2014-12-28','yyyy-mm-dd') 
  8     order by o.ordertime;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 121517303

-------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                      |     1 |    40 |     8   (0)| 00:00:01 |
|*  1 |  FILTER                        |                      |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID  | goolen               |     1 |    40 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN            | IND_goolen_ORDERTIME |     1 |       |     3   (0)| 00:00:01 |
|   4 |   SORT AGGREGATE               |                      |     1 |    22 |            |          |
|*  5 |    FILTER                      |                      |       |       |            |          |
|*  6 |     TABLE ACCESS BY INDEX ROWID| goolen               |     1 |    22 |     4   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | IND_goolen_ORDERTIME |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

   1 - filter( (SELECT COUNT(*) FROM "goolen" "OO" WHERE :B1>TO_DATE(' 2014-11-27 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND "OO"."ORDERTIME"               hh24:mi:ss') AND "OO"."ORDERTIME">TO_DATE(' 2014-11-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "OO"."USER_ID"=:B2 AND "OO"."goolenTATUSID"<>1 AND ("OO"."PAYWAYID"=182 OR
              "OO"."PAYWAYID"=188) AND "OO"."ORDERTIME"<:b3>0)
   2 - filter("O"."goolenTATUSID"=1 AND "O"."PAYWAYID"<>182 AND "O"."PAYWAYID"<>188)
   3 - access("O"."ORDERTIME">TO_DATE(' 2014-11-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORDERTIME"    5 - filter(:B1>TO_DATE(' 2014-11-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   6 - filter("OO"."USER_ID"=:B1 AND "OO"."goolenTATUSID"<>1 AND ("OO"."PAYWAYID"=182 OR
              "OO"."PAYWAYID"=188))
   7 - access("OO"."ORDERTIME">TO_DATE(' 2014-11-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "OO"."ORDERTIME"        filter("OO"."ORDERTIME"<:b1>
32 rows selected.

这个表不算大，不到1000万的数据，不过由于SQL写法限制，两个结果集关联，优化器选择了FILTER操作，filter操作其实类似NL的操作，
驱动表谓词过滤后差不多也有10多万的数据，虽然被驱动表上有索引，由于会被扫描次数太多，速度肯定快不了

优化的思路有两个，可以尽可能减少子查询（被驱动表）的体积，但是最好还是改写一下sql，避免做filter操作

使用下面的写法，可以减少被驱动表的体积，但是速度上还是容忍不了，查询一次差不多还是需要40秒左右

SQL> explain plan for with t as (
  2  select o.id ,o.user_id , o.amount , o.ordertime ,o.paytime,o.goolentatusid,o.paywayid
  3  from goolen o
  4  where 
  5  o.ordertime > to_date('2014-11-27','yyyy-mm-dd') and o.ordertime < to_date('2014-12-28','yyyy-mm-dd') 
  6  ),
  7      oo as (
  8  select  /*+ materialize */ * from t where t.paywayid  in (188,182) and t.goolentatusid <>1 
  9  )
 10  select t1.id ,t1.user_id ,  t1.amount , t1.ordertime ,t1.paytime
 11    from t t1
 12  where  t1.paywayid  not in (188,182  ) 
 13     and (select count(id) from oo 
 14              where t1.user_id = oo.user_id 
 15              and oo.ordertime < t1.ordertime
 16          )>0
 17     and t1.goolentatusid = 1
 18     order by t1.ordertime;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3251867247

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                             |     1 |    83 |    11  (10)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION    |                             |       |       |            |          |
|   2 |   LOAD AS SELECT              | SYS_TEMP_0FD9D6601_ABDE85D0 |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| goolen                      |     1 |    40 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IND_goolen_ORDERTIME        |     1 |       |     3   (0)| 00:00:01 |
|   5 |   LOAD AS SELECT              | SYS_TEMP_0FD9D6602_ABDE85D0 |       |       |            |          |
|*  6 |    VIEW                       |                             |     1 |    40 |     2   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL         | SYS_TEMP_0FD9D6601_ABDE85D0 |     1 |    40 |     2   (0)| 00:00:01 |
|   8 |   SORT ORDER BY               |                             |     1 |    83 |     5  (20)| 00:00:01 |
|*  9 |    FILTER                     |                             |       |       |            |          |
|* 10 |     VIEW                      |                             |     1 |    83 |     2   (0)| 00:00:01 |
|  11 |      TABLE ACCESS FULL        | SYS_TEMP_0FD9D6601_ABDE85D0 |     1 |    40 |     2   (0)| 00:00:01 |
|  12 |     SORT AGGREGATE            |                             |     1 |    35 |            |          |
|* 13 |      VIEW                     |                             |     1 |    35 |     2   (0)| 00:00:01 |
|  14 |       TABLE ACCESS FULL       | SYS_TEMP_0FD9D6602_ABDE85D0 |     1 |    40 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

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

   4 - access("O"."ORDERTIME">TO_DATE(' 2014-11-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORDERTIME"    6 - filter(("T"."PAYWAYID"=182 OR "T"."PAYWAYID"=188) AND "T"."goolenTATUSID"<>1)
   9 - filter( (SELECT COUNT("ID") FROM  (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "ID","C1"
              "USER_ID","C2" "AMOUNT","C3" "ORDERTIME","C4" "PAYTIME","C5" "goolenTATUSID","C6" "PAYWAYID" FROM
              "SYS"."SYS_TEMP_0FD9D6602_ABDE85D0" "T1") "OO" WHERE "OO"."USER_ID"=:B1 AND "OO"."ORDERTIME"<:b2>0)
  10 - filter("T1"."PAYWAYID"<>188 AND "T1"."PAYWAYID"<>182 AND "T1"."goolenTATUSID"=1)
  13 - filter("OO"."USER_ID"=:B1 AND "OO"."ORDERTIME"<:b2>
33 rows selected.

仔细观察这个sql，其实可以把sql进行如下改写，去掉了filter操作，最终这个SQL能在2秒内出结果
SQL> explain plan for with t as (select /*+ materialize */ oo.user_id, oo.ordertime
  2            from goolen oo
  3           where  oo.goolentatusid <> 1
  4             and oo.paywayid in (188, 182)
  5             and oo.ordertime > to_date('2014-11-27', 'yyyy-mm-dd')
  6             and oo.ordertime < to_date('2014-12-28', 'yyyy-mm-dd')),
  7  t1 as (
  8  select/*+ materialize */  o.id, o.user_id, o.amount, o.ordertime, o.paytime
  9    from goolen o
 10  where o.paywayid not in (188, 182)
 11     and o.goolentatusid = 1
 12     and o.ordertime > to_date('2014-11-27', 'yyyy-mm-dd')
 13     and o.ordertime < to_date('2014-12-28', 'yyyy-mm-dd'))
 14  select distinct t1.* from t join t1 on t.user_id= t1.user_id and t.ordertime
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1357376612

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                             |     1 |    47 |    15  (20)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION    |                             |       |       |            |          |
|   2 |   LOAD AS SELECT              | SYS_TEMP_0FD9D6603_ABDE85D0 |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| goolen                      |     1 |    22 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IND_goolen_ORDERTIME        |     1 |       |     3   (0)| 00:00:01 |
|   5 |   LOAD AS SELECT              | SYS_TEMP_0FD9D6604_ABDE85D0 |       |       |            |          |
|*  6 |    TABLE ACCESS BY INDEX ROWID| goolen                      |     1 |    40 |     4   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | IND_goolen_ORDERTIME        |     1 |       |     3   (0)| 00:00:01 |
|   8 |   SORT UNIQUE                 |                             |     1 |    47 |     6  (34)| 00:00:01 |
|*  9 |    HASH JOIN                  |                             |     1 |    47 |     5  (20)| 00:00:01 |
|  10 |     VIEW                      |                             |     1 |    15 |     2   (0)| 00:00:01 |
|  11 |      TABLE ACCESS FULL        | SYS_TEMP_0FD9D6603_ABDE85D0 |     1 |    14 |     2   (0)| 00:00:01 |
|  12 |     VIEW                      |                             |     1 |    32 |     2   (0)| 00:00:01 |
|  13 |      TABLE ACCESS FULL        | SYS_TEMP_0FD9D6604_ABDE85D0 |     1 |    32 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

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

   3 - filter("OO"."goolenTATUSID"<>1 AND ("OO"."PAYWAYID"=182 OR "OO"."PAYWAYID"=188))
   4 - access("OO"."ORDERTIME">TO_DATE(' 2014-11-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "OO"."ORDERTIME"    6 - filter("O"."goolenTATUSID"=1 AND "O"."PAYWAYID"<>182 AND "O"."PAYWAYID"<>188)
   7 - access("O"."ORDERTIME">TO_DATE(' 2014-11-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORDERTIME"    9 - access("T"."USER_ID"="T1"."USER_ID")
       filter("T"."ORDERTIME"
32 rows selected.

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

转载于:http://blog.itpub.net/23249684/viewspace-1391084/