用Merge来改写相关更新的例子

下面的两个SQL是等价的，但是一个执行N小时都执行不完，一个花了一分钟。

执行计划显示
第一个语句是由外面的即将被更新的表驱动内层，相对于是一个NEST LOOP，cost非常大。
第二个语句是内层单独执行完后，与外面的筛选结果做一个HASH JOIN, cost降低了很多.


UPDATE GPCOMP1.GPRECL R SET (PROMISED_BY,PROMISED_DATE)=(SELECT W.USER_ID,W.CREATED_ON FROM
                                                                                           (SELECT T.*,ROWNUM FROM (
                                                                                            SELECT A.USER_ID,A.CREATED_ON,RL.TRAN_ID,ROW_NUMBER() OVER(PARTITION BY A.CUSTNO ORDER BY A.ACTIVITY_ID DESC) RN
                                                                                            FROM GPCOMP1.GPRECL RL,GPCOMP1.GPACTV A 
                                                                                             WHERE A.CUSTNO = RL.CUSTNO AND A.ACTVDATE < RL.RCLDATE AND RL.PROMISED ='Y'
                                                                                             ) T WHERE T.RN =1) W WHERE W.TRAN_ID=R.TRAN_ID
) WHERE R.PROMISED ='Y';

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2415251688
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                  |                              | 19278 |   433K|       |  1712M  (1)|999:59:59 |
|   1 |  UPDATE                           | GPRECL                       |       |       |       |         |     |
|   2 |   TABLE ACCESS BY INDEX ROWID     | GPRECL                       | 19278 |   433K|       |  9334   (1)| 00:01:53 |
|*  3 |    INDEX RANGE SCAN               | GPRCL_PROMISED_CUST_TRAN     | 19278 |       |       |   103   (0)| 00:00:02 |
|*  4 |   VIEW                            |                              |   338K|    11M|       | 44419   (1)| 00:08:54 |
|   5 |    COUNT                          |                              |       |       |       |         |     |
|*  6 |     VIEW                          |                              |   338K|    15M|       | 44419   (1)| 00:08:54 |
|*  7 |      WINDOW SORT PUSHED RANK      |                              |   338K|    22M|    28M| 44419   (1)| 00:08:54 |
|   8 |       NESTED LOOPS                |                              |   338K|    22M|       | 38703   (1)| 00:07:45 |
|*  9 |        INDEX RANGE SCAN           | GPRCL_PROMISED_CUST_TRAN     | 19278 |   508K|       |   103   (0)| 00:00:02 |
|* 10 |        TABLE ACCESS BY INDEX ROWID| GPACTV                       |    18 |   792 |       |     3   (0)| 00:00:01 |
|* 11 |         INDEX RANGE SCAN          | GPACTV_CUSTNO_TRUNC_ACTVDATE |     1 |       |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("R"."PROMISED"='Y')
   4 - filter("W"."TRAN_ID"=:B1)
   6 - filter("T"."RN"=1)
   7 - filter(ROW_NUMBER() OVER ( PARTITION BY "A"."CUSTNO" ORDER BY INTERNAL_FUNCTION("A"."ACTIVITY_ID") DESC)<=1)
   9 - access("RL"."PROMISED"='Y')
  10 - filter("A"."ACTVDATE"<"RL"."RCLDATE")
  11 - access("A"."CUSTNO"="RL"."CUSTNO" AND TRUNC(INTERNAL_FUNCTION("ACTVDATE"),'fmddd')<=TRUNC(INTERNAL_FUNCTION("RL"."RCLDATE"),'fmddd'))


MERGE INTO GPCOMP1.GPRECL R USING (SELECT T.USER_ID,T.CREATED_ON,T.ID FROM (
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　SELECT A.USER_ID,A.CREATED_ON,RL.ROWID AS ID,ROW_NUMBER() OVER(PARTITION BY A.CUSTNO ORDER BY A.ACTIVITY_ID DESC) RN
                                     FROM GPCOMP1.GPRECL RL,GPCOMP1.GPACTV A 
                                   WHERE A.CUSTNO = RL.CUSTNO AND A.ACTVDATE < RL.RCLDATE AND RL.PROMISED ='Y') T 
　　　　　　　　　　　　　　　　　　　　　　　WHERE T.RN =1)  W
ON (W.ID=R.ROWID AND R.PROMISED ='Y')
WHEN MATCHED THEN
UPDATE SET R.PROMISED_BY = W.USER_ID, R.PROMISED_DATE=W.CREATED_ON;

 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1259882673
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                   |                              |  3194 |   112K|       | 56039   (1)| 00:11:13 |
|   1 |  MERGE                            | GPRECL                       |       |       |       |         |     |
|   2 |   VIEW                            |                              |       |       |       |         |     |
|*  3 |    HASH JOIN                      |                              |  3194 |  1394K|  7760K| 56039   (1)| 00:11:13 |
|   4 |     TABLE ACCESS BY INDEX ROWID   | GPRECL                       | 19278 |  7530K|       |  9334   (1)| 00:01:53 |
|*  5 |      INDEX RANGE SCAN             | GPRCL_PROMISED_CUST_TRAN     | 19278 |       |       |   103   (0)| 00:00:02 |
|*  6 |     VIEW                          |                              |   338K|    15M|       | 45379   (1)| 00:09:05 |
|*  7 |      WINDOW SORT PUSHED RANK      |                              |   338K|    27M|    33M| 45379   (1)| 00:09:05 |
|   8 |       NESTED LOOPS                |                              |   338K|    27M|       | 38703   (1)| 00:07:45 |
|*  9 |        INDEX RANGE SCAN           | GPRCL_PROMISED_CUST_TRAN     | 19278 |   602K|       |   103   (0)| 00:00:02 |
|* 10 |        TABLE ACCESS BY INDEX ROWID| GPACTV                       |    18 |   954 |       |     3   (0)| 00:00:01 |
|* 11 |         INDEX RANGE SCAN          | GPACTV_CUSTNO_TRUNC_ACTVDATE |     1 |       |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T"."ID"="R".ROWID)
   5 - access("R"."PROMISED"='Y')
   6 - filter("T"."RN"=1)
   7 - filter(ROW_NUMBER() OVER ( PARTITION BY "A"."CUSTNO" ORDER BY INTERNAL_FUNCTION("A"."ACTIVITY_ID") DESC)<=1)
   9 - access("RL"."PROMISED"='Y')
  10 - filter(TRUNC(INTERNAL_FUNCTION("ACTVDATE"))<=TRUNC(INTERNAL_FUNCTION("RL"."RCLDATE")) AND "A"."ACTVDATE"<"RL"."RCLDATE")
  11 - access("A"."CUSTNO"="RL"."CUSTNO" AND TRUNC(INTERNAL_FUNCTION("ACTVDATE"),'fmddd')<=TRUNC(INTERNAL_FUNCTION("RL"."RCLDATE"),'fmddd'))