SQL中带有NOT IN 子查询改写

最新推荐文章于 2024-04-24 17:32:33 发布
最新推荐文章于 2024-04-24 17:32:33 发布
阅读量1.4w 收藏 8
点赞数 2
分类专栏： Oracle sql优化 Oracle SQL 优化 文章标签： not-in子查询 sql优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wanbin6470398/article/details/78709187
版权

报表程序中的一段SQL运行很慢，代码如下：

优化前：

耗时：1337s

INSERT INTO PER_LTE_ZIB_PB_COMMISSION_07
  SELECT P.TOPACTUALID,
         Q.POLICYNO,
         Q.ENDORSEMENTNO,
         CASE
           WHEN Q.ENDORSEMENTSTATUS = '$$900002107001' THEN
            '1'
           ELSE
            NULL
         END AS ENDORSE_CFLAG,
         P.ROLEID,
         P.OURFLAG,
         P.HOSTFLAG,
         P.AMOUNTOFPOLICYISSUINGFEES AS AMOUNTOFPOLICYISSUINGFEES,
         P.UWCODE,
         P.UWNAME,
         '04' AS DATATYPE,
         P.CURRENCYCODE,
         T3.TOPACTUALID AS NEXTTOPACTUALID,
         P.PAYEE,
         P.PAYEENAME
    FROM PER_LTE_ZIB_PB_COMMISSION_06 P
    LEFT JOIN (SELECT POLICYNO,
                      '0' AS ENDORSEMENTNO,
                      TOPACTUALID,
                      POLICYID,
                      NULL AS ENDORSEMENTSTATUS
                 FROM RPT_ST.POLICY
                WHERE POLICYNO IS NOT NULL
                  AND ENDORSEMENTID IS NULL
                  AND POLICYSTATUS IS NOT NULL
               UNION ALL
               SELECT T1.POLICYNO,
                      T2.ENDORSEMENTNO,
                      T1.TOPACTUALID,
                      POLICYID,
                      T2.ENDORSEMENTSTATUS
                 FROM RPT_ST.POLICY T1
                 LEFT JOIN RPT_ST.ENDORSEMENT T2
                   ON T1.ENDORSEMENTID = T2.ENDORSEMENTID
                WHERE T1.POLICYNO IS NOT NULL
                  AND T2.ENDORSEMENTNO IS NOT NULL
                  AND POLICYSTATUS IS NOT NULL
                  AND T2.ENDORSEMENTSTATUS <> '$$900002107001') Q
      ON P.TOPACTUALID = Q.TOPACTUALID
    LEFT JOIN (SELECT S.TOPACTUALID,
                      S.POLICYID,
                      S.POLICYSTATUS,
                      NULL AS PREPOLICYID
                 FROM RPT_ST.POLICY S
                WHERE POLICYNO IS NOT NULL
                  AND ENDORSEMENTID IS NULL
                  AND POLICYSTATUS IS NOT NULL
               UNION ALL
               SELECT T1.TOPACTUALID,
                      T1.POLICYID,
                      T1.POLICYSTATUS,
                      T2.PREPOLICYID
                 FROM RPT_ST.POLICY T1
                 LEFT JOIN RPT_ST.ENDORSEMENT T2
                   ON T1.ENDORSEMENTID = T2.ENDORSEMENTID
                WHERE T1.POLICYNO IS NOT NULL
                  AND T2.ENDORSEMENTNO IS NOT NULL
                  AND POLICYSTATUS IS NOT NULL
                  AND T2.ENDORSEMENTSTATUS <> '$$900002107001') T3
      ON Q.POLICYID = T3.PREPOLICYID
     AND T3.POLICYSTATUS IS NOT NULL
   WHERE P.TOPACTUALID IN
         (SELECT TOPACTUALID
            FROM PER_LTE_ZIB_PB_COMMISSION_06 P
           WHERE P.OURFLAG || NVL(P.HOSTFLAG, 0) = '$$1000090000011')
     AND P.ROLEID NOT IN
         (SELECT ROLEID
            FROM PER_LTE_ZIB_PB_COMMISSION_06 P
           WHERE P.OURFLAG || NVL(P.HOSTFLAG, 0) = '$$1000090000011')
     AND Q.POLICYNO IS NOT NULL

