Delete 语句带有子查询的sql优化
来源：互联网 发布：网络分流器和交换机 编辑：程序博客网 时间：2024/05/21 08:02
背景：
接到开发通知，应用页面打不开，让我协助。。。（开发跟我说，表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')阅读全文
1 0
- Delete 语句带有子查询的sql优化
- 包含IN子查询的SQL语句的优化
- MySQL 中 delete ,update语句的子查询限制
- MySQL 中 delete 语句的子查询限制
- MySQL 中 delete 语句的子查询限制
- MySQL 中 delete 语句的子查询限制
- MySQL 中 delete 语句的子查询限制
- sql子查询语句
- sql子查询语句
- 优化系列 | DELETE子查询改写优化
- 优化系列 | DELETE子查询改写优化
- SQL 语句的子查询例子
- sql 子查询优化
- 要优化的sql语句 递归查询不包含本部门及其子部门
- UPDATE、DELETE 语句中的子查询
- SQL 查询语句优化
- sql查询语句优化
- SQL语句查询优化
- Gson特殊字段解析
- JavaBean 在Servlet 和JSP页面传递
- Nginx
- Axure RP 8 注册码（序列号亲测可用）
- Hadoop之Pig从安装到使用
- Delete 语句带有子查询的sql优化
- 剑指offer第25题（复杂链表的复制）
- VTK学习-vtkLight
- 在idea中使用maven的一些总结
- JNI教程（所用的环境为LINUX）
- [Java面试五]Spring总结以及在面试中的一些问题
- ubuntu下CMake编译生成动态库和静态库，以OpenTLD为例。
- Sublime text3 设置快捷键打开指定浏览器预览
- public 等区分
