oracle with sql优化,一个SQL性能优化分享---利用with as 优化SQL

最新推荐文章于 2023-12-28 14:52:37 发布
最新推荐文章于 2023-12-28 14:52:37 发布
阅读量289 收藏
点赞数
文章标签： oracle with sql优化

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

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

SELECT

B.AREA_ID,

A.PARTY_ID,

B.AREA_NAME,

C.NAME           CHANNEL_NAME,

B.NAME           PARTY_NAME,

B.ACCESS_NUMBER,

B.PROD_SPEC,

B.START_DT,

A.BO_ACTION_NAME,

A.SO_STAFF_ID,

A.ATOM_ACTION_ID,

A.PROD_ID

FROM   DW_CHANNEL      C,

DW_CRM_DAY_USER B,

DW_BO_ORDER     A

WHERE  A.PROD_ID = B.PROD_ID AND

A.CHANNEL_ID = C.CHANNEL_ID AND

A.SO_STAFF_ID LIKE '36%' AND

A.BO_ACTION_NAME IN ('新装','移机','资费变更') AND

B.PROD_SPEC IN ('普通电话', 'ADSL','LAN', '手机',

'E8 - 2S','E6移动版', 'E9版1M(老版)',

'普通E9','普通新版E8',

'全省_紧密融合型E9套餐产品规格',

'(新) 全省_紧密融合型E9套餐产品规格',

'新春欢乐送之E8套餐',

'新春欢乐送之E6套餐') AND

NOT  EXISTS (SELECT  *

FROM   DW_BO_ORDER D

WHERE  D.STAFF_ID LIKE '36%' AND

A.PARTY_ID = D.PARTY_ID AND

A.BO_ID != D.BO_ID AND

A.PROD_ID != D.PROD_ID AND

A.BO_ACTION_NAME IN

('新装', '移机','资费变更') AND

A.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT);

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

SQL> select count(*) from dw_bo_order;

COUNT(*)

----------

2282548

SQL> select count(*) from dw_crm_day_user;

COUNT(*)

----------

420918

SQL> select count(*) from dw_channel;

COUNT(*)

----------

48031

Plan hash value: 2142862569

----------------------------------------------------------------------------------------------------------

| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

----------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |                 |   905 |   121K|  4152K  (2)| 13:50:32 |       |       |

|*  1 |  FILTER                |                 |       |       |            |          |       |       |

|*  2 |   HASH JOIN            |                 |   905 |   121K| 12616   (2)| 00:02:32 |       |       |

|*  3 |    HASH JOIN           |                 |   905 | 99550 | 12448   (2)| 00:02:30 |       |       |

|   4 |     PARTITION RANGE ALL|                 |  1979 |   108K|  9168   (2)| 00:01:51 |     1 |     5 |

|*  5 |      TABLE ACCESS FULL | DW_BO_ORDER     |  1979 |   108K|  9168   (2)| 00:01:51 |     1 |     5 |

|*  6 |     TABLE ACCESS FULL  | DW_CRM_DAY_USER |   309K|    15M|  3277   (2)| 00:00:40 |       |       |

|   7 |    TABLE ACCESS FULL   | DW_CHANNEL      | 48425 |  1276K|   168   (1)| 00:00:03 |       |       |

|*  8 |   FILTER               |                 |       |       |            |          |       |       |

|   9 |    PARTITION RANGE ALL |                 |     1 |    29 |  9147   (2)| 00:01:50 |     1 |     5 |

|* 10 |     TABLE ACCESS FULL  | DW_BO_ORDER     |     1 |    29 |  9147   (2)| 00:01:50 |     1 |     5 |

----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "DW_BO_ORDER" "D" WHERE (:B1='新装' OR :B2='移机' OR

:B3='资费变更') AND "D"."PARTY_ID"=:B4 AND TO_CHAR("D"."STAFF_ID") LIKE '36%' AND

"D"."COMPLETE_DT">:B5-INTERVAL'+07 00:00:00' DAY(2) TO SECOND(0) AND "D"."PROD_ID"<>:B6 AND

"D"."BO_ID"<>:B7))

2 - access("A"."CHANNEL_ID"="C"."CHANNEL_ID")