Plan hash value: 21661008

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
---------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT     |                              |       |       |  9145 (100)|          |        |      |
|*  1 |  FILTER              |                              |       |       |            |          |        |      |
|*  2 |   HASH JOIN SEMI     |                              |     1 |   451 |  8782   (1)| 00:01:46 |        |      |
|*  3 |    HASH JOIN         |                              |     2 |   854 |  8418   (1)| 00:01:42 |        |      |
|*  4 |     HASH JOIN OUTER  |                              |     1 |   260 |  8056   (1)| 00:01:37 |        |      |
|   5 |      VIEW            |                              |     1 |   192 |  4028   (1)| 00:00:49 |        |      |
|   6 |       UNION-ALL      |                              |       |       |            |          |        |      |
|   7 |        REMOTE        | POLICY                       |     1 |   143 |  4028   (1)| 00:00:49 |  ENOLC | R->S |
|   8 |        REMOTE        |                              |       |       |            |          |  ENOLC | R->S |
|   9 |      VIEW            |                              |     1 |    68 |  4028   (1)| 00:00:49 |        |      |
|  10 |       UNION-ALL      |                              |       |       |            |          |        |      |
|  11 |        REMOTE        | POLICY                       |     1 |   130 |  4028   (1)| 00:00:49 |  ENOLC | R->S |
|  12 |        REMOTE        |                              |       |       |            |          |  ENOLC | R->S |
|  13 |     TABLE ACCESS FULL| PER_LTE_ZIB_PB_COMMISSION_06 | 65448 |    10M|   361   (1)| 00:00:05 |        |      |
|* 14 |    TABLE ACCESS FULL | PER_LTE_ZIB_PB_COMMISSION_06 |   654 | 15696 |   363   (2)| 00:00:05 |        |      |
|* 15 |   TABLE ACCESS FULL  | PER_LTE_ZIB_PB_COMMISSION_06 |     1 |    24 |   363   (2)| 00:00:05 |        |      |
---------------------------------------------------------------------------------------------------------------------

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

   1 - filter( IS NULL)
   2 - access("P"."TOPACTUALID"="TOPACTUALID")
   3 - access("P"."TOPACTUALID"="Q"."TOPACTUALID")
   4 - access("Q"."POLICYID"="T3"."PREPOLICYID")
  14 - filter("P"."OURFLAG"||NVL("P"."HOSTFLAG",'0')='$$1000090000011')
  15 - filter(("P"."OURFLAG"||NVL("P"."HOSTFLAG",'0')='$$1000090000011' AND LNNVL("ROLEID"<>:B1)))

分析

分析SQL代码，其中where 条件中有NOT IN（select roleid from per..）子查询。
去掉not in子查询，进行查询，能在3s内结果，由此该SQL性能瓶颈就出在NOT IN子查询上。
而NOT IN子查询，可以等价改写成left join，改写形式如下：

P.ROLEID NOT IN
         (SELECT ROLEID
            FROM PER_LTE_ZIB_PB_COMMISSION_06 P
           WHERE P.OURFLAG || NVL(P.HOSTFLAG, 0) = '$$1000090000011')

改写成：

LEFT JOIN (SELECT ROLEID
            FROM PER_LTE_ZIB_PB_COMMISSION_06 P
           WHERE P.OURFLAG || NVL(P.HOSTFLAG, 0) = '$$1000090000011') D
        ON D.ROLEID=P.ROLEID  
 where  D.ROLEID IS NULL

整体改下SQL如下：

