处理半连接SQL自动改写内连接SQL一例

最新推荐文章于 2024-10-22 16:39:07 发布
最新推荐文章于 2024-10-22 16:39:07 发布
阅读量713 收藏
点赞数 1
分类专栏： Oracle sql优化 Oracle SQL 优化 文章标签： 半连接优化 in子查询
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wanbin6470398/article/details/78730657
版权

昨天刚写了半连接改写系列，今天就遇到了此类型SQL：
##优化前

耗时：28s
返回：0

SELECT D.DAILYAUDITNO, D.TRANSTOACC
  FROM PB_DOIC.MM_DAILYREPORT_TD D
 WHERE D.REPORTSTATUS = '4'
   AND D.TRANSTOACC IN ('00', 'B1')
   AND EXISTS (SELECT 1
          FROM PB_DOIC.MM_DAILYREPORT_DETAIL_TD
         WHERE DAILYAUDITNO = D.DAILYAUDITNO
           AND BUSINESSONE || BUSINESSTWO NOT IN
               ('604988', '605988', '606988', '607988')
           AND BUSINESSTWO != '991')
   AND NOT EXISTS
 (SELECT 1
          FROM PB_DOIC.MM_DAILYREPORT_DETAIL_TD E
         WHERE E.DAILYAUDITNO = D.DAILYAUDITNO
           AND E.BUSINESSONE || E.BUSINESSTWO NOT IN
               ('604988', '605988', '606988', '607988')
           AND E.BUSINESSTWO != '991'
           AND NOT EXISTS
         (SELECT 1
                  FROM PB_DOIC.MM_VOUCHERMODULE_TC V
                 WHERE V.BUSINESSTYPE = E.BUSINESSONE || E.BUSINESSTWO
                   AND V.MODULETYPE = '1'
                   AND V.MODULESTATUS = 0))
   FOR UPDATE OF D.TRANSTOACC NOWAIT
 
 Execution Plan
----------------------------------------------------------
Plan hash value: 915590932

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                              |    10 |  1160 | 20991   (2)| 00:04:12 |
|   1 |  FOR UPDATE                   |                              |       |       |            |          |
|   2 |   NESTED LOOPS                |                              |     1 |   116 | 20945   (2)| 00:04:12 |
|   3 |    SORT UNIQUE                |                              |    29 |  1943 | 20928   (2)| 00:04:12 |
|*  4 |     INDEX FAST FULL SCAN      | IDX_MM_DAILYREPORT_DETAIL_TD |    29 |  1943 | 20928   (2)| 00:04:12 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| MM_DAILYREPORT_TD            |     1 |    49 |     2   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN         | PK_MM_DAILYREPORT_TD         |     1 |       |     1   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN         | IDX_MM_DAILYREPORT_DETAIL_TD |     1 |    67 |     7   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN        | PK_MM_VOUCHERMODULE_TC       |     1 |    15 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

   4 - filter("BUSINESSONE"||"BUSINESSTWO"<>'604988' AND "BUSINESSONE"||"BUSINESSTWO"<>'605988' AND
              "BUSINESSONE"||"BUSINESSTWO"<>'606988' AND "BUSINESSONE"||"BUSINESSTWO"<>'607988' AND
              "BUSINESSTWO"<>'991')
   5 - filter(("D"."TRANSTOACC"='00' OR "D"."TRANSTOACC"='B1') AND "D"."REPORTSTATUS"='4')
   6 - access("DAILYAUDITNO"="D"."DAILYAUDITNO")
       filter( NOT EXISTS (SELECT /*+ */ 0 FROM "PB_DOIC"."MM_DAILYREPORT_DETAIL_TD" "SYS_ALIAS_2"
              WHERE "E"."DAILYAUDITNO"=:B1 AND "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'604988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'605988' AND "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'606988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'607988' AND "E"."BUSINESSTWO"<>'991' AND  NOT EXISTS (SELECT
              /*+ */ 0 FROM "PB_DOIC"."MM_VOUCHERMODULE_TC" "V" WHERE "V"."BUSINESSTYPE"=:B2||:B3 AND
              "V"."MODULETYPE"='1' AND "V"."MODULESTATUS"=0)))
   7 - access("E"."DAILYAUDITNO"=:B1)
       filter("E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'604988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'605988' AND "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'606988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'607988' AND "E"."BUSINESSTWO"<>'991' AND  NOT EXISTS (SELECT
              /*+ */ 0 FROM "PB_DOIC"."MM_VOUCHERMODULE_TC" "V" WHERE "V"."BUSINESSTYPE"=:B1||:B2 AND
              "V"."MODULETYPE"='1' AND "V"."MODULESTATUS"=0))
   8 - access("V"."MODULETYPE"='1' AND "V"."BUSINESSTYPE"=:B1||:B2 AND "V"."MODULESTATUS"=0)
       filter("V"."MODULESTATUS"=0)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    3809828  consistent gets
        347  physical reads
          0  redo size
        397  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