3 - access("A"."PROD_ID"="B"."PROD_ID")

5 - filter("A"."PROD_ID" IS NOT NULL AND ("A"."BO_ACTION_NAME"='新装' OR

"A"."BO_ACTION_NAME"='移机' OR "A"."BO_ACTION_NAME"='资费变更') AND TO_CHAR("A"."SO_STAFF_ID") LIKE

'36%')

6 - filter("B"."PROD_SPEC"='(新) 全省_紧密融合型E9套餐产品规格' OR "B"."PROD_SPEC"='ADSL' OR

"B"."PROD_SPEC"='E6移动版' OR "B"."PROD_SPEC"='E8 - 2S' OR "B"."PROD_SPEC"='E9版1M(老版)' OR

"B"."PROD_SPEC"='LAN' OR "B"."PROD_SPEC"='普通E9' OR "B"."PROD_SPEC"='普通电话' OR

"B"."PROD_SPEC"='普通新版E8' OR "B"."PROD_SPEC"='全省_紧密融合型E9套餐产品规格' OR "B"."PROD_SPEC"='手机' OR

"B"."PROD_SPEC"='新春欢乐送之E6套餐' OR "B"."PROD_SPEC"='新春欢乐送之E8套餐')

8 - filter(:B1='新装' OR :B2='移机' OR :B3='资费变更')

10 - filter("D"."PARTY_ID"=:B1 AND TO_CHAR("D"."STAFF_ID") LIKE '36%' AND

"D"."COMPLETE_DT">:B2-INTERVAL'+07 00:00:00' DAY(2) TO SECOND(0) AND "D"."PROD_ID"<>:B3 AND

"D"."BO_ID"<>:B4)

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

