下面的两个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)  WON (W.ID=R.ROWID AND R.PROMISED =‘Y‘)WHEN MATCHED THENUPDATE 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‘))