INSERT INTO PER_LTE_ZIB_PB_COMMISSION_07
  SELECT P.TOPACTUALID,
         Q.POLICYNO,
         Q.ENDORSEMENTNO,
         CASE
           WHEN Q.ENDORSEMENTSTATUS = '$$900002107001' THEN
            '1'
           ELSE
            NULL
         END AS ENDORSE_CFLAG,
         P.ROLEID,
         P.OURFLAG,
         P.HOSTFLAG,
         P.AMOUNTOFPOLICYISSUINGFEES AS AMOUNTOFPOLICYISSUINGFEES,
         P.UWCODE,
         P.UWNAME,
         '04' AS DATATYPE,
         P.CURRENCYCODE,
         T3.TOPACTUALID AS NEXTTOPACTUALID,
         P.PAYEE,
         P.PAYEENAME
    FROM PER_LTE_ZIB_PB_COMMISSION_06 P
    LEFT JOIN (SELECT POLICYNO,
                      '0' AS ENDORSEMENTNO,
                      TOPACTUALID,
                      POLICYID,
                      NULL AS ENDORSEMENTSTATUS
                 FROM RPT_ST.POLICY
                WHERE POLICYNO IS NOT NULL
                  AND ENDORSEMENTID IS NULL
                  AND POLICYSTATUS IS NOT NULL
               UNION ALL
               SELECT T1.POLICYNO,
                      T2.ENDORSEMENTNO,
                      T1.TOPACTUALID,
                      POLICYID,
                      T2.ENDORSEMENTSTATUS
                 FROM RPT_ST.POLICY T1
                 LEFT JOIN RPT_ST.ENDORSEMENT T2
                   ON T1.ENDORSEMENTID = T2.ENDORSEMENTID
                WHERE T1.POLICYNO IS NOT NULL
                  AND T2.ENDORSEMENTNO IS NOT NULL
                  AND POLICYSTATUS IS NOT NULL
                  AND T2.ENDORSEMENTSTATUS <> '$$900002107001') Q
      ON P.TOPACTUALID = Q.TOPACTUALID
    LEFT JOIN (SELECT S.TOPACTUALID,
                      S.POLICYID,
                      S.POLICYSTATUS,
                      NULL AS PREPOLICYID
                 FROM RPT_ST.POLICY S
                WHERE POLICYNO IS NOT NULL
                  AND ENDORSEMENTID IS NULL
                  AND POLICYSTATUS IS NOT NULL
               UNION ALL
               SELECT T1.TOPACTUALID,
                      T1.POLICYID,
                      T1.POLICYSTATUS,
                      T2.PREPOLICYID
                 FROM RPT_ST.POLICY T1
                 LEFT JOIN RPT_ST.ENDORSEMENT T2
                   ON T1.ENDORSEMENTID = T2.ENDORSEMENTID
                WHERE T1.POLICYNO IS NOT NULL
                  AND T2.ENDORSEMENTNO IS NOT NULL
                  AND POLICYSTATUS IS NOT NULL
                  AND T2.ENDORSEMENTSTATUS <> '$$900002107001') T3
      ON Q.POLICYID = T3.PREPOLICYID
     AND T3.POLICYSTATUS IS NOT NULL    
   LEFT JOIN (SELECT ROLEID
            FROM PER_LTE_ZIB_PB_COMMISSION_06 P
           WHERE P.OURFLAG || NVL(P.HOSTFLAG, 0) = '$$1000090000011') D
        ON D.ROLEID=P.ROLEID       
   WHERE P.TOPACTUALID IN
         (SELECT TOPACTUALID
            FROM PER_LTE_ZIB_PB_COMMISSION_06 P
           WHERE P.OURFLAG || NVL(P.HOSTFLAG, 0) = '$$1000090000011')
                AND Q.POLICYNO IS NOT NULL
                AND D.ROLEID IS NULL

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT      |                              |     1 |   475 |  9145   (1)| 00:01:50 |        |      |
|*  1 |  HASH JOIN SEMI       |                              |     1 |   475 |  9145   (1)| 00:01:50 |        |      |
|*  2 |   FILTER              |                              |       |       |            |          |        |      |
|*  3 |    HASH JOIN OUTER    |                              |     1 |   451 |  8781   (1)| 00:01:46 |        |      |
|*  4 |     HASH JOIN         |                              |     2 |   854 |  8418   (1)| 00:01:42 |        |      |
|*  5 |      HASH JOIN OUTER  |                              |     1 |   260 |  8056   (1)| 00:01:37 |        |      |
|   6 |       VIEW            |                              |     1 |   192 |  4028   (1)| 00:00:49 |        |      |
|   7 |        UNION-ALL      |                              |       |       |            |          |        |      |
|   8 |         REMOTE        | POLICY                       |     1 |   143 |  4028   (1)| 00:00:49 |  ENOLC | R->S |
|   9 |         REMOTE        |                              |       |       |            |          |  ENOLC | R->S |
|  10 |       VIEW            |                              |     1 |    68 |  4028   (1)| 00:00:49 |        |      |
|  11 |        UNION-ALL      |                              |       |       |            |          |        |      |
|  12 |         REMOTE        | POLICY                       |     1 |   130 |  4028   (1)| 00:00:49 |  ENOLC | R->S |
|  13 |         REMOTE        |                              |       |       |            |          |  ENOLC | R->S |
|  14 |      TABLE ACCESS FULL| PER_LTE_ZIB_PB_COMMISSION_06 | 65448 |    10M|   361   (1)| 00:00:05 |        |      |
|* 15 |     TABLE ACCESS FULL | PER_LTE_ZIB_PB_COMMISSION_06 |   654 | 15696 |   363   (2)| 00:00:05 |        |      |
|* 16 |   TABLE ACCESS FULL   | PER_LTE_ZIB_PB_COMMISSION_06 |   654 | 15696 |   363   (2)| 00:00:05 |        |      |
----------------------------------------------------------------------------------------------------------------------

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

   1 - access("P"."TOPACTUALID"="TOPACTUALID")
   2 - filter("ROLEID" IS NULL)
   3 - access("ROLEID"(+)="P"."ROLEID")
   4 - access("P"."TOPACTUALID"="Q"."TOPACTUALID")
   5 - access("Q"."POLICYID"="T3"."PREPOLICYID"(+))
  15 - filter("P"."OURFLAG"(+)||NVL("P"."HOSTFLAG"(+),'0')='$$1000090000011')
  16 - filter("P"."OURFLAG"||NVL("P"."HOSTFLAG",'0')='$$1000090000011')

