处理半连接SQL自动改写内连接SQL一例
来源：互联网 发布：层次聚类算法的优缺点 编辑：程序博客网 时间：2024/06/07 07:45
昨天刚写了半连接改写系列，今天就遇到了此类型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 441898select 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 NOWAITExecution 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 joinSELECT 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 NOWAITExecution 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 阅读全文
1 0
- 处理半连接SQL自动改写内连接SQL一例
- 【SQL】连接（一）：内连接
- SQL 内连接,外连接
- sql 内连接 外连接
- SQL 内连接,外连接
- SQL 内连接、外连接
- SQL 内连接 外连接
- SQL 内连接 外连接
- sql内连接，外连接
- sql内自连接
- SQL 之 内连接
- SQL 一 (有关内连接、左、右连接、最大最小值)
- SQL内连接 外连接 自连接
- SQL内连接、外连接、全连接
- sql 内连接，左连接，右连接
- SQL外连接、内连接、交叉连接
- SQL 左连接 右连接 内连接
- sql内链接，外连接
- 阿里云 ubuntu 16.04 系统突然 Read-only file system
- 你需要理解的 Java 反射机制知识总结
- Android调用js方法传递参数自动计算问题的解决
- Linux下Gitbook生成pdf及中文问题的解决
- 隐藏自已，做好清理
- 处理半连接SQL自动改写内连接SQL一例
- 用CamOdoCal 进行鱼眼相机的标定
- 主题模型（LDA）(一)--通俗理解与简单应用
- mui图片轮播ajax动态加载后不轮播
- Linux-VI编辑器
- 二维数组排序
- 微服务生态总结
- hive hbase 映射表
- websrvice 学习