##分析

查看执行计划，发现ID=3处为 SORT UNIQUE ，而SQL语句并没有DISTINCT关键字，难道SQL进行自动改写了？

为了确认自己想法，我进行以下手动改写，把半连接手动改写成内连接：

改写之前，先确认表与表之间的关系：

select count(*),count(distinct DAILYAUDITNO) from MM_DAILYREPORT_DETAIL_TD;

COUNT(*)	COUNT(DISTINCTDAILYAUDITNO)
--------    -----------------------
5111081	    441898

select count(*),count(distinct DAILYAUDITNO) from MM_DAILYREPORT_TD;

COUNT(*)	COUNT(DISTINCTDAILYAUDITNO)
--------    -----------------------
441940      441940

--表MM_DAILYREPORT_TD与表MM_DAILYREPORT_DETAIL_TD是1:N的关系，所以改写时候，需要先去重，再连接。

SELECT D.DAILYAUDITNO, D.TRANSTOACC
  FROM PB_DOIC.MM_DAILYREPORT_TD D
 INNER JOIN (SELECT DISTINCT DAILYAUDITNO
               FROM PB_DOIC.MM_DAILYREPORT_DETAIL_TD
              WHERE BUSINESSONE || BUSINESSTWO NOT IN
                    ('604988', '605988', '606988', '607988')
                AND BUSINESSTWO != '991') TD
    ON D.DAILYAUDITNO = TD.DAILYAUDITNO
 WHERE D.REPORTSTATUS = '4'
   AND D.TRANSTOACC IN ('00', 'B1')
   AND NOT EXISTS
 (SELECT 1
          FROM PB_DOIC.MM_DAILYREPORT_DETAIL_TD E
         WHERE E.DAILYAUDITNO = D.DAILYAUDITNO
           AND E.BUSINESSONE || E.BUSINESSTWO NOT IN
               ('604988', '605988', '606988', '607988')
           AND E.BUSINESSTWO != '991'
           AND NOT EXISTS
         (SELECT 1
                  FROM PB_DOIC.MM_VOUCHERMODULE_TC V
                 WHERE V.BUSINESSTYPE = E.BUSINESSONE || E.BUSINESSTWO
                   AND V.MODULETYPE = '1'
                   AND V.MODULESTATUS = 0))
   FOR UPDATE OF D.TRANSTOACC NOWAIT;