Remote SQL Information (identified by operation id):
----------------------------------------------------

   8 - SELECT "POLICYID","POLICYNO","POLICYSTATUS","TOPACTUALID","ENDORSEMENTID" FROM "CUAN_DOIC"."POLICY"
       "POLICY" WHERE "ENDORSEMENTID" IS NULL AND "POLICYNO" IS NOT NULL AND "POLICYSTATUS" IS NOT NULL (accessing
       'ENOLC' )

   9 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT "A2"."POLICYNO","A1"."ENDORSEMENTNO","A2"."TOPACTUALID","A2".
       "POLICYID","A1"."ENDORSEMENTSTATUS" FROM "CUAN_DOIC"."POLICY" "A2","CUAN_DOIC"."ENDORSEMENT" "A1" WHERE
       "A2"."POLICYNO" IS NOT NULL AND "A1"."ENDORSEMENTNO" IS NOT NULL AND "A2"."POLICYSTATUS" IS NOT NULL AND
       "A1"."ENDORSEMENTSTATUS"<>'$$900002107001' AND "A2"."ENDORSEMENTID"="A1"."ENDORSEMENTID" AND "A2"."POLICYNO"
       IS NOT NULL (accessing 'ENOLC' )

  12 - SELECT "POLICYNO","POLICYSTATUS","TOPACTUALID","ENDORSEMENTID" FROM "CUAN_DOIC"."POLICY" "S" WHERE
        "ENDORSEMENTID" IS NULL AND "POLICYNO" IS NOT NULL AND "POLICYSTATUS" IS NOT NULL (accessing 'ENOLC' )

  13 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT "A2"."TOPACTUALID","A2"."POLICYID","A2"."POLICYSTATUS","A1"."
        PREPOLICYID" FROM "CUAN_DOIC"."POLICY" "A2","CUAN_DOIC"."ENDORSEMENT" "A1" WHERE "A2"."POLICYNO" IS NOT NULL
        AND "A1"."ENDORSEMENTNO" IS NOT NULL AND "A2"."POLICYSTATUS" IS NOT NULL AND
        "A1"."ENDORSEMENTSTATUS"<>'$$900002107001' AND "A2"."ENDORSEMENTID"="A1"."ENDORSEMENTID" AND
        "A2"."POLICYSTATUS" IS NOT NULL (accessing 'ENOLC' )

优化后

耗时：4s

