程序博客网 > 萝莉捏脸数据百度云

使用WITH AS 优化SQL

来源：互联网 发布：萝莉捏脸数据百度云 编辑：程序博客网 时间：2024/06/07 02:59

http://blog.csdn.net/robinson1988/article/details/6953019


马上就要单身节了，正在想今年我去祸害谁家的姑娘，突然QQ好友发来信息，说能否帮忙优化一个SQL，SQL调优做得实在太多了，都已经麻木了，反正优化一个SQL也就几秒钟到几分钟的事情。

哥们说下面的SQL要跑5个多小时

[html] view plaincopyprint?
  1. SELECT   
  2.                B.AREA_ID,  
  3.                A.PARTY_ID,  
  4.                B.AREA_NAME,  
  5.                C.NAME           CHANNEL_NAME,  
  6.                B.NAME           PARTY_NAME,  
  7.                B.ACCESS_NUMBER,  
  8.                B.PROD_SPEC,  
  9.                B.START_DT,  
  10.                A.BO_ACTION_NAME,  
  11.                A.SO_STAFF_ID,  
  12.                A.ATOM_ACTION_ID,  
  13.                A.PROD_ID   
  14.         FROM   DW_CHANNEL      C,  
  15.                DW_CRM_DAY_USER B,  
  16.                DW_BO_ORDER     A  
  17.         WHERE  A.PROD_ID = B.PROD_ID AND  
  18.                A.CHANNEL_ID = C.CHANNEL_ID AND  
  19.                A.SO_STAFF_ID LIKE '36%' AND  
  20.                A.BO_ACTION_NAME IN ('新装','移机','资费变更') AND  
  21.                B.PROD_SPEC IN ('普通电话', 'ADSL','LAN', '手机',  
  22.                                'E8 - 2S','E6移动版', 'E9版1M(老版)',  
  23.                                '普通E9','普通新版E8',  
  24.                                '全省_紧密融合型E9套餐产品规格',  
  25.                                '(新) 全省_紧密融合型E9套餐产品规格',  
  26.                                '新春欢乐送之E8套餐',  
  27.                                '新春欢乐送之E6套餐') AND  
  28.               NOT  EXISTS (SELECT  *   
  29.                 FROM   DW_BO_ORDER D  
  30.                 WHERE  D.STAFF_ID LIKE '36%' AND  
  31.                        A.PARTY_ID = D.PARTY_ID AND  
  32.                        A.BO_ID != D.BO_ID AND  
  33.                        A.PROD_ID != D.PROD_ID AND  
  34.                        A.BO_ACTION_NAME IN  
  35.                        ('新装', '移机','资费变更') AND  
  36.                        A.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT);  


下面是执行计划以及表信息

