Oracle案例：index range scan真的不会多块读吗？

团团圆圆吃汤圆，快快来三连

此次案例来自西安某客户的一次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                         |                            |