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 |
常用的sql查询语句有哪些|  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')

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。