DB-Engineer
关注
  • 2
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
sql not in 与not exists使用中的细微差别
12-15
not exists的子查询，对于子查询不返回行和子查询返回行的查询结果是有区别的 这些细小的差别千万不要被我们所忽视，一旦项目庞大了，想跟踪到具体的错误所花费的时间也是可观的。尽量把这些不必要的错误扼杀在摇篮...
SQLServer Execpt和not in 性能区别
12-15
在SQL Server中，`EXCEPT` 和 `NOT IN` 是两个用于查询数据的子句，它们都可以用来找出在一个集合中但不在另一个集合中的数据。然而，它们在性能方面可能存在显著的差异，尤其是在处理大量数据时。本文将深入探讨这...
sql----改写 in ,exists,not in ,not exists
数据库专栏
05-23 951
建立测试用表
not in、not exists 及用join改写
有教无类的专栏
11-13 5284
/*我们接着用上次建的两个表。数据如下*/ SQL> select * from l; STR   V ------ - left_1 1 left_2 2 left_3 3 left_4 4   SQL> select * from r; STR    V ------- - right_3 3 right_4 4 right_5 5 right_6 6   /
sql in 改写 join的几种方法
最新发布
关光磊的博客
04-24 184
【代码】sql in 改写 join的几种方法。
sql语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句
01-19
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接。在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率。在子查询中,NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。 如 我要查询 Sendorder表中的冗余数据（没有和reg_person或worksite相连的数据） sql=”select Sendorder.id,Sendorder.reads,Sendor
sql not in 的坑及改写为join
weixin_30642029的博客
08-08 437
/*我们接着用上次建的两个表。数据如下*/ SQL> select * from l; STRV ------ - left_1 1 left_2 2 left_3 3 left_4 4 SQL> select * from r; STRV ------- - right_3 3 right_4 4 right_5 5 righ...
Hive中not in的正确使用
晓晓的天空
07-14 4873
Hive中not in的正确使用
改写优化SQL（2）：not in改为left join
SQL Server探索
01-24 9299
在sql语句中，not in是经常会用到的一种写法，因为这种写法很直观，容易理解。 但如果不注意的话，很容易写出错误的sql，而且性能存在严重问题，所以，不建议使用not in，要尽量把 not in写法，改为left join。 一、建表 CREATE TABLE tb_emp ( emp_id INT NOT NULL PRIMARY KEY CLUSTERED, emp_name
not in / not exists /外连接 互相改写
wbaug20th的专栏
12-06 912
SQL> select count(*) from employees where department_id is null; COUNT(*) ---------- 1 SQL> select count(*) from departments where department_id is null; COUNT(*) ---------- 1
Mysql子查询IN中使用LIMIT应用示例
09-10
初版SQL语句尝试在`IN`子查询中直接使用`LIMIT`，但这样做会导致错误。 错误的SQL语句如下： ```sql WHERE mapply.aid IN (SELECT aid FROM `mapply` WHERE state = $state ORDER BY `atime`, `uid` DESC LIMIT 0,...
SQLServer 优化SQL语句 in 和not in的替代方案
12-15
但是用IN的SQL性能总是比较低的，从SQL执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别： SQL试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多...
多个字段的in 和 not in 及其替代写法（exists，not exists）
热门推荐
mok的专栏
01-07 7万+
首先声明：数据库高手请走开，您看了碍眼啊！ 有些时候进行查询的时候啊，一个字段的in、 not in根本不能满足要求，非常需要类似多个字段的in、not in，但是很多数据库不支持多个字段的in、 not in （DB2是支持的）,估计也让不少朋友们郁闷吧!不过没关系，我这不写文章了嘛，呵呵！ 我用过的数据库有Access，SQL Server，DB2，见笑见笑啊！其实也就SQL Server...
SQL中NOT IN一个致命的问题
09-13 3908
今天项目中遇到一个问题导致系统即将瘫痪，排查的好久最后发现是SQL中NOT IN写法问题导致的，所以在这里总结一下。首先，EXISTS与IN和用法想必大家都非常清楚，百度一下，网上也有很多介绍，一般情况下EXISTS与IN的使用效率的问题，通常情况下采用EXISTS要比IN效率高，因为IN不走索引，但要看实际情况具体使用：IN适合于外表大而内表小的情况，而EXISTS适合于外表小而内表大的情况。但有一种情况，NOTEXISTS和NOT IN的SQL查询中子查询包含NULL，这样的话两者查询...
hive不能用in或者 not in 子查询时怎么办
weixin_43792309的博客
06-08 3314
1111111111111111111111
MySQL not in嵌套查询改写成外连接方式
cuantangnie3915的博客
08-18 595
在MySQL中，not in 嵌套查询会在数据库里面创建一张临时表，导致执行效率很低，可以改成外连接的方式处理，效率会好很多。 not in方式 mysql> s...
分析函数改写not in
ciqiyuan9775的博客
08-13 150
1.OLD: SELECTcard.c_cust_id,card.TYPE,card.n_all_money FROMcard WHEREcard.c_cust_idNOTIN(S...
sql 嵌套查询not in
04-20
嵌套查询是指在一个查询语句中嵌套另一个查询语句，将内部查询的结果作为外部查询的条件之一。而"NOT IN"是一种条件运算符，用于判断某个值是否不在指定的列表中。 在SQL中，嵌套查询可以使用子查询来实现。而"NOT IN"运算符可以与子查询结合使用，用于排除满足子查询条件的结果。 下面是一个示例，演示了如何使用嵌套查询和"NOT IN"运算符： 假设我们有两个表：Customers（客户表）和 Orders（订单表）。我们想要找出没有下过订单的客户。可以使用嵌套查询和"NOT IN"运算符来实现： ``` SELECT CustomerName FROM Customers WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders); ``` 上述查询首先执行内部的子查询，获取所有已经下过订单的客户ID。然后，外部查询使用"NOT IN"运算符，排除这些客户ID，返回没有下过订单的客户名字。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值