注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

一条关联子查询的sql优化  

2014-01-27 17:27:03|  分类: 优化 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

原sql:
--1.563S
SELECT COUNT(1)
FROM   CU_CUSTOMER_EXT CCE
WHERE  ((SELECT COUNT(1)
         FROM   TK_PHONE         TK
               ,TK_PHONE_LINE    L
               ,TK_PHONE_PROCESS P
         WHERE  TK.TASK_ID = L.TASK_LINE_TASK
         AND    L.TASK_LINE_ID = P.TASK_PROC_TASK_LINE
         AND    L.TASK_LINE_CUSTOMER = CCE.CUSTOMER_ID
         AND    P.TASK_PROC_ISINTERACT = 1
         AND    TRUNC(TASK_PROC_PLANED_DATE) >=
                TO_DATE('2013-12-15'
                        ,'YYYY-MM-DD')
         AND    TRUNC(TASK_PROC_PLANED_DATE) <=
                TO_DATE('2014-1-14'
                        ,'YYYY-MM-DD')) BETWEEN 1 AND 3)
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                          |      1 |        |      1 |00:00:03.27 |     271K|
|   1 |  SORT AGGREGATE                   |                          |      1 |      1 |      1 |00:00:03.27 |     271K|
|*  2 |   FILTER                          |                          |      1 |        |      6 |00:00:03.27 |     271K|
|   3 |    INDEX FAST FULL SCAN           | PK_CU_CUSTOMER_EXT       |      1 |    285K|    285K|00:00:00.09 |     632 |
|   4 |    SORT AGGREGATE                 |                          |    285K|      1 |    285K|00:00:02.81 |     270K|
|   5 |     NESTED LOOPS                  |                          |    285K|        |     21 |00:00:02.42 |     270K|
|   6 |      NESTED LOOPS                 |                          |    285K|      1 |  10184 |00:00:02.16 |     261K|
|   7 |       NESTED LOOPS                |                          |    285K|      1 |  22129 |00:00:01.87 |     244K|
|   8 |        TABLE ACCESS BY INDEX ROWID| TK_PHONE_LINE            |    285K|      3 |    173K|00:00:01.32 |     172K|
|*  9 |         INDEX RANGE SCAN          | IX_TK_PHONE_LINE_CUST    |    285K|      3 |    173K|00:00:00.57 |    4662 |
|* 10 |        INDEX UNIQUE SCAN          | PK_TK_PHONE              |    173K|      1 |  22129 |00:00:00.25 |   71854 |
|* 11 |       INDEX RANGE SCAN            | IX_TK_PHONE_PROCESS_LINE |  22129 |      2 |  10184 |00:00:00.08 |   17465 |
|* 12 |      TABLE ACCESS BY INDEX ROWID  | TK_PHONE_PROCESS         |  10184 |      1 |     21 |00:00:00.03 |    9475 |
------------------------------------------------------------------------------------------------------------------------
   2 - filter((>=1 AND <=3))
   9 - access("L"."TASK_LINE_CUSTOMER"=:B1)
  10 - access("TK"."TASK_ID"="L"."TASK_LINE_TASK")
  11 - access("L"."TASK_LINE_ID"="P"."TASK_PROC_TASK_LINE")
  12 - filter((NVL("P"."TASK_PROC_ISINTERACT",0)=1 AND TRUNC(INTERNAL_FUNCTION("TASK_PROC_PLANED_DATE"))>=TO_DAT
              E(' 2013-12-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              TRUNC(INTERNAL_FUNCTION("TASK_PROC_PLANED_DATE"))<=TO_DATE(' 2014-01-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))


我总结了一下,有两种改写方式:
一、把外层的CU_CUSTOMER_EXT推入到关联子查询的SQL中
二、把关联子查询的SQL拉到外层,形成跟CU_CUSTOMER_EXT的关联查询
两种改写方式都很成功,SQL执行消耗的时间一致。

 

一、把外层的CU_CUSTOMER_EXT推入到关联子查询的SQL中
1. 用with语句的方式
--0.031S
WITH T AS
(SELECT CCE.CUSTOMER_ID
  FROM   CU_CUSTOMER_EXT  CCE
        ,TK_PHONE         TK
        ,TK_PHONE_LINE    L
        ,TK_PHONE_PROCESS P
  WHERE  TK.TASK_ID = L.TASK_LINE_TASK
  AND    L.TASK_LINE_ID = P.TASK_PROC_TASK_LINE
  AND    L.TASK_LINE_CUSTOMER = CCE.CUSTOMER_ID
  AND    P.TASK_PROC_ISINTERACT = 1
  AND    TRUNC(TASK_PROC_PLANED_DATE) >=
         TO_DATE('2013-12-15'
                 ,'YYYY-MM-DD')
  AND    TRUNC(TASK_PROC_PLANED_DATE) <=
         TO_DATE('2014-1-14'
                 ,'YYYY-MM-DD')
  GROUP  BY CCE.CUSTOMER_ID
  HAVING COUNT(CCE.CUSTOMER_ID) BETWEEN 1 AND 3)
SELECT COUNT(1) FROM   T;


2.上面1中的with语句实际上是在内部语句执行完后,又执行了一次COUNT(1),
所以在内部语句外面再加上一层COUNT(1)计算,即可代替with语句,于是改写成如下:
--0.031S 也是成功的
SELECT COUNT(1) FROM
(SELECT CCE.CUSTOMER_ID,COUNT(1)
  FROM   CU_CUSTOMER_EXT  CCE
        ,TK_PHONE         TK
        ,TK_PHONE_LINE    L
        ,TK_PHONE_PROCESS P
  WHERE  TK.TASK_ID = L.TASK_LINE_TASK
  AND    L.TASK_LINE_ID = P.TASK_PROC_TASK_LINE
  AND    L.TASK_LINE_CUSTOMER = CCE.CUSTOMER_ID
  AND    P.TASK_PROC_ISINTERACT = 1
  AND    TRUNC(TASK_PROC_PLANED_DATE) >=
         TO_DATE('2013-12-15'
                 ,'YYYY-MM-DD')
  AND    TRUNC(TASK_PROC_PLANED_DATE) <=
         TO_DATE('2014-1-14'
                 ,'YYYY-MM-DD')
  GROUP  BY CCE.CUSTOMER_ID
  HAVING COUNT(1) BETWEEN 1 AND 3)


3.对于2中的HAVING条件,也可以认为是内部语句的一个where条件,于是也可以对2改成如下:
--0.031S 也是成功的
SELECT COUNT(1) FROM
(SELECT CCE.CUSTOMER_ID,COUNT(1) CNT
  FROM   CU_CUSTOMER_EXT  CCE
        ,TK_PHONE         TK
        ,TK_PHONE_LINE    L
        ,TK_PHONE_PROCESS P
  WHERE  TK.TASK_ID = L.TASK_LINE_TASK
  AND    L.TASK_LINE_ID = P.TASK_PROC_TASK_LINE
  AND    L.TASK_LINE_CUSTOMER = CCE.CUSTOMER_ID
  AND    P.TASK_PROC_ISINTERACT = 1
  AND    TRUNC(TASK_PROC_PLANED_DATE) >=
         TO_DATE('2013-12-15'
                 ,'YYYY-MM-DD')
  AND    TRUNC(TASK_PROC_PLANED_DATE) <=
         TO_DATE('2014-1-14'
                 ,'YYYY-MM-DD')
  GROUP  BY CCE.CUSTOMER_ID) T
WHERE T.CNT BETWEEN 1 AND 3
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                       |      1 |        |      1 |00:00:00.01 |     442 |
|   1 |  SORT AGGREGATE                    |                       |      1 |      1 |      1 |00:00:00.01 |     442 |
|   2 |   VIEW                             |                       |      1 |      1 |      6 |00:00:00.01 |     442 |
|*  3 |    FILTER                          |                       |      1 |        |      6 |00:00:00.01 |     442 |
|   4 |     HASH GROUP BY                  |                       |      1 |      1 |      7 |00:00:00.01 |     442 |
|   5 |      NESTED LOOPS                  |                       |      1 |      7 |     21 |00:00:00.01 |     442 |
|   6 |       NESTED LOOPS                 |                       |      1 |      7 |     24 |00:00:00.01 |     427 |
|   7 |        NESTED LOOPS                |                       |      1 |     52 |     24 |00:00:00.01 |     400 |
|*  8 |         TABLE ACCESS BY INDEX ROWID| TK_PHONE_PROCESS      |      1 |     52 |     52 |00:00:00.01 |     364 |
|*  9 |          INDEX RANGE SCAN          | IX_TK_PHONE_PLAN_DATE |      1 |    199 |    699 |00:00:00.01 |       7 |
|  10 |         TABLE ACCESS BY INDEX ROWID| TK_PHONE_LINE         |     52 |      1 |     24 |00:00:00.01 |      36 |
|* 11 |          INDEX UNIQUE SCAN         | PK_TK_PHONE_LINE      |     52 |      1 |     24 |00:00:00.01 |      12 |
|* 12 |        INDEX UNIQUE SCAN           | PK_TK_PHONE           |     24 |      1 |     24 |00:00:00.01 |      27 |
|* 13 |       INDEX UNIQUE SCAN            | PK_CU_CUSTOMER_EXT    |     24 |      1 |     21 |00:00:00.01 |      15 |
----------------------------------------------------------------------------------------------------------------------
   3 - filter((COUNT(*)>=1 AND COUNT(*)<=3))
   8 - filter(NVL("P"."TASK_PROC_ISINTERACT",0)=1)
   9 - access("P"."SYS_NC00019$">=TO_DATE(' 2013-12-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "P"."SYS_NC00019$"<=TO_DATE(' 2014-01-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  11 - access("L"."TASK_LINE_ID"="P"."TASK_PROC_TASK_LINE")
  12 - access("TK"."TASK_ID"="L"."TASK_LINE_TASK")
  13 - access("L"."TASK_LINE_CUSTOMER"="CCE"."CUSTOMER_ID")

 

 


二、把关联子查询的SQL拉到外层,形成跟CU_CUSTOMER_EXT的关联查询
1. 关联子查询变两表关联方式
--0.031S 也是成功的
SELECT COUNT(1)
FROM   CU_CUSTOMER_EXT CCE
      ,(SELECT L.TASK_LINE_CUSTOMER
              ,COUNT(1) CNT
        FROM   TK_PHONE         TK
              ,TK_PHONE_LINE    L
              ,TK_PHONE_PROCESS P
        WHERE  TK.TASK_ID = L.TASK_LINE_TASK
        AND    L.TASK_LINE_ID = P.TASK_PROC_TASK_LINE
              -- AND L.TASK_LINE_CUSTOMER = CCE.CUSTOMER_ID
        AND    P.TASK_PROC_ISINTERACT = 1
        AND    TRUNC(TASK_PROC_PLANED_DATE) >=
               TO_DATE('2013-12-15'
                       ,'YYYY-MM-DD')
        AND    TRUNC(TASK_PROC_PLANED_DATE) <=
               TO_DATE('2014-1-14'
                       ,'YYYY-MM-DD')
        GROUP  BY L.TASK_LINE_CUSTOMER) T
WHERE  CCE.CUSTOMER_ID = T.TASK_LINE_CUSTOMER
AND    T.CNT BETWEEN 1 AND 3
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                           |      1 |        |      1 |00:00:00.01 |     365 |
|   1 |  SORT AGGREGATE                    |                           |      1 |      1 |      1 |00:00:00.01 |     365 |
|   2 |   NESTED LOOPS                     |                           |      1 |      1 |      6 |00:00:00.01 |     365 |
|   3 |    VIEW                            |                           |      1 |      1 |      8 |00:00:00.01 |     357 |
|*  4 |     FILTER                         |                           |      1 |        |      8 |00:00:00.01 |     357 |
|   5 |      HASH GROUP BY                 |                           |      1 |      1 |      9 |00:00:00.01 |     357 |
|   6 |       NESTED LOOPS                 |                           |      1 |      1 |     24 |00:00:00.01 |     357 |
|   7 |        NESTED LOOPS                |                           |      1 |      1 |     24 |00:00:00.01 |     330 |
|*  8 |         TABLE ACCESS BY INDEX ROWID| TK_PHONE_PROCESS          |      1 |      1 |     52 |00:00:00.01 |     294 |
|*  9 |          INDEX RANGE SCAN          | IX_TASK_PROC_PLANED_DATE2 |      1 |    199 |    699 |00:00:00.01 |       5 |
|  10 |         TABLE ACCESS BY INDEX ROWID| TK_PHONE_LINE             |     52 |      1 |     24 |00:00:00.01 |      36 |
|* 11 |          INDEX UNIQUE SCAN         | PK_TK_PHONE_LINE          |     52 |      1 |     24 |00:00:00.01 |      12 |
|* 12 |        INDEX UNIQUE SCAN           | PK_TK_PHONE               |     24 |      1 |     24 |00:00:00.01 |      27 |
|* 13 |    INDEX UNIQUE SCAN               | PK_CU_CUSTOMER_EXT        |      8 |      1 |      6 |00:00:00.01 |       8 |
--------------------------------------------------------------------------------------------------------------------------
   4 - filter((COUNT(*)>=1 AND COUNT(*)<=3))
   8 - filter(NVL("P"."TASK_PROC_ISINTERACT",0)=1)
   9 - access("P"."SYS_NC00019$">=TO_DATE(' 2013-12-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "P"."SYS_NC00019$"<=TO_DATE(' 2014-01-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  11 - access("L"."TASK_LINE_ID"="P"."TASK_PROC_TASK_LINE")
  12 - access("TK"."TASK_ID"="L"."TASK_LINE_TASK")
  13 - access("CCE"."CUSTOMER_ID"="T"."TASK_LINE_CUSTOMER")

2.就像前面的例子一样,关联子查询的条件可以改成HAVING,具体SQL如下:
--0.031S 也是成功的
SELECT COUNT(1)
FROM   CU_CUSTOMER_EXT CCE
      ,(SELECT L.TASK_LINE_CUSTOMER
              ,COUNT(1)
        FROM   TK_PHONE         TK
              ,TK_PHONE_LINE    L
              ,TK_PHONE_PROCESS P
        WHERE  TK.TASK_ID = L.TASK_LINE_TASK
        AND    L.TASK_LINE_ID = P.TASK_PROC_TASK_LINE
              -- AND L.TASK_LINE_CUSTOMER = CCE.CUSTOMER_ID
        AND    P.TASK_PROC_ISINTERACT = 1
        AND    TRUNC(TASK_PROC_PLANED_DATE) >=
               TO_DATE('2013-12-15'
                       ,'YYYY-MM-DD')
        AND    TRUNC(TASK_PROC_PLANED_DATE) <=
               TO_DATE('2014-1-14'
                       ,'YYYY-MM-DD')
        GROUP  BY TASK_LINE_CUSTOMER
        HAVING COUNT(1) BETWEEN 1 AND 3) T
WHERE  CCE.CUSTOMER_ID = T.TASK_LINE_CUSTOMER

 

我个人更倾向于第二种方式,更好理解,而且看上面的执行计划,第二种方式消耗的内存更少一点。
该sql貌似已优化解决了,但我这实际情况是不允许调整后面的条件到前面,这时该如何优化呢?现在尚无解。


原始SQL:
SELECT COUNT(1)
FROM   CU_CUSTOMER CC
INNER  JOIN CU_CUSTOMER_EXT CCE
ON     CC.CUSTOMER_ID = CCE.CUSTOMER_ID
WHERE  1 = 1
AND    CUSTOMER_ISVALID = 1
AND    CUSTOMER_TYPE = 1
AND    (CUSTOMER_LEVEL > 1 OR
      (CUSTOMER_LEVEL = 1 AND EXISTS
       (SELECT 'x'
          FROM   SO_ORDER B
          WHERE  B.ORDER_STATUS NOT IN (91)
          AND    B.ORDER_RECEIVED_STATUS <> 2
          AND    B.ORDER_SHIPPED_TIME IS NOT NULL
          AND    B.ORDER_CUSTOMER = CC.CUSTOMER_ID
          AND    B.ORDER_TYPE <> 4
          AND    NOT EXISTS
           (SELECT 'x'
                  FROM   SO_ORDER O
                  WHERE  O.ORDER_TYPE = 4
                  AND    O.ORDER_STATUS NOT IN (91)
                  AND    O.ORDER_RELEVANT = B.ORDER_ID))))
AND    CUSTOMER_ISPHONE = 1
AND    ((SELECT COUNT(*)
         FROM   TK_PHONE         TK
               ,TK_PHONE_LINE    L
               ,TK_PHONE_PROCESS P
         WHERE  TK.TASK_ID = L.TASK_LINE_TASK
         AND    L.TASK_LINE_ID = P.TASK_PROC_TASK_LINE
         AND    L.TASK_LINE_CUSTOMER = CCE.CUSTOMER_ID
         AND    P.TASK_PROC_ISINTERACT = 1
         AND    TRUNC(TASK_PROC_PLANED_DATE) >=
                TO_DATE('2013-12-15'
                        ,'YYYY-MM-DD')
         AND    TRUNC(TASK_PROC_PLANED_DATE) <=
                TO_DATE('2014-1-14'
                        ,'YYYY-MM-DD')) BETWEEN 1 AND 3)
AND    (CCE.CUSTOMER_SERVICE_USER IS NULL OR CCE.CUSTOMER_SERVICE_USER = 0)

改写后的SQL:
SELECT COUNT(1)
FROM   CU_CUSTOMER CC
      ,CU_CUSTOMER_EXT CCE
      ,(SELECT L.TASK_LINE_CUSTOMER
              ,COUNT(*)
        FROM   TK_PHONE         TK
              ,TK_PHONE_LINE    L
              ,TK_PHONE_PROCESS P
        WHERE  TK.TASK_ID = L.TASK_LINE_TASK
        AND    L.TASK_LINE_ID = P.TASK_PROC_TASK_LINE
              --AND    L.TASK_LINE_CUSTOMER = CCE.CUSTOMER_ID
        AND    P.TASK_PROC_ISINTERACT = 1
        AND    TRUNC(TASK_PROC_PLANED_DATE) >=
               TO_DATE('2013-12-15'
                       ,'YYYY-MM-DD')
        AND    TRUNC(TASK_PROC_PLANED_DATE) <=
               TO_DATE('2014-1-14'
                       ,'YYYY-MM-DD')
        GROUP  BY L.TASK_LINE_CUSTOMER
        HAVING COUNT(*) BETWEEN 1 AND 3) T
WHERE  CCE.CUSTOMER_ID = T.TASK_LINE_CUSTOMER
AND    CC.CUSTOMER_ID = CCE.CUSTOMER_ID
AND    1 = 1
AND    CUSTOMER_ISVALID = 1
AND    CUSTOMER_TYPE = 1
AND    (CUSTOMER_LEVEL > 1 OR
      (CUSTOMER_LEVEL = 1 AND EXISTS
       (SELECT 'x'
          FROM   SO_ORDER B
          WHERE  B.ORDER_STATUS NOT IN (91)
          AND    B.ORDER_RECEIVED_STATUS <> 2
          AND    B.ORDER_SHIPPED_TIME IS NOT NULL
          AND    B.ORDER_CUSTOMER = CC.CUSTOMER_ID
          AND    B.ORDER_TYPE <> 4
          AND    NOT EXISTS
           (SELECT 'x'
                  FROM   SO_ORDER O
                  WHERE  O.ORDER_TYPE = 4
                  AND    O.ORDER_STATUS NOT IN (91)
                  AND    O.ORDER_RELEVANT = B.ORDER_ID))))
AND    CUSTOMER_ISPHONE = 1
      /*AND    ((SELECT COUNT(*)
      FROM   TK_PHONE         TK
            ,TK_PHONE_LINE    L
            ,TK_PHONE_PROCESS P
      WHERE  TK.TASK_ID = L.TASK_LINE_TASK
      AND    L.TASK_LINE_ID = P.TASK_PROC_TASK_LINE
      AND    L.TASK_LINE_CUSTOMER = CCE.CUSTOMER_ID
      AND    P.TASK_PROC_ISINTERACT = 1
      AND    TRUNC(TASK_PROC_PLANED_DATE) >=
             TO_DATE('2013-12-15'
                     ,'YYYY-MM-DD')
      AND    TRUNC(TASK_PROC_PLANED_DATE) <=
             TO_DATE('2014-1-14'
                     ,'YYYY-MM-DD')) BETWEEN 1 AND 3)*/
AND    (CCE.CUSTOMER_SERVICE_USER IS NULL OR CCE.CUSTOMER_SERVICE_USER = 0)

  评论这张
 
阅读(183)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018