[html] view plaincopyprint?
  1. SQL> select count(*) from dw_bo_order;  
  2.    
  3.   COUNT(*)  
  4. ----------  
  5.    2282548  
  6.    
  7. SQL> select count(*) from dw_crm_day_user;  
  8.    
  9.   COUNT(*)  
  10. ----------  
  11.     420918  
  12.    
  13. SQL> select count(*) from dw_channel;  
  14.    
  15.   COUNT(*)  
  16. ----------  
  17.      48031  
  18.        
  19.   
  20. Plan hash value: 2142862569  
  21.    
  22. ----------------------------------------------------------------------------------------------------------  
  23. | Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
  24. ----------------------------------------------------------------------------------------------------------  
  25. |   0 | SELECT STATEMENT       |                 |   905 |   121K|  4152K  (2)| 13:50:32 |       |       |  
  26. |*  1 |  FILTER                |                 |       |       |            |          |       |       |  
  27. |*  2 |   HASH JOIN            |                 |   905 |   121K| 12616   (2)| 00:02:32 |       |       |  
  28. |*  3 |    HASH JOIN           |                 |   905 | 99550 | 12448   (2)| 00:02:30 |       |       |  
  29. |   4 |     PARTITION RANGE ALL|                 |  1979 |   108K|  9168   (2)| 00:01:51 |     1 |     5 |  
  30. |*  5 |      TABLE ACCESS FULL | DW_BO_ORDER     |  1979 |   108K|  9168   (2)| 00:01:51 |     1 |     5 |  
  31. |*  6 |     TABLE ACCESS FULL  | DW_CRM_DAY_USER |   309K|    15M|  3277   (2)| 00:00:40 |       |       |  
  32. |   7 |    TABLE ACCESS FULL   | DW_CHANNEL      | 48425 |  1276K|   168   (1)| 00:00:03 |       |       |  
  33. |*  8 |   FILTER               |                 |       |       |            |          |       |       |  
  34. |   9 |    PARTITION RANGE ALL |                 |     1 |    29 |  9147   (2)| 00:01:50 |     1 |     5 |  
  35. |* 10 |     TABLE ACCESS FULL  | DW_BO_ORDER     |     1 |    29 |  9147   (2)| 00:01:50 |     1 |     5 |  
  36. ----------------------------------------------------------------------------------------------------------  
  37.    
  38. Predicate Information (identified by operation id):  
  39. ---------------------------------------------------  
  40.    
  41.    1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "DW_BO_ORDER" "D" WHERE (:B1='新装' OR :B2='移机' OR   
  42.               :B3='资费变更') AND "D"."PARTY_ID"=:B4 AND TO_CHAR("D"."STAFF_ID") LIKE '36%' AND   
  43.               "D"."COMPLETE_DT">:B5-INTERVAL'+07 00:00:00' DAY(2) TO SECOND(0) AND "D"."PROD_ID"<>:B6 AND   
  44.               "D"."BO_ID"<>:B7))  
  45.    2 - access("A"."CHANNEL_ID"="C"."CHANNEL_ID")  
  46.    3 - access("A"."PROD_ID"="B"."PROD_ID")  
  47.    5 - filter("A"."PROD_ID" IS NOT NULL AND ("A"."BO_ACTION_NAME"='新装' OR   
  48.               "A"."BO_ACTION_NAME"='移机' OR "A"."BO_ACTION_NAME"='资费变更') AND TO_CHAR("A"."SO_STAFF_ID") LIKE   
  49.               '36%')  
  50.    6 - filter("B"."PROD_SPEC"='(新) 全省_紧密融合型E9套餐产品规格' OR "B"."PROD_SPEC"='ADSL' OR   
  51.               "B"."PROD_SPEC"='E6移动版' OR "B"."PROD_SPEC"='E8 - 2S' OR "B"."PROD_SPEC"='E9版1M(老版)' OR   
  52.               "B"."PROD_SPEC"='LAN' OR "B"."PROD_SPEC"='普通E9' OR "B"."PROD_SPEC"='普通电话' OR   
  53.               "B"."PROD_SPEC"='普通新版E8' OR "B"."PROD_SPEC"='全省_紧密融合型E9套餐产品规格' OR "B"."PROD_SPEC"='手机' OR   
  54.               "B"."PROD_SPEC"='新春欢乐送之E6套餐' OR "B"."PROD_SPEC"='新春欢乐送之E8套餐')  
  55.    8 - filter(:B1='新装' OR :B2='移机' OR :B3='资费变更')  
  56.   10 - filter("D"."PARTY_ID"=:B1 AND TO_CHAR("D"."STAFF_ID") LIKE '36%' AND   
  57.               "D"."COMPLETE_DT">:B2-INTERVAL'+07 00:00:00' DAY(2) TO SECOND(0) AND "D"."PROD_ID"<>:B3 AND   
  58.               "D"."BO_ID"<>:B4)          

 

有经验的人一看，一眼就知道这个SQL性能问题出在这里

[html] view plaincopyprint?
  1. NOT  EXISTS (SELECT  *   
  2.                FROM   DW_BO_ORDER D  
  3.                WHERE  D.STAFF_ID LIKE '36%' AND  
  4.                       A.PARTY_ID = D.PARTY_ID AND  
  5.                       A.BO_ID != D.BO_ID AND  
  6.                       A.PROD_ID != D.PROD_ID AND  
  7.                       A.BO_ACTION_NAME IN  
  8.                       ('新装', '移机','资费变更') AND  
  9.                       A.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT);  

你一定要注意看，前面的NOT EXISTS 里面套了 2个 !=  尼玛，坑爹啊，神马业务逻辑啊，这个SQL太坑爹了，由于有!=的存在，CBO不能选择 HASH_AJ join的方式，只能走FILTER，哈哈，走FILTER绝对搞死人，不是吗？因为它要反复扫描 DW_BO_ORDER 非常多次，那么我建议那哥们把SQL改了，把里面的!=拆分，不过可惜的是，不管他怎么拆分，SQL业务逻辑总是不对，尼玛谁叫我们写SQL水平菜呢(自我批评一下)