Execution Plan
----------------------------------------------------------
Plan hash value: 1310135718

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                              |    25 |  2250 | 21076   (2)| 00:04:13 |
|   1 |  FOR UPDATE                   |                              |       |       |            |          |
|   2 |   NESTED LOOPS                |                              |     1 |    90 | 20959   (2)| 00:04:12 |
|   3 |    VIEW                       |                              |    29 |  1189 | 20929   (2)| 00:04:12 |
|   4 |     SORT UNIQUE               |                              |    29 |  1943 | 20929   (2)| 00:04:12 |
|*  5 |      INDEX FAST FULL SCAN     | IDX_MM_DAILYREPORT_DETAIL_TD |    29 |  1943 | 20928   (2)| 00:04:12 |
|*  6 |    TABLE ACCESS BY INDEX ROWID| MM_DAILYREPORT_TD            |     1 |    49 |     2   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | PK_MM_DAILYREPORT_TD         |     1 |       |     1   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN         | IDX_MM_DAILYREPORT_DETAIL_TD |     1 |    67 |     7   (0)| 00:00:01 |
|*  9 |       INDEX RANGE SCAN        | PK_MM_VOUCHERMODULE_TC       |     1 |    15 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

   5 - filter("BUSINESSONE"||"BUSINESSTWO"<>'604988' AND "BUSINESSONE"||"BUSINESSTWO"<>'605988' AND
              "BUSINESSONE"||"BUSINESSTWO"<>'606988' AND "BUSINESSONE"||"BUSINESSTWO"<>'607988' AND
              "BUSINESSTWO"<>'991')
   6 - filter(("D"."TRANSTOACC"='00' OR "D"."TRANSTOACC"='B1') AND "D"."REPORTSTATUS"='4')
   7 - access("D"."DAILYAUDITNO"="TD"."DAILYAUDITNO")
       filter( NOT EXISTS (SELECT /*+ */ 0 FROM "PB_DOIC"."MM_DAILYREPORT_DETAIL_TD" "SYS_ALIAS_2"
              WHERE "E"."DAILYAUDITNO"=:B1 AND "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'604988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'605988' AND "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'606988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'607988' AND "E"."BUSINESSTWO"<>'991' AND  NOT EXISTS (SELECT
              /*+ */ 0 FROM "PB_DOIC"."MM_VOUCHERMODULE_TC" "V" WHERE "V"."BUSINESSTYPE"=:B2||:B3 AND
              "V"."MODULETYPE"='1' AND "V"."MODULESTATUS"=0)))
   8 - access("E"."DAILYAUDITNO"=:B1)
       filter("E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'604988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'605988' AND "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'606988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'607988' AND "E"."BUSINESSTWO"<>'991' AND  NOT EXISTS (SELECT
              /*+ */ 0 FROM "PB_DOIC"."MM_VOUCHERMODULE_TC" "V" WHERE "V"."BUSINESSTYPE"=:B1||:B2 AND
              "V"."MODULETYPE"='1' AND "V"."MODULESTATUS"=0))
   9 - access("V"."MODULETYPE"='1' AND "V"."BUSINESSTYPE"=:B1||:B2 AND "V"."MODULESTATUS"=0)
       filter("V"."MODULESTATUS"=0)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    3809828  consistent gets
        431  physical reads
          0  redo size
        397  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

可以看出改写后的执行计划于改写之前执行计划基本一致，所以可以确定SQL是进行自动改写内连接而导致的性能问题。

##优化方法1：

耗时：0.4s
返回：0

--在半连接里加上ROWNUM>0,让SQL不进行展开。
SELECT D.DAILYAUDITNO, D.TRANSTOACC
  FROM PB_DOIC.MM_DAILYREPORT_TD D
 WHERE D.REPORTSTATUS = '4'
   AND D.TRANSTOACC IN ('00', 'B1')
  AND EXISTS (SELECT 1
          FROM PB_DOIC.MM_DAILYREPORT_DETAIL_TD
         WHERE DAILYAUDITNO = D.DAILYAUDITNO
           AND BUSINESSONE || BUSINESSTWO NOT IN
               ('604988', '605988', '606988', '607988')
           AND BUSINESSTWO != '991' AND ROWNUM>0)                                
   AND NOT EXISTS
 (SELECT 1
          FROM PB_DOIC.MM_DAILYREPORT_DETAIL_TD E
         WHERE E.DAILYAUDITNO = D.DAILYAUDITNO
           AND E.BUSINESSONE || E.BUSINESSTWO NOT IN
               ('604988', '605988', '606988', '607988')
           AND E.BUSINESSTWO != '991'
           AND NOT EXISTS
         (SELECT 1
                  FROM PB_DOIC.MM_VOUCHERMODULE_TC V
                 WHERE V.BUSINESSTYPE = E.BUSINESSONE || E.BUSINESSTWO
                   AND V.MODULETYPE = '1'
                   AND V.MODULESTATUS = 0))
   FOR UPDATE OF D.TRANSTOACC NOWAIT;
   
