团团圆圆吃汤圆,快快来三连
此次案例来自西安某客户的一次SQL优化,对于优化本身并不复杂,但是发现了一个比较有趣的问题,就是索引范围扫描以及回表都有使用多块读的方式。下面来看看具体案例。
SQL文本:
UPDATE A_INV_PRINT_DET P SET (P.P_POWER, P.N_POWER, P.V_POWER, P.P_KWH_PRC, P.N_KWH_PRC, P.V_KWH_PRC, P.P_KWH_AMT, P.N_KWH_AMT, P.V_KWH_AMT) = (SELECT KWH.P_POWER, KWH.N_POWER, KWH.V_POWER,
KWH.P_KWH_PRC, KWH.N_KWH_PRC, KWH.V_KWH_PRC, KWH.P_KWH_AMT, KWH.N_KWH_AMT, KWH.V_KWH_AMT FROM (SELECT B.PRC_AMT_ID, SUM(CASE WHEN B.PRC_TS_CODE= :B4 THEN B.SETTLE_APQ ELSE 0 END) P_POWER, SUM(CASE
WHEN B.PRC_TS_CODE= :B3 THEN B.SETTLE_APQ ELSE 0 END) N_POWER, SUM(CASE WHEN B.PRC_TS_CODE= :B2 THEN B.SETTLE_APQ ELSE 0 END) V_POWER, MAX(CASE WHEN B.PRC_TS_CODE= :B4 THEN B.KWH_PRC ELSE 0 END)
P_KWH_PRC, MAX(CASE WHEN B.PRC_TS_CODE= :B3 THEN B.KWH_PRC ELSE 0 END) N_KWH_PRC, MAX(CASE WHEN B.PRC_TS_CODE= :B2 THEN B.KWH_PRC ELSE 0 END) V_KWH_PRC, SUM(CASE WHEN B.PRC_TS_CODE= :B4 THEN
B.KWH_AMT ELSE 0 END) P_KWH_AMT, SUM(CASE WHEN B.PRC_TS_CODE= :B3 THEN B.KWH_AMT ELSE 0 END) N_KWH_AMT, SUM(CASE WHEN B.PRC_TS_CODE= :B2 THEN B.KWH_AMT ELSE 0 END) V_KWH_AMT FROM ARC_E_KWH_AMT B
WHERE B.ORG_NO LIKE :B1 AND EXISTS (SELECT 1 FROM A_NOTEPRC_TMP T WHERE B.PRC_AMT_ID = T.NOTE_ID) GROUP BY B.PRC_AMT_ID) KWH WHERE KWH.PRC_AMT_ID = P.PRC_AMT_ID) WHERE EXISTS (SELECT 1 FROM
A_INV_PRINT I WHERE I.INV_MAIN_ID = P.INV_MAIN_ID AND I.ORG_NO LIKE '61020%' AND EXISTS (SELECT 1 FROM A_AMT_CONS WHERE CONS_NO = I.CONS_NO)) AND EXISTS (SELECT 1 FROM ARC_E_KWH_AMT B WHERE B.ORG_NO
LIKE '61020%' AND B.PRC_AMT_ID = P.PRC_AMT_ID AND EXISTS (SELECT 1 FROM A_NOTEPRC_TMP T WHERE B.PRC_AMT_ID = T.NOTE_ID) )
执行计划:
Plan hash value: 4279392932
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 78882 (100)| | | |
| 1 | UPDATE | A_INV_PRINT_DET | | | | | | |
|* 2 | HASH JOIN SEMI | | 1 | 63 | 9915 (1)| 00:01:59 | | |
| 3 | NESTED LOOPS | | 229 | 11450 | 2253 (1)| 00:00:28 | | |
| 4 | NESTED LOOPS | | 229 | 11450 | 2253 (1)| 00:00:28 | | |
| 5 | VIEW | VW_SQ_1 | 214 | 2782 | 1395 (1)| 00:00:17 | | |
| 6 | SORT UNIQUE | | 214 | 22898 | | | | |
| 7 | NESTED LOOPS | | 214 | 22898 | 1395 (1)| 00:00:17 | | |
| 8 | NESTED LOOPS | | 1323 | 22898 | 1395 (1)| 00:00:17 | | |
| 9 | SORT UNIQUE | | 126 | 9702 | 2 (0)| 00:00:01 | | |
| 10 | INDEX FAST FULL SCAN | DX_A_AMT_CONS | 126 | 9702 | 2 (0)| 00:00:01 | | |
|* 11 | INDEX RANGE SCAN | IDX_CONS_NO3 | 21 | | 3 (0)| 00:00:01 | | |
|* 12 | TABLE ACCESS BY GLOBAL INDEX ROWID| A_INV_PRINT | 2 | 60 | 24 (0)| 00:00:01 | ROWID | ROWID |
|* 13 | INDEX RANGE SCAN | IDX_AINVPRINTDET_INVMAINID | 1 | | 3 (0)| 00:00:01 | | |
| 14 | TABLE ACCESS BY INDEX ROWID | A_INV_PRINT_DET | 1 | 37 | 4 (0)| 00:00:01 | | |
| 15 | VIEW | VW_SQ_2 | 295K| 3753K| 7662 (1)| 00:01:32 | | |
|* 16 | HASH JOIN RIGHT SEMI | | 295K| 18M| 7662 (1)| 00:01:32 | | |
| 17 | TABLE ACCESS FULL | A_NOTEPRC_TMP | 127 | 6350 | 2 (0)| 00:00:01 | | |
| 18 | PARTITION RANGE ITERATOR | | 295K| 4908K| 7659 (1)| 00:01:32 | KEY | KEY |
| 19 | PARTITION LIST ALL | | 295K| 4908K| 7659 (1)| 00:01:32 | 1 | 49 |
| 20 | TABLE ACCESS BY LOCAL INDEX ROWID | ARC_E_KWH_AMT | 295K| 4908K| 7659 (1)| 00:01:32 | KEY | KEY |gc cr multi block request(5)(12.82%) |
| |db file sequential read(6)(15.38%) |
| |gc current block 2-way(2)(5.13%) |
| |db file scattered read(9)(23.08%) |
| |CPU(2)(5.13%) |
|* 21 | INDEX RANGE SCAN | IDX_ARC_E_KWH_AMT_OY_2X | 295K| | 1123 (1)| 00:00:14 | KEY | KEY |db file scattered read(6)(15.38%) |
| |db file sequential read(5)(12.82%) |
| |gc cr multi block request(1)(2.56%) |
| |CPU(2)(5.13%) |
| |gc current block 2-way(1)(2.56%) |
| 22 | VIEW | | 1 | 130 | 7662 (1)| 00:01:32 | | |
| 23 | SORT GROUP BY | | 1 | 83 | 7662 (1)| 00:01:32 | | |
| 24 | NESTED LOOPS | | 1 | 83 | 7662 (1)| 00:01:32 | | |
| 25 | NESTED LOOPS | | 295K| 83 | 7662 (1)| 00:01:32 | | |
| 26 | SORT UNIQUE | |