于是建议他用下面的方法改写SQL

[html] view plaincopyprint?
  1. with D as (select  /*+ materialize */  PARTY_ID,BO_ID,PROD_ID from DW_BO_ORDER where STAFF_ID LIKE '36%')           
  2. SELECT   
  3.                B.AREA_ID,  
  4.                A.PARTY_ID,  
  5.                B.AREA_NAME,  
  6.                C.NAME           CHANNEL_NAME,  
  7.                B.NAME           PARTY_NAME,  
  8.                B.ACCESS_NUMBER,  
  9.                B.PROD_SPEC,  
  10.                B.START_DT,  
  11.                A.BO_ACTION_NAME,  
  12.                A.SO_STAFF_ID,  
  13.                A.ATOM_ACTION_ID,  
  14.                A.PROD_ID   
  15.         FROM   DW_CHANNEL      C,  
  16.                DW_CRM_DAY_USER B,  
  17.                DW_BO_ORDER     A  
  18.         WHERE  A.PROD_ID = B.PROD_ID AND  
  19.                A.CHANNEL_ID = C.CHANNEL_ID AND  
  20.                A.SO_STAFF_ID LIKE '36%' AND  
  21.                A.BO_ACTION_NAME IN ('新装','移机','资费变更') AND  
  22.                B.PROD_SPEC IN ('普通电话', 'ADSL','LAN', '手机',  
  23.                                'E8 - 2S','E6移动版', 'E9版1M(老版)',  
  24.                                '普通E9','普通新版E8',  
  25.                                '全省_紧密融合型E9套餐产品规格',  
  26.                                '(新) 全省_紧密融合型E9套餐产品规格',  
  27.                                '新春欢乐送之E8套餐',  
  28.                                '新春欢乐送之E6套餐') AND  
  29.               NOT  EXISTS (SELECT  *   
  30.                 FROM  D  
  31.                 WHERE  D.STAFF_ID LIKE '36%' AND  
  32.                        A.PARTY_ID = D.PARTY_ID AND  
  33.                        A.BO_ID != D.BO_ID AND  
  34.                        A.PROD_ID != D.PROD_ID AND  
  35.                        A.BO_ACTION_NAME IN  
  36.                        ('新装', '移机','资费变更') AND  
  37.                        A.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT);  


执行计划和SQL执行时间如下：