Execution Plan
----------------------------------------------------------
Plan hash value: 4088075167

------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                              |     1 |    49 |   336K  (1)| 01:07:15 |
|   1 |  FOR UPDATE           |                              |       |       |            |          |
|*  2 |   FILTER              |                              |       |       |            |          |
|*  3 |    TABLE ACCESS FULL  | MM_DAILYREPORT_TD            | 22372 |  1070K|  2632   (2)| 00:00:32 |
|   4 |    COUNT              |                              |       |       |            |          |
|*  5 |     FILTER            |                              |       |       |            |          |
|*  6 |      INDEX RANGE SCAN | IDX_MM_DAILYREPORT_DETAIL_TD |     1 |    67 |     7   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN | IDX_MM_DAILYREPORT_DETAIL_TD |     1 |    67 |     7   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN| PK_MM_VOUCHERMODULE_TC       |     1 |    15 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   2 - filter( EXISTS (SELECT /*+ */ 0 FROM "PB_DOIC"."MM_DAILYREPORT_DETAIL_TD"
              "MM_DAILYREPORT_DETAIL_TD" WHERE ROWNUM>0 AND "DAILYAUDITNO"=:B1 AND
              "BUSINESSONE"||"BUSINESSTWO"<>'604988' AND "BUSINESSONE"||"BUSINESSTWO"<>'605988' AND
              "BUSINESSONE"||"BUSINESSTWO"<>'606988' AND "BUSINESSONE"||"BUSINESSTWO"<>'607988' AND
              "BUSINESSTWO"<>'991') AND  NOT EXISTS (SELECT /*+ */ 0 FROM
              "PB_DOIC"."MM_DAILYREPORT_DETAIL_TD" "SYS_ALIAS_2" WHERE "E"."DAILYAUDITNO"=:B2 AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'604988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'605988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'606988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'607988' AND "E"."BUSINESSTWO"<>'991' AND  NOT EXISTS
              (SELECT /*+ */ 0 FROM "PB_DOIC"."MM_VOUCHERMODULE_TC" "V" WHERE "V"."BUSINESSTYPE"=:B3||:B4
              AND "V"."MODULETYPE"='1' AND "V"."MODULESTATUS"=0)))
   3 - filter(("D"."TRANSTOACC"='00' OR "D"."TRANSTOACC"='B1') AND "D"."REPORTSTATUS"='4')
   5 - filter(ROWNUM>0)
   6 - access("DAILYAUDITNO"=:B1)
       filter("BUSINESSONE"||"BUSINESSTWO"<>'604988' AND
              "BUSINESSONE"||"BUSINESSTWO"<>'605988' AND "BUSINESSONE"||"BUSINESSTWO"<>'606988' AND
              "BUSINESSONE"||"BUSINESSTWO"<>'607988' AND "BUSINESSTWO"<>'991')
   7 - access("E"."DAILYAUDITNO"=:B1)
       filter("E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'604988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'605988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'606988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'607988' AND "E"."BUSINESSTWO"<>'991' AND  NOT EXISTS
              (SELECT /*+ */ 0 FROM "PB_DOIC"."MM_VOUCHERMODULE_TC" "V" WHERE "V"."BUSINESSTYPE"=:B1||:B2
              AND "V"."MODULETYPE"='1' AND "V"."MODULESTATUS"=0))
   8 - access("V"."MODULETYPE"='1' AND "V"."BUSINESSTYPE"=:B1||:B2 AND "V"."MODULESTATUS"=0)
       filter("V"."MODULESTATUS"=0)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     105048  consistent gets
          0  physical reads
          0  redo size
        397  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

##优化方法2：

耗时：0.4s
返回：0