NOT  EXISTS (SELECT  *

FROM   DW_BO_ORDER D

WHERE  D.STAFF_ID LIKE '36%' AND

A.PARTY_ID = D.PARTY_ID AND

A.BO_ID != D.BO_ID AND

A.PROD_ID != D.PROD_ID AND

A.BO_ACTION_NAME IN

('新装', '移机','资费变更') AND

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

with D as (select  /*+ materialize */  PARTY_ID,BO_ID,PROD_ID from DW_BO_ORDER where STAFF_ID LIKE '36%')

SELECT

B.AREA_ID,

A.PARTY_ID,

B.AREA_NAME,

C.NAME           CHANNEL_NAME,

B.NAME           PARTY_NAME,

B.ACCESS_NUMBER,

B.PROD_SPEC,

B.START_DT,

A.BO_ACTION_NAME,

A.SO_STAFF_ID,

A.ATOM_ACTION_ID,

A.PROD_ID

FROM   DW_CHANNEL      C,

DW_CRM_DAY_USER B,

DW_BO_ORDER     A

WHERE  A.PROD_ID = B.PROD_ID AND

A.CHANNEL_ID = C.CHANNEL_ID AND

A.SO_STAFF_ID LIKE '36%' AND

A.BO_ACTION_NAME IN ('新装','移机','资费变更') AND

B.PROD_SPEC IN ('普通电话', 'ADSL','LAN', '手机',

'E8 - 2S','E6移动版', 'E9版1M(老版)',

'普通E9','普通新版E8',

'全省_紧密融合型E9套餐产品规格',

'(新) 全省_紧密融合型E9套餐产品规格',

'新春欢乐送之E8套餐',

'新春欢乐送之E6套餐') AND

NOT  EXISTS (SELECT  *

FROM  D

WHERE  D.STAFF_ID LIKE '36%' AND

A.PARTY_ID = D.PARTY_ID AND

A.BO_ID != D.BO_ID AND

A.PROD_ID != D.PROD_ID AND

A.BO_ACTION_NAME IN

('新装', '移机','资费变更') AND

A.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT);

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

SQL> set timi on

SQL> WITH D AS

2   (SELECT /*+ materialize */

3     PARTY_ID,

4     BO_ID,

5     PROD_ID,

6     COMPLETE_DT

7    FROM   DW_BO_ORDER

8    WHERE  STAFF_ID LIKE '36%' AND

9           BO_ACTION_NAME IN ('新装',

10                                '移机',

11                                '资费变更'))

12  SELECT

13                 B.AREA_ID,

14                 A.PARTY_ID,

15                 B.AREA_NAME,

16                 C.NAME           CHANNEL_NAME,

17                 B.NAME           PARTY_NAME,

18                 B.ACCESS_NUMBER,

19                 B.PROD_SPEC,

20                 B.START_DT,

21                 A.BO_ACTION_NAME,

22                 A.SO_STAFF_ID,

23                 A.ATOM_ACTION_ID,

24                 A.PROD_ID

25          FROM   DW_CHANNEL      C,

26                 DW_CRM_DAY_USER B,

27                 DW_BO_ORDER     A

28          WHERE  A.PROD_ID = B.PROD_ID AND

29                 A.CHANNEL_ID = C.CHANNEL_ID AND

30                 A.SO_STAFF_ID LIKE '36%' AND

31                 A.BO_ACTION_NAME IN ('新装','移机','资费变更') AND

32                 B.PROD_SPEC IN ('普通电话', 'ADSL','LAN', '手机',

33                                 'E8 - 2S','E6移动版', 'E9版1M(老版)',

34                                 '普通E9','普通新版E8',

35                                 '全省_紧密融合型E9套餐产品规格',

36                                 '(新) 全省_紧密融合型E9套餐产品规格',

37                                 '新春欢乐送之E8套餐',

38                                 '新春欢乐送之E6套餐') AND

39                NOT  EXISTS (SELECT  *

40                  FROM  D

41                  WHERE  A.PARTY_ID = D.PARTY_ID AND

42                         A.BO_ID != D.BO_ID AND

43                         A.PROD_ID != D.PROD_ID AND

44                         A.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT);

已选择49245行。

已用时间:  00: 00: 12.37

执行计划

--------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2591883460

--------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |                             |   905 |   121K| 62428   (2)| 00:12:30 |       |       |

|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |       |       |

|   2 |   LOAD AS SELECT           | DW_BO_ORDER                 |       |       |            |          |       |       |

|   3 |    PARTITION RANGE ALL     |                             |   114K|  3228K|  9127   (2)| 00:01:50 |     1 |     5 |

|*  4 |     TABLE ACCESS FULL      | DW_BO_ORDER                 |   114K|  3228K|  9127   (2)| 00:01:50 |     1 |     5 |

|*  5 |   FILTER                   |                             |       |       |            |          |       |       |

|*  6 |    HASH JOIN               |                             |   905 |   121K| 12616   (2)| 00:02:32 |       |       |

|*  7 |     HASH JOIN              |                             |   905 | 99550 | 12448   (2)| 00:02:30 |       |       |

|   8 |      PARTITION RANGE ALL   |                             |  1979 |   108K|  9168   (2)| 00:01:51 |     1 |     5 |

|*  9 |       TABLE ACCESS FULL    | DW_BO_ORDER                 |  1979 |   108K|  9168   (2)| 00:01:51 |     1 |     5 |

|* 10 |      TABLE ACCESS FULL     | DW_CRM_DAY_USER             |   309K|    15M|  3277   (2)| 00:00:40 |       |       |

|  11 |     TABLE ACCESS FULL      | DW_CHANNEL                  | 48425 |  1276K|   168   (1)| 00:00:03 |       |       |

|* 12 |    FILTER                  |                             |       |       |            |          |       |       |

|* 13 |     VIEW                   |                             |   114K|  6791K|    90   (3)| 00:00:02 |       |       |

|  14 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D662E_D625B872 |   114K|  3228K|    90   (3)| 00:00:02 |       |       |

--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - filter(TO_CHAR("STAFF_ID") LIKE '36%')

5 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM  (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "STAFF_ID","C1"

"PARTY_ID","C2" "BO_ID","C3" "PROD_ID","C4" "COMPLETE_DT" FROM "SYS"."SYS_TEMP_0FD9D662E_D625B872" "T1") "D"

WHERE (:B1='新装' OR :B2='移机' OR :B3='资费变更') AND TO_CHAR("D"."STAFF_ID") LIKE '36%' AND "D"."PARTY_ID"=:B4 AND

"D"."BO_ID"<>:B5 AND "D"."PROD_ID"<>:B6 AND "D"."COMPLETE_DT">:B7-INTERVAL'+07 00:00:00' DAY(2) TO SECOND(0)))

