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

昨天刚写了半连接改写系列,今天就遇到了此类型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   
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值