--添加hint: /*+ no_unnest */ 目的也是让半连接的sql不展开。与上面改写效果一致。

SELECT D.DAILYAUDITNO, D.TRANSTOACC
  FROM PB_DOIC.MM_DAILYREPORT_TD D
 WHERE D.REPORTSTATUS = '4'
   AND D.TRANSTOACC IN ('00', 'B1')
   AND EXISTS (SELECT /*+NO_UNNEST*/1
          FROM PB_DOIC.MM_DAILYREPORT_DETAIL_TD
         WHERE DAILYAUDITNO = D.DAILYAUDITNO
           AND BUSINESSONE || BUSINESSTWO NOT IN
               ('604988', '605988', '606988', '607988')
           AND BUSINESSTWO != '991')
   AND NOT EXISTS
 (SELECT 1
          FROM PB_DOIC.MM_DAILYREPORT_DETAIL_TD E
         WHERE E.DAILYAUDITNO = D.DAILYAUDITNO
           AND E.BUSINESSONE || E.BUSINESSTWO NOT IN
               ('604988', '605988', '606988', '607988')
           AND E.BUSINESSTWO != '991'
           AND NOT EXISTS
         (SELECT 1
                  FROM PB_DOIC.MM_VOUCHERMODULE_TC V
                 WHERE V.BUSINESSTYPE = E.BUSINESSONE || E.BUSINESSTWO
                   AND V.MODULETYPE = '1'
                   AND V.MODULESTATUS = 0))
   FOR UPDATE OF D.TRANSTOACC NOWAIT

Execution Plan
----------------------------------------------------------
Plan hash value: 991259826

-----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                              |     1 |    49 |   336K  (1)| 01:07:15 |
|   1 |  FOR UPDATE          |                              |       |       |            |          |
|*  2 |   FILTER             |                              |       |       |            |          |
|*  3 |    TABLE ACCESS FULL | MM_DAILYREPORT_TD            | 22372 |  1070K|  2632   (2)| 00:00:32 |
|*  4 |    INDEX RANGE SCAN  | IDX_MM_DAILYREPORT_DETAIL_TD |     1 |    67 |     7   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN | IDX_MM_DAILYREPORT_DETAIL_TD |     1 |    67 |     7   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN| PK_MM_VOUCHERMODULE_TC       |     1 |    15 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "PB_DOIC"."MM_DAILYREPORT_DETAIL_TD"
              "MM_DAILYREPORT_DETAIL_TD" WHERE "DAILYAUDITNO"=:B1 AND
              "BUSINESSONE"||"BUSINESSTWO"<>'604988' AND "BUSINESSONE"||"BUSINESSTWO"<>'605988' AND
              "BUSINESSONE"||"BUSINESSTWO"<>'606988' AND "BUSINESSONE"||"BUSINESSTWO"<>'607988' AND
              "BUSINESSTWO"<>'991') AND  NOT EXISTS (SELECT /*+ */ 0 FROM
              "PB_DOIC"."MM_DAILYREPORT_DETAIL_TD" "SYS_ALIAS_2" WHERE "E"."DAILYAUDITNO"=:B2 AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'604988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'605988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'606988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'607988' AND "E"."BUSINESSTWO"<>'991' AND  NOT EXISTS
              (SELECT /*+ */ 0 FROM "PB_DOIC"."MM_VOUCHERMODULE_TC" "V" WHERE "V"."BUSINESSTYPE"=:B3||:B4
              AND "V"."MODULETYPE"='1' AND "V"."MODULESTATUS"=0)))
   3 - filter(("D"."TRANSTOACC"='00' OR "D"."TRANSTOACC"='B1') AND "D"."REPORTSTATUS"='4')
   4 - access("DAILYAUDITNO"=:B1)
       filter("BUSINESSONE"||"BUSINESSTWO"<>'604988' AND
              "BUSINESSONE"||"BUSINESSTWO"<>'605988' AND "BUSINESSONE"||"BUSINESSTWO"<>'606988' AND
              "BUSINESSONE"||"BUSINESSTWO"<>'607988' AND "BUSINESSTWO"<>'991')
   5 - access("E"."DAILYAUDITNO"=:B1)
       filter("E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'604988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'605988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'606988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'607988' AND "E"."BUSINESSTWO"<>'991' AND  NOT EXISTS
              (SELECT /*+ */ 0 FROM "PB_DOIC"."MM_VOUCHERMODULE_TC" "V" WHERE "V"."BUSINESSTYPE"=:B1||:B2
              AND "V"."MODULETYPE"='1' AND "V"."MODULESTATUS"=0))
   6 - access("V"."MODULETYPE"='1' AND "V"."BUSINESSTYPE"=:B1||:B2 AND "V"."MODULESTATUS"=0)
       filter("V"."MODULESTATUS"=0)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     105048  consistent gets
          0  physical reads
          0  redo size
        397  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