6 - access("A"."CHANNEL_ID"="C"."CHANNEL_ID")

7 - access("A"."PROD_ID"="B"."PROD_ID")

9 - filter("A"."PROD_ID" IS NOT NULL AND ("A"."BO_ACTION_NAME"='新装' OR "A"."BO_ACTION_NAME"='移机' OR

"A"."BO_ACTION_NAME"='资费变更') AND TO_CHAR("A"."SO_STAFF_ID") LIKE '36%')

10 - filter("B"."PROD_SPEC"='(新) 全省_紧密融合型E9套餐产品规格' OR "B"."PROD_SPEC"='ADSL' OR "B"."PROD_SPEC"='E6移动版' OR

"B"."PROD_SPEC"='E8 - 2S' OR "B"."PROD_SPEC"='E9版1M(老版)' OR "B"."PROD_SPEC"='LAN' OR "B"."PROD_SPEC"='普通E9' OR

"B"."PROD_SPEC"='普通电话' OR "B"."PROD_SPEC"='普通新版E8' OR "B"."PROD_SPEC"='全省_紧密融合型E9套餐产品规格' OR "B"."PROD_SPEC"='手机'

OR "B"."PROD_SPEC"='新春欢乐送之E6套餐' OR "B"."PROD_SPEC"='新春欢乐送之E8套餐')

12 - filter(:B1='新装' OR :B2='移机' OR :B3='资费变更')

13 - filter(TO_CHAR("D"."STAFF_ID") LIKE '36%' AND "D"."PARTY_ID"=:B1 AND "D"."BO_ID"<>:B2 AND

"D"."PROD_ID"<>:B3 AND "D"."COMPLETE_DT">:B4-INTERVAL'+07 00:00:00' DAY(2) TO SECOND(0))

统计信息

----------------------------------------------------------

2  recursive calls

29  db block gets

110506  consistent gets

22  physical reads

656  redo size

2438096  bytes sent via SQL*Net to client

449  bytes received via SQL*Net from client

11  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

49245  rows processed

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

更多的SQL优化案例 http://blog.csdn.net/robinson1988/article/category/621201 欢迎点评

