Delete 语句带有子查询的sql优化
背景：
接到开发通知，应用页面打不开，让我协助。。。（开发跟我说，表GV_BOOKS一直有锁，锁了有1个多小时了，问我能不能把锁释放掉，我回答他们说，这肯定是sql性能问题，表上有锁是正常现象，不是锁导致的sql执行不出来）。
利用工具，追踪到以下sql。
--sql代码
DELETE GV_BOOKS
WHERE ACCOUNTID IN
(SELECT ACCOUNTID
FROM GV_BOOKS
MINUS
SELECT A.ACCOUNTID
FROM GV_ACCOUNTS A, VW_BP_ACCOUNT_SYN B
WHERE A.DBLINK = B.DB_LINK
AND A.ACCOUNTNO = B.ACNTNO
MINUS
SELECT A.ACCOUNTID
FROM GV_ACCOUNTS A, VW_CNTACNT_GVIEW B
WHERE A.DBLINK = B.DB_LINK
AND A.ACCOUNTNO = B.NO);
--执行计划
Plan hash value: 1376647110
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 110M| 2734M| 1129 (19)| 00:00:14 |
| 1 | DELETE | GV_BOOKS | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | GV_BOOKS | 86600 | 2198K| 104 (2)| 00:00:02 |
| 4 | MINUS | | | | | |
| 5 | MINUS | | | | | |
| 6 | SORT UNIQUE NOSORT | | 1274 | 5096 | 7 (15)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IX_GV_BOOKS | 1274 | 5096 | 6 (0)| 00:00:01 |
| 8 | SORT UNIQUE NOSORT | | 1 | 39 | 5 (20)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 39 | 4 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID | GV_ACCOUNTS | 1 | 23 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_GV_ACCOUNTS | 1 | | 0 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | BP_ACCOUNT | 1 | 16 | 3 (0)| 00:00:01 |
| 13 | NESTED LOOPS OUTER | | 1 | 96 | 2 (0)| 00:00:01 |
| 14 | NESTED LOOPS OUTER | | 1 | 83 | 2 (0)| 00:00:01 |
| 15 | NESTED LOOPS OUTER | | 1 | 70 | 2 (0)| 00:00:01 |
| 16 | NESTED LOOPS OUTER | | 1 | 57 | 2 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 44 | 2 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID| GV_ACCOUNTS | 1 | 23 | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PK_GV_ACCOUNTS | 1 | | 0 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID| CB_ACCOUNT | 81 | 1701 | 1 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | IX_CB_ACC_NO | 1 | | 0 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | ACCOUNT_EXT_KEY | 81 | 1053 | 0 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | ACCOUNT_EXT_KEY | 81 | 1053 | 0 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | ACCOUNT_EXT_KEY | 81 | 1053 | 0 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | ACCOUNT_EXT_KEY | 81 | 1053 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS ( (SELECT "ACCOUNTID" FROM "GV_BOOKS" "GV_BOOKS" WHERE
"ACCOUNTID"=:B1)MINUS (SELECT "A"."ACCOUNTID" FROM NSTCSA."BP_ACCOUNT"
"BP_ACCOUNT","GV_ACCOUNTS" "A" WHERE "A"."ACCOUNTID"=:B2 AND "A"."DBLINK"='90' AND
"A"."ACCOUNTNO"="ACNTNO")MINUS (SELECT "A"."ACCOUNTID" FROM NSTCSA."CB_ACCOUNT_EXT"
"E",NSTCSA."CB_ACCOUNT_EXT" "D",NSTCSA."CB_ACCOUNT_EXT" "C",NSTCSA."CB_ACCOUNT_EXT"
"B",NSTCSA."CB_ACCOUNT" "A","GV_ACCOUNTS" "A" WHERE "A"."ACCOUNTID"=:B3 AND "A"."DBLINK"='90'
AND "A"."ACCOUNTNO"="A"."ACCOUNT_NO" AND "B"."EXT_KEY"(+)='CALCINTR' AND
"A"."ACCOUNT_ID"="B"."ACCOUNT_ID"(+) AND "C"."EXT_KEY"(+)='DAYMODE' AND
"A"."ACCOUNT_ID"="C"."ACCOUNT_ID"(+) AND "D"."EXT_KEY"(+)='FEEMODE1' AND
"A"."ACCOUNT_ID"="D"."ACCOUNT_ID"(+) AND "E"."EXT_KEY"(+)='FEEMODE2' AND
"A"."ACCOUNT_ID"="E"."ACCOUNT_ID"(+))))
7 - access("ACCOUNTID"=:B1)
10 - filter("A"."DBLINK"='90')
11 - access("A"."ACCOUNTID"=:B1)
12 - filter("A"."ACCOUNTNO"="ACNTNO")
18 - filter("A"."DBLINK"='90')
19 - access("A"."ACCOUNTID"=:B1)
21 - access("A"."ACCOUNTNO"="A"."ACCOUNT_NO")
22 - access("A"."ACCOUNT_ID"="E"."ACCOUNT_ID"(+) AND "E"."EXT_KEY"(+)='FEEMODE2')
23 - access("A"."ACCOUNT_ID"="D"."ACCOUNT_ID"(+) AND "D"."EXT_KEY"(+)='FEEMODE1')
24 - access("A"."ACCOUNT_ID"="C"."ACCOUNT_ID"(+) AND "C"."EXT_KEY"(+)='DAYMODE')
25 - access("A"."ACCOUNT_ID"="B"."ACCOUNT_ID"(+) AND "B"."EXT_KEY"(+)='CALCINTR')分析
表信息：
GV_BOOKS ：86668行数据
子查询 ：1行数据
由以上信息可以想到，让子查询方向驱动主表GV_BOOKS
改写后代码：
执行时间 ：1s内
DELETE /*+ use_nl(tp@a,GV_BOOKS) */ GV_BOOKS
WHERE ACCOUNTID IN (select /*+ qb_name(a)*/ ACCOUNTID from
(SELECT ACCOUNTID
FROM GV_BOOKS
MINUS
SELECT A.ACCOUNTID
FROM GV_ACCOUNTS A, VW_BP_ACCOUNT_SYN B
WHERE A.DBLINK = B.DB_LINK
AND A.ACCOUNTNO = B.ACNTNO
MINUS
SELECT A.ACCOUNTID
FROM GV_ACCOUNTS A, VW_CNTACNT_GVIEW B
WHERE A.DBLINK = B.DB_LINK
AND A.ACCOUNTNO = B.NO) tp);
Plan hash value: 9035204
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 104 | 13 (31)| 00:00:01 |
| 1 | DELETE | GV_BOOKS | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 104 | 13 (31)| 00:00:01 |
| 4 | VIEW | | 1 | 13 | 13 (31)| 00:00:01 |
| 5 | MINUS | | | | | |
| 6 | MINUS | | | | | |
| 7 | SORT UNIQUE | | 1 | 13 | | |
| 8 | TABLE ACCESS FULL | GV_BOOKS | 1 | 13 | 2 (0)| 00:00:01 |
| 9 | SORT UNIQUE | | 1 | 66 | | |
|* 10 | HASH JOIN | | 1 | 66 | 6 (17)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | GV_ACCOUNTS | 1 | 49 | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | BP_ACCOUNT | 33 | 561 | 3 (0)| 00:00:01 |
| 13 | SORT UNIQUE | | 1 | 117 | | |
| 14 | NESTED LOOPS OUTER | | 1 | 117 | 2 (0)| 00:00:01 |
| 15 | NESTED LOOPS OUTER | | 1 | 105 | 2 (0)| 00:00:01 |
| 16 | NESTED LOOPS OUTER | | 1 | 93 | 2 (0)| 00:00:01 |
| 17 | NESTED LOOPS OUTER | | 1 | 81 | 2 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 69 | 2 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | GV_ACCOUNTS | 1 | 49 | 2 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID| CB_ACCOUNT | 1 | 20 | 0 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | IX_CB_ACC_NO | 1 | | 0 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | ACCOUNT_EXT_KEY | 1 | 12 | 0 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | ACCOUNT_EXT_KEY | 1 | 12 | 0 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | ACCOUNT_EXT_KEY | 1 | 12 | 0 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | ACCOUNT_EXT_KEY | 1 | 12 | 0 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | IX_GV_BOOKS | 1 | | 0 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | GV_BOOKS | 1 | 91 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("A"."ACCOUNTNO"="ACNTNO")
11 - filter("A"."DBLINK"='90')
19 - filter("A"."DBLINK"='90')
21 - access("A"."ACCOUNTNO"="A"."ACCOUNT_NO")
22 - access("A"."ACCOUNT_ID"="D"."ACCOUNT_ID"(+) AND "D"."EXT_KEY"(+)='FEEMODE1')
23 - access("A"."ACCOUNT_ID"="C"."ACCOUNT_ID"(+) AND "C"."EXT_KEY"(+)='DAYMODE')
24 - access("A"."ACCOUNT_ID"="B"."ACCOUNT_ID"(+) AND "B"."EXT_KEY"(+)='CALCINTR')
25 - access("A"."ACCOUNT_ID"="E"."ACCOUNT_ID"(+) AND "E"."EXT_KEY"(+)='FEEMODE2')
26 - access("ACCOUNTID"="ACCOUNTID")优化方法二：
根据原sql执行计划，看出sql是走的filter，也就是子查询并未展开。
所以添加hint（unnest）让优化器对子查询展开。
执行时间：1s内
--执行计划
Plan hash value: 4288598425
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 110M| 4101M| | 1513 (53)| 00:00:19 |
| 1 | DELETE | GV_BOOKS | | | | | |
|* 2 | HASH JOIN | | 110M| 4101M| 2120K| 1513 (53)| 00:00:19 |
| 3 | VIEW | VW_NSO_1 | 86600 | 1099K| | 372 (3)| 00:00:05 |
| 4 | MINUS | | | | | | |
| 5 | MINUS | | | | | | |
| 6 | SORT UNIQUE | | 86600 | 338K| 1032K| | |
| 7 | TABLE ACCESS FULL | GV_BOOKS | 86600 | 338K| | 103 (1)| 00:00:02 |
| 8 | SORT UNIQUE | | 87 | 3393 | | | |
|* 9 | HASH JOIN | | 87 | 3393 | | 7 (15)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | GV_ACCOUNTS | 87 | 2001 | | 3 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | BP_ACCOUNT | 87 | 1392 | | 3 (0)| 00:00:01 |
| 12 | SORT UNIQUE | | 81 | 7776 | | | |
|* 13 | HASH JOIN | | 81 | 7776 | | 6 (17)| 00:00:01 |
| 14 | NESTED LOOPS OUTER | | 81 | 5913 | | 3 (34)| 00:00:01 |
| 15 | NESTED LOOPS OUTER | | 81 | 4860 | | 3 (34)| 00:00:01 |
| 16 | NESTED LOOPS OUTER | | 81 | 3807 | | 3 (34)| 00:00:01 |
| 17 | NESTED LOOPS OUTER | | 81 | 2754 | | 3 (34)| 00:00:01 |
| 18 | VIEW | index$_join$_009 | 81 | 1701 | | 3 (34)| 00:00:01 |
|* 19 | HASH JOIN | | | | | | |
| 20 | INDEX FAST FULL SCAN| IX_CB_ACC_NO | 81 | 1701 | | 1 (0)| 00:00:01 |
| 21 | INDEX FAST FULL SCAN| PK_CB_ACCOUNT | 81 | 1701 | | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | ACCOUNT_EXT_KEY | 1 | 13 | | 0 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | ACCOUNT_EXT_KEY | 1 | 13 | | 0 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | ACCOUNT_EXT_KEY | 1 | 13 | | 0 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | ACCOUNT_EXT_KEY | 1 | 13 | | 0 (0)| 00:00:01 |
|* 26 | TABLE ACCESS FULL | GV_ACCOUNTS | 87 | 2001 | | 3 (0)| 00:00:01 |
| 27 | TABLE ACCESS FULL | GV_BOOKS | 86600 | 2198K| | 104 (2)| 00:00:02 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ACCOUNTID"="ACCOUNTID")
9 - access("A"."ACCOUNTNO"="ACNTNO")
10 - filter("A"."DBLINK"='90')
13 - access("A"."ACCOUNTNO"="A"."ACCOUNT_NO")
19 - access(ROWID=ROWID)
22 - access("A"."ACCOUNT_ID"="E"."ACCOUNT_ID"(+) AND "E"."EXT_KEY"(+)='FEEMODE2')
23 - access("A"."ACCOUNT_ID"="B"."ACCOUNT_ID"(+) AND "B"."EXT_KEY"(+)='CALCINTR')
24 - access("A"."ACCOUNT_ID"="C"."ACCOUNT_ID"(+) AND "C"."EXT_KEY"(+)='DAYMODE')
25 - access("A"."ACCOUNT_ID"="D"."ACCOUNT_ID"(+) AND "D"."EXT_KEY"(+)='FEEMODE1')
26 - filter("A"."DBLINK"='90')