##优化方法3：

耗时：0.4s
返回：0

添加hint：/*+ nl_sj */,目的让SQL走semi join

SELECT D.DAILYAUDITNO, D.TRANSTOACC
  FROM PB_DOIC.MM_DAILYREPORT_TD D
 WHERE D.REPORTSTATUS = '4'
   AND D.TRANSTOACC IN ('00', 'B1')
   AND EXISTS (SELECT /*+nl_sj*/ 1
          FROM PB_DOIC.MM_DAILYREPORT_DETAIL_TD
         WHERE DAILYAUDITNO = D.DAILYAUDITNO
           AND BUSINESSONE || BUSINESSTWO NOT IN
               ('604988', '605988', '606988', '607988')
           AND BUSINESSTWO != '991')
   AND NOT EXISTS
 (SELECT  1
          FROM PB_DOIC.MM_DAILYREPORT_DETAIL_TD E
         WHERE E.DAILYAUDITNO = D.DAILYAUDITNO
           AND E.BUSINESSONE || E.BUSINESSTWO NOT IN
               ('604988', '605988', '606988', '607988')
           AND E.BUSINESSTWO != '991'
           AND NOT EXISTS
         (SELECT 1
                  FROM PB_DOIC.MM_VOUCHERMODULE_TC V
                 WHERE V.BUSINESSTYPE = E.BUSINESSONE || E.BUSINESSTWO
                   AND V.MODULETYPE = '1'
                   AND V.MODULESTATUS = 0))
   FOR UPDATE OF D.TRANSTOACC NOWAIT

Execution Plan
----------------------------------------------------------
Plan hash value: 2686500646

-----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                              |    34 |  3944 |   137K  (1)| 00:27:29 |
|   1 |  FOR UPDATE          |                              |       |       |            |          |
|*  2 |   FILTER             |                              |       |       |            |          |
|   3 |    NESTED LOOPS SEMI |                              |    34 |  3944 |   137K  (1)| 00:27:27 |
|*  4 |     TABLE ACCESS FULL| MM_DAILYREPORT_TD            | 22372 |  1070K|  2632   (2)| 00:00:32 |
|*  5 |     INDEX RANGE SCAN | IDX_MM_DAILYREPORT_DETAIL_TD |     1 |    67 |     6   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN  | IDX_MM_DAILYREPORT_DETAIL_TD |     1 |    67 |     7   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN | PK_MM_VOUCHERMODULE_TC       |     1 |    15 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "PB_DOIC"."MM_DAILYREPORT_DETAIL_TD"
              "SYS_ALIAS_2" WHERE "E"."DAILYAUDITNO"=:B1 AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'604988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'605988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'606988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'607988' AND "E"."BUSINESSTWO"<>'991' AND  NOT EXISTS
              (SELECT /*+ */ 0 FROM "PB_DOIC"."MM_VOUCHERMODULE_TC" "V" WHERE "V"."BUSINESSTYPE"=:B2||:B3
              AND "V"."MODULETYPE"='1' AND "V"."MODULESTATUS"=0)))
   4 - filter(("D"."TRANSTOACC"='00' OR "D"."TRANSTOACC"='B1') AND "D"."REPORTSTATUS"='4')
   5 - access("DAILYAUDITNO"="D"."DAILYAUDITNO")
       filter("BUSINESSONE"||"BUSINESSTWO"<>'604988' AND
              "BUSINESSONE"||"BUSINESSTWO"<>'605988' AND "BUSINESSONE"||"BUSINESSTWO"<>'606988' AND
              "BUSINESSONE"||"BUSINESSTWO"<>'607988' AND "BUSINESSTWO"<>'991')
   6 - access("E"."DAILYAUDITNO"=:B1)
       filter("E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'604988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'605988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'606988' AND
              "E"."BUSINESSONE"||"E"."BUSINESSTWO"<>'607988' AND "E"."BUSINESSTWO"<>'991' AND  NOT EXISTS
              (SELECT /*+ */ 0 FROM "PB_DOIC"."MM_VOUCHERMODULE_TC" "V" WHERE "V"."BUSINESSTYPE"=:B1||:B2
              AND "V"."MODULETYPE"='1' AND "V"."MODULESTATUS"=0))
   7 - access("V"."MODULETYPE"='1' AND "V"."BUSINESSTYPE"=:B1||:B2 AND "V"."MODULESTATUS"=0)
       filter("V"."MODULESTATUS"=0)