牛保书
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
ORACLE-SQL性能优化(内部培训资料).docx
11-28
ORACLE SQL 性能优化系列 ...ORACLE SQL 性能优化是一个复杂的过程，需要从多方面入手，选择合适的优化器、访问表的方式、共享 SQL 语句等，并合理地使用其他优化技巧，以提高数据库的性能和效率。
在SQL Server中，关于with as使用介绍
weixin_30478923的博客
07-20 727
一．WITH AS的含义 WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。 特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行...
ORACLE WITH与性能优化
weixin_42156231的博客
05-23 1357
with as能够做到更好性能的原理是把一段查询sql先执行，把获取的数据放入temp表。 with as性能优化主要有两方面： 一方面，当一个with as定义的表名被调用两次以上时，oracle优化器会自动创建一个temp表，该with as语句只需要执行一遍，即可引用多次。如下两段代码，功能是一样的，但使用with as后tmp和tmp1语句在第一段皆只需执行一次，在第二段里tmp语句执行了两次而tmp1语句执行一次。 WITH TMP AS (SELECT * FROM TABLE1),
oracle的临时表和With As总结
Harry的博客
04-10 6594
ORACLE数据库除了可以保存永久表外，还可以建立临时表temporary tables。这些临时表用来保存一个会话SESSION的数据，或者保存在一个事务中需要的数据。当会话退出或者用户提交commit和回滚rollback事务的时候，临时表的数据自动清空，但是临时表的结构以及元数据还存储在用户的数据字典中。 1. 会话级临时表 会话级临时表是指临时表中的数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据。 Create Global Temporary Ta
oracle 常用写法--with as 用法，提高查询性能
汉南最後の読書人
02-26 2099
一．WITH AS的含义      WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。  特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分...
【ORACLE】Oracle提高篇之WITH AS
热门推荐
菜鸟sdut的博客
09-21 1万+
With as 在Oracle 中使用的还是很频繁的，我几乎稍微有点长的sql都会使用with as。不仅sql清晰，而且有的时候查询也很快
ORACLE-SQL性能优化大全.pdf
最新发布
02-23
ORACLE_SQL性能优化大全.pdf
Oracle-SQL优化.docx
09-12
Oracle SQL 优化是指对 Oracle 数据库中的 SQL 语句进行优化，以提高查询效率、减少资源占用和提高系统性能。下面是 Oracle SQL 优化的相关知识点： 一、 SQL 语句编写注意问题 1.1 IS NULL 与 IS NOT NULL 在...
oracle-sql优化
08-03
oracle_sql优化
Oracle_SQL-Optimization.rar_oracle_sql优化
09-20
综上所述，Oracle SQL优化是一个综合性的过程，涉及到多个层面，包括查询结构、索引设计、数据库配置和资源管理等。学习并掌握这些基础优化技术，将有助于提升Oracle数据库的运行效率，为业务系统提供更好的支撑。
oracle 使用with as,oracle中with as语句的使用
weixin_30767323的博客
04-04 1581
一. with as 简介：with as其实就是一个子查询， 使用它可以增强sql的可读性，同时因为该查询只执行一次，并将结果存储在用户临时表空间中，可以多次引用，增强性能。二. with as 用法：with temp as (select * from xxx)select * from temp;二. with as 实战：查询出部门的总薪水大于所有部门平均总薪水的部门：部门表s_dept...
oracle中with的用法及用处
weixin_37222169的博客
03-30 8067
======== WITH ======== 用于一个语句中某些中间结果放在临时表空间的SQL语句 如 WITH channel_summary AS ( SELECT channels.channel_desc, SUM(amount_sold) AS channel_total FROM sales, channels WHERE sales.channel_id = channels.channel_id GROUP BY channels.channel_desc ) SELECT channel.
记一次百万数据量查询sql 优化
weixin_39654646的博客
12-21 3437
记一次百万数据量查询sql 优化问题描述问题分析过程总结 问题描述 数据库用的是pgsql。sql 中用到了表连接，group by ，count以及sum 函数，现场测试的时候由于有定时任务在跑，所以数据库中一直有增量。当数据达到200万条时，页面加载时间非常慢，达到40s。 问题分析过程 本人开发小白一个，之前没有接触过太大数据量的业务，只有面试的时候自己看过相关的sql优化知识，而实践还是头...
oracle跑with语句很慢,oracle 11g新特性---WITH语句增强
weixin_32534943的博客
04-16 1004
--with语句增强，11gR2增强SQL> select * from test;ID NAME ADDR---------- -------------------- --------------------1 wzk chengdu2 cherry chengdu3 wzk...
使用Oracle with内嵌视图优化一例
wzy0623的专栏
12-28 1099
需求：有分类、物资、分类/物资关系三个表，要求按树的遍历方式查询出分类ID、分类/物资名称、从根到当前节点的路径。一个分类下的物资显示在该分类下，同一级分类按序号排序，一个分类下的物资按创建时间排序。[sql] view plain copy-- 创建分类表    CREATE TABLE tab_class  (     id               NUMBER (8) NOT NULL 
with as慢sql临时解决方案
s07aser123的专栏
12-28 1000
with as引起的慢sql
oraclewith嵌套with_使用oracle中的with从句来优化SQL语句
weixin_39949413的博客
12-22 205
oracle中的with从句是被设计来提高or查询性能的，如果你在你的查询语句中的一段(例如一段子查询)需要多次使用，那么你可以使用with从句将其命名待用，oracle将会将其存储在oracle的临时表空间里，然后在写查询语句的时候可以将其当作临时的表多次使用，而oracle将只进行一次计算，当然会提高查询性能。使用with从句的另一个好处是可以使我们的查询语句更加的条理化和清晰。下面是一个有关...
sql with as 用法_Oracle sql 语句中with....as的用法
weixin_39788960的博客
12-09 294
Oracle的SQL语句中，With查询语句不是以select开头，而是以with关键字开头。 with...as语句增加了SQL 的可读性，构造子查询，结构会更加清晰。可以“一次分析，多次使用”，可以提高性能，达到“少读”的目标。 具体使用with....as.....的SQL语句举例：with sql_conc_waits as (select sql_id, substr(sql_text...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值