[html] view plaincopyprint?
  1. SQL> set timi on  
  2. SQL> WITH D AS  
  3.   2   (SELECT /*+ materialize */  
  4.   3     PARTY_ID,  
  5.   4     BO_ID,  
  6.   5     PROD_ID,  
  7.   6     COMPLETE_DT  
  8.   7    FROM   DW_BO_ORDER  
  9.   8    WHERE  STAFF_ID LIKE '36%' AND  
  10.   9           BO_ACTION_NAME IN ('新装',  
  11.  10                                '移机',  
  12.  11                                '资费变更'))  
  13.  12  SELECT  
  14.  13                 B.AREA_ID,  
  15.  14                 A.PARTY_ID,  
  16.  15                 B.AREA_NAME,  
  17.  16                 C.NAME           CHANNEL_NAME,  
  18.  17                 B.NAME           PARTY_NAME,  
  19.  18                 B.ACCESS_NUMBER,  
  20.  19                 B.PROD_SPEC,  
  21.  20                 B.START_DT,  
  22.  21                 A.BO_ACTION_NAME,  
  23.  22                 A.SO_STAFF_ID,  
  24.  23                 A.ATOM_ACTION_ID,  
  25.  24                 A.PROD_ID  
  26.  25          FROM   DW_CHANNEL      C,  
  27.  26                 DW_CRM_DAY_USER B,  
  28.  27                 DW_BO_ORDER     A  
  29.  28          WHERE  A.PROD_ID = B.PROD_ID AND  
  30.  29                 A.CHANNEL_ID = C.CHANNEL_ID AND  
  31.  30                 A.SO_STAFF_ID LIKE '36%' AND  
  32.  31                 A.BO_ACTION_NAME IN ('新装','移机','资费变更') AND  
  33.  32                 B.PROD_SPEC IN ('普通电话', 'ADSL','LAN', '手机',  
  34.  33                                 'E8 - 2S','E6移动版', 'E9版1M(老版)',  
  35.  34                                 '普通E9','普通新版E8',  
  36.  35                                 '全省_紧密融合型E9套餐产品规格',  
  37.  36                                 '(新) 全省_紧密融合型E9套餐产品规格',  
  38.  37                                 '新春欢乐送之E8套餐',  
  39.  38                                 '新春欢乐送之E6套餐') AND  
  40.  39                NOT  EXISTS (SELECT  *  
  41.  40                  FROM  D  
  42.  41                  WHERE  A.PARTY_ID = D.PARTY_ID AND  
  43.  42                         A.BO_ID != D.BO_ID AND  
  44.  43                         A.PROD_ID != D.PROD_ID AND  
  45.  44                         A.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT);  
  46.   
  47. 已选择49245行。  
  48.   
  49.   
  50. 已用时间:  00: 00: 12.37  
  51.   
  52. 执行计划  
  53. --------------------------------------------------------------------------------------------------------------------------  
  54. Plan hash value: 2591883460  
  55.    
  56. --------------------------------------------------------------------------------------------------------------------------  
  57. | Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
  58. --------------------------------------------------------------------------------------------------------------------------  
  59. |   0 | SELECT STATEMENT           |                             |   905 |   121K| 62428   (2)| 00:12:30 |       |       |  
  60. |   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |       |       |  
  61. |   2 |   LOAD AS SELECT           | DW_BO_ORDER                 |       |       |            |          |       |       |  
  62. |   3 |    PARTITION RANGE ALL     |                             |   114K|  3228K|  9127   (2)| 00:01:50 |     1 |     5 |  
  63. |*  4 |     TABLE ACCESS FULL      | DW_BO_ORDER                 |   114K|  3228K|  9127   (2)| 00:01:50 |     1 |     5 |  
  64. |*  5 |   FILTER                   |                             |       |       |            |          |       |       |  
  65. |*  6 |    HASH JOIN               |                             |   905 |   121K| 12616   (2)| 00:02:32 |       |       |  
  66. |*  7 |     HASH JOIN              |                             |   905 | 99550 | 12448   (2)| 00:02:30 |       |       |  
  67. |   8 |      PARTITION RANGE ALL   |                             |  1979 |   108K|  9168   (2)| 00:01:51 |     1 |     5 |  
  68. |*  9 |       TABLE ACCESS FULL    | DW_BO_ORDER                 |  1979 |   108K|  9168   (2)| 00:01:51 |     1 |     5 |  
  69. |* 10 |      TABLE ACCESS FULL     | DW_CRM_DAY_USER             |   309K|    15M|  3277   (2)| 00:00:40 |       |       |  
  70. |  11 |     TABLE ACCESS FULL      | DW_CHANNEL                  | 48425 |  1276K|   168   (1)| 00:00:03 |       |       |  
  71. |* 12 |    FILTER                  |                             |       |       |            |          |       |       |  
  72. |* 13 |     VIEW                   |                             |   114K|  6791K|    90   (3)| 00:00:02 |       |       |  
  73. |  14 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D662E_D625B872 |   114K|  3228K|    90   (3)| 00:00:02 |       |       |  
  74. --------------------------------------------------------------------------------------------------------------------------  
  75.    
  76. Predicate Information (identified by operation id):  
  77. ---------------------------------------------------  
  78.    
  79.    4 - filter(TO_CHAR("STAFF_ID") LIKE '36%')  
  80.    5 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM  (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "STAFF_ID","C1"   
  81.               "PARTY_ID","C2" "BO_ID","C3" "PROD_ID","C4" "COMPLETE_DT" FROM "SYS"."SYS_TEMP_0FD9D662E_D625B872" "T1") "D"   
  82.               WHERE (:B1='新装' OR :B2='移机' OR :B3='资费变更') AND TO_CHAR("D"."STAFF_ID") LIKE '36%' AND "D"."PARTY_ID"=:B4 AND   
  83.               "D"."BO_ID"<>:B5 AND "D"."PROD_ID"<>:B6 AND "D"."COMPLETE_DT">:B7-INTERVAL'+07 00:00:00' DAY(2) TO SECOND(0)))  
  84.    6 - access("A"."CHANNEL_ID"="C"."CHANNEL_ID")  
  85.    7 - access("A"."PROD_ID"="B"."PROD_ID")  
  86.    9 - filter("A"."PROD_ID" IS NOT NULL AND ("A"."BO_ACTION_NAME"='新装' OR "A"."BO_ACTION_NAME"='移机' OR   
  87.               "A"."BO_ACTION_NAME"='资费变更') AND TO_CHAR("A"."SO_STAFF_ID") LIKE '36%')  
  88.   10 - filter("B"."PROD_SPEC"='(新) 全省_紧密融合型E9套餐产品规格' OR "B"."PROD_SPEC"='ADSL' OR "B"."PROD_SPEC"='E6移动版' OR   
  89.               "B"."PROD_SPEC"='E8 - 2S' OR "B"."PROD_SPEC"='E9版1M(老版)' OR "B"."PROD_SPEC"='LAN' OR "B"."PROD_SPEC"='普通E9' OR   
  90.               "B"."PROD_SPEC"='普通电话' OR "B"."PROD_SPEC"='普通新版E8' OR "B"."PROD_SPEC"='全省_紧密融合型E9套餐产品规格' OR "B"."PROD_SPEC"='手机'   
  91.               OR "B"."PROD_SPEC"='新春欢乐送之E6套餐' OR "B"."PROD_SPEC"='新春欢乐送之E8套餐')  
  92.   12 - filter(:B1='新装' OR :B2='移机' OR :B3='资费变更')  
  93.   13 - filter(TO_CHAR("D"."STAFF_ID") LIKE '36%' AND "D"."PARTY_ID"=:B1 AND "D"."BO_ID"<>:B2 AND   
  94.               "D"."PROD_ID"<>:B3 AND "D"."COMPLETE_DT">:B4-INTERVAL'+07 00:00:00' DAY(2) TO SECOND(0))     
  95.   
  96.   
  97. 统计信息  
  98. ----------------------------------------------------------  
  99.           2  recursive calls  
  100.          29  db block gets  
  101.      110506  consistent gets  
  102.          22  physical reads  
  103.         656  redo size  
  104.     2438096  bytes sent via SQL*Net to client  
  105.         449  bytes received via SQL*Net from client  
  106.          11  SQL*Net roundtrips to/from client  
  107.           0  sorts (memory)  
  108.           0  sorts (disk)  
  109.       49245  rows processed  