Statistics
----------------------------------------------------------
       2114  recursive calls
          0  db block gets
      83115  consistent gets
       1341  physical reads
          0  redo size
        397  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         77  sorts (memory)
          0  sorts (disk)
          0  rows processed
DB-Engineer
关注
专栏目录
NL2SQL技术方案系列(4)：金融领域NL2SQL技术方案以及行业案例实战讲解2
丨汀、的博客
04-24 425
NL2SQL技术方案系列(4)：金融领域NL2SQL技术方案以及行业案例实战讲解2
【in - exists-join(distinct)】SQL调优改写一例
Ask Self，Ask Tomorrow
07-10 306
相关数据已经脱敏处理 原SQLMERGE INTO IntoTable t USING ( SELECT x.PID, b.SEC_ID, b.T_SYB, b.E_CD, x.END_DATE, x.xh 区间级别, isnull(x.hj, 0) 区间人数 FROM
sql内连接和外连接
12-14
主要讲述了使用select进行内、外连接查询的内容
【SQL】表连接 --半连接
cojm55771的博客
09-06 397
半连接： 当一张表在另一张表找到匹配的记录之后，半连接（semi-jion）返回第一张表中的记录。与条件连接相反，即使在右节点中找到几条匹配的记录，左节点的表也只会返回一条记录。另外，右节点的表一条记录也不会返回。半连接通常使...
SQL语句学习（五）子查询、半连接
zhaoxia_iii的博客
05-08 338
若要取name则会返回第一条和第三条数据，但是他们并不是真正的最大学号，所以不能通过这种方式获取名字。= 等于号的子查询只能返回一个数字，但是如果用in，子查询可以返回一列数值。子查询如果在where或select中时相当于子查询先计算结果再判断。题目：找到每个classid中最大的id对应的信息。半连接一般的形式是在子查询内 引用副查询的表信息。题目：找到每个班级学生的最大学号对应的学生信息。题目：找出每个部门工资最高的员工。题目：找到小蓝带的所有学生。
SQL 改写系列十：半连接转内连接
OceanBase分布式数据库的博客
09-16 683
本文主要介绍OceanBase的半连接转内连接改写，以及这个改写的优化点、容易被忽略的错误。OceanBase会把满足一定条件的半连接转换成内连接，使优化器能够尝试更多的计划，生成的查询计划可能更优。
【3.16数据库作业7】SQL练习4 – SELECT（连接查询、嵌套查询）
12-14
在本篇【3.16数据库作业7】的SQL练习中，主要涵盖了两个核心概念：连接查询和嵌套查询。连接查询是SQL中用于合并来自多个表的数据的关键操作，而嵌套查询则涉及到在主查询中使用子查询来进一步筛选数据。 首先，...
java连接sql server数据库的代码如何改成连接mysql_连接sqlserver数据库代码
weixin_33764069的博客
01-27 1521
Sql Server的JDBC测试程序与远程连接参考文献：1.MySql与oracle的JDBC测试程序2.查看sqlserver的端口号代码示例在我的机器上安装了多个版本的sqlserver数据库，那么如何区分这两个数据库服务器呢。这就要用到前一篇博客中提到的数据库服务端口号，详细见参考2。此处端口号5419对应的是sqlserve...文章嗯哼99252017-12-271167浏览量webl...
基于关系型代数的 SQL 等价改写
wujiandao的专栏
09-12 841
点击蓝色“有关SQL”关注我哟加个“星标”，天天与10000人一起快乐成长看过我那篇《SQL 数据库小白，从入门到精通》的朋友，一定不会陌生，SQL 的数学原理，就是集合运算。集合运算, ...
一条半连接SQL的优化过程
bisal的专栏
05-12 498
最近一直在看落落老师的书，受益匪浅，举其中提到的一个案例。直接通过模拟实验来说明，在Oracle11.2.0.4下，创建两张表，SQL>createtableaassel...
内连接（sql）
m0_46639782的博客
06-12 1446
上述语句将 orders 表和 customers 表基于 customer_id 列进行等值连接，返回订单号、客户名称和订单日期。上述语句将 products 表和 suppliers 表基于 supplier_id 列进行非等值连接，返回产品名称和供应商名称。上述语句将 employees 表自连接，基于 manager_id 列进行等值连接，返回员工姓名和经理姓名。其中，t1 和 t2 为同一张表的别名(起别名可以用as关键字，或者用空格隔开)。等值连接的语法格式为：（sql99语法）
SQL的内连接
大大的直板
03-06 703
/*SQL的内连接*/ /* 内连接使用比较运算符根据每个表共有的列的值匹配两个表中的行 */ /*语法① select 表1.列名,表2.列名 from 表1,表2 where 表1.有关系的列名=表2.有关系的列名 */ /*例：在学生表和成绩表中，查询他们的姓名和对应的成绩*/ select Students.name,Chengji.ch...
SQL内连接
QingShangIei的博客
03-27 4651
开发工具与关键技术：sql 撰写时间：2022/3/27 内连接（可多个表连接）：两表合并为1表 内连接格式1： select 字段 from 表1，表2 where 连接条件 或者 ： select 字段 from 表1 inner join 表2 on 连接条件， ...
SQL中的内连接
a1078285909的博客
12-22 2111
SQL中的标准写法 select * form 表1 inner join 表2 on 表1.字段 = 表2.字段 1、 inner 可以省略 2、 内连接特点：查询结果必须满足条件，不满足条件的数据将会丢失
SQL 干货 | SQL 半连接
最新发布
Navicat 官方账号
10-22 1410
在今天的博客中，我们学习了如何使用 ANSI SQL 语法模拟半连接。除了在“正确性”方面是最佳解决方案外，使用“半”连接（SEMI）而不是内连接（INNER JOIN）在性能上也有一些好处，因为数据库可以在找到第一个匹配项后立即停止寻找匹配项。
论表与表之间的关系--半连接改写
风中清淡
12-05 634
论表与表之间的关系--半连接改写
SQL-多表查询-内连接
CMD138的博客
11-17 2262
通过本篇博客，我们详细介绍了SQL内连接的概念、语法和应用场景。内连接通过比较两个或多个表的列值，返回满足连接条件的结果行。通过实例分析，我们展示了如何使用内连接查询订单和客户的信息。希望本篇博客对于理解和应用SQL内连接有所帮助。
SQL查询优化技巧：索引设计与性能提升
以表`record`为例，如果在`date`列上创建了一个非聚集索引，对于查询`date`在特定范围内的记录，例如`date > '19991201' and date 且`amount > 2000`的情况，非聚集索引可能导致表扫描，从而造成查询耗时25秒。...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值