现在SQL 12秒可以跑完了，这个SQL优化到这里就行了，不能连接他的DB，妈的我业务逻辑也不清楚，奶奶的，神马时候帮别人优化一个SQL 一行一元。


萝莉捏脸数据百度云 萝莉捏脸数据百度云
原创粉丝点击
热门IT博客
杭州软件培训杭州软件培训
多张表格数据汇总透视多张表格数据汇总透视
2k17乔丹捏脸详细数据
淘宝花呗怎么开通不了
诵读古兰经软件
网络电视直播
藤原文太 知乎
全球恐怖袭击数据库
陕西师大网络远程教育
js获取内网ip地址
微信html5砍价源码
java面向对象,面向过程
中国信贷数据
行知天下参考答案2017
穷 艺术家 知乎
sai软件下载
mac网络重装系统错误
芗里芗亲app软件下载
java数据库连接池
网络借贷平台排行榜
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 中专生怎么上大专 上大专学什么好 想报考大专 大专考 怎么才能上大专 报考大专时间 中专护士升大专 大专升本科的学校 大专培训 上大专学什么 大专提高学历 大专怎么上本科 报考大专条件 上海有什么大专 成教大专升本科 大专的毕业证书 大专文凭报考 大专需要什么学历 读大专有什么要求 全日制大专自考 高级大专 大专及以上学历 大专 自考 中专与大专的区别 大专统考 大专毕业证证书 职业高中升大专 大专 专业 大专函授报名时间 大专生在职研究生 成人大专选什么专业好 大专可以读研究生吗 大专生怎么出国留学 网上大专 大专的专业有那些 大专有哪些学校好 大专和大学一样吗 可以专科升本科的学校 自考专科哪些专业好考 自考专科报考条件 计算机自考专科

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里