Elapsed: 00:00:00.00 10:35:26 HH_CDW@hhp3 SQL> SELECT * FROM TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2811015489 ----------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 26M| 7200M| | 4951K (4)| 12:35:42 | | | | | 1 | MERGE | T_CUST_EMAIL | | | | | | | | | | 2 | VIEW | | | | | | | | | | | 3 | SEQUENCE | CUST_EMAIL_ID | | | | | | | | | |* 4 | HASH JOIN RIGHT OUTER | | 26M| 5124M| 147M| 4951K (4)| 12:35:42 | | | | |* 5 | TABLE ACCESS FULL | T_CUST_EMAIL | 18M| 2134M| | 93811 (7)| 00:14:20 | | | | | 6 | VIEW | | 26M| 2101M| | 4851K (4)| 12:20:24 | | | | | 7 | SORT UNIQUE | | 26M| 2793M| 386G| 4851K (4)| 12:20:24 | | | | |* 8 | FILTER | | | | | | | | | | | 9 | PX COORDINATOR | | | | | | | | | | | 10 | PX SEND QC (RANDOM) | :TQ10002 | 58M| 6079M| | 1158K (5)| 02:56:52 | Q1,02 | P->S | QC (RAND) | | 11 | MERGE JOIN | | 58M| 6079M| | 1158K (5)| 02:56:52 | Q1,02 | PCWP | | | 12 | BUFFER SORT | | | | | | | Q1,02 | PCWC | | | 13 | PX RECEIVE | | 58M| 5298M| | 1146K (5)| 02:55:01 | Q1,02 | PCWP | | | 14 | PX SEND HASH | :TQ10000 | 58M| 5298M| | 1146K (5)| 02:55:01 | | S->P | HASH | |* 15 | VIEW | | 58M| 5298M| | 1146K (5)| 02:55:01 | | | | | 16 | WINDOW SORT | | 58M| 5131M| 12G| 1146K (5)| 02:55:01 | | | | | 17 | VIEW | | 58M| 5131M| | 466K (8)| 01:11:10 | | | | | 18 | UNION-ALL | | | | | | | | | | | 19 | MAT_VIEW ACCESS FULL | T_MEM_CHANNEL | 44M| 1585M| | 54938 (11)| 00:08:24 | | | | |* 20 | HASH JOIN | | 13M| 1202M| 724M| 411K (8)| 01:02:47 | | | | |* 21 | VIEW | | 13M| 569M| | 176K (9)| 00:26:57 | | | | | 22 | WINDOW SORT | | 13M| 375M| 1142M| 176K (9)| 00:26:57 | | | | |* 23 | MAT_VIEW ACCESS FULL| A_MEM_CHANNEL | 13M| 375M| | 115K (10)| 00:17:40 | | | | | 24 | MAT_VIEW ACCESS FULL | A_MEM_CHANNEL | 81M| 3802M| | 115K (10)| 00:17:38 | | | | |* 25 | SORT JOIN | | 28M| 385M| 1324M| 12114 (4)| 00:01:51 | Q1,02 | PCWP | | | 26 | PX RECEIVE | | 28M| 385M| | 6444 (1)| 00:01:00 | Q1,02 | PCWP | | | 27 | PX SEND HASH | :TQ10001 | 28M| 385M| | 6444 (1)| 00:01:00 | Q1,01 | P->P | HASH | | 28 | PX BLOCK ITERATOR | | 28M| 385M| | 6444 (1)| 00:01:00 | Q1,01 | PCWC | | | 29 | TABLE ACCESS FULL | T_HHONORS_MEM | 28M| 385M| | 6444 (1)| 00:01:00 | Q1,01 | PCWP | | | 30 | SORT AGGREGATE | | 1 | 70 | | | | | | | |* 31 | VIEW | | 58M| 3904M| | 1119K (5)| 02:50:53 | | | | | 32 | WINDOW SORT | | 58M| 4963M| 12G| 1119K (5)| 02:50:53 | | | | | 33 | VIEW | | 58M| 4963M| | 462K (8)| 01:10:40 | | | | | 34 | UNION-ALL | | | | | | | | | | | 35 | MAT_VIEW ACCESS FULL | T_MEM_CHANNEL | 44M| 1499M| | 54938 (11)| 00:08:24 | | | | |* 36 | HASH JOIN | | 13M| 1177M| 724M| 408K (8)| 01:02:17 | | | | |* 37 | VIEW | | 13M| 569M| | 176K (9)| 00:26:57 | | | | | 38 | WINDOW SORT | | 13M| 375M| 1142M| 176K (9)| 00:26:57 | | | | |* 39 | MAT_VIEW ACCESS FULL | A_MEM_CHANNEL | 13M| 375M| | 115K (10)| 00:17:40 | | | | | 40 | MAT_VIEW ACCESS FULL | A_MEM_CHANNEL | 81M| 3647M| | 115K (10)| 00:17:38 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("C"."EMAIL_TYPE"(+)="S"."CODE_CHANNEL_TYPE" AND "C"."EMAIL_ADDR"(+)="S"."EMAIL_ADDR" AND "C"."CUSTOMER_ID"(+)="S"."CUSTOMER_ID") 5 - filter("C"."EMAIL_TYPE"(+) IS NOT NULL) 8 - filter("AUDIT_TIME"= (SELECT MAX("AUDIT_TIME") FROM (SELECT "DGROUP" "DGROUP","ID_MEM_CHANNEL" "ID_MEM_CHANNEL","CODE_CHANNEL" "CODE_CHANNEL","CODE_CHANNEL_TYPE" "CODE_CHANNEL_TYPE","CHANNEL_STATUS" "CHANNEL_STATUS","IND_PREFER" "IND_PREFER","STATUS" "STATUS","ID_MEMBER" "ID_MEMBER","BEG_DATE" "BEG_DATE","END_DATE" "END_DATE","CHANNEL_ADDR" "CHANNEL_ADDR","TITLE" "TITLE","COMPANY_NAME" "COMPANY_NAME","ADDR1" "ADDR1","ADDR2" "ADDR2","ADDR3" "ADDR3","CITY" "CITY","CODE_STATE" "CODE_STATE","ZIP" "ZIP","CODE_POSTAL" "CODE_POSTAL","CODE_COUNTRY" "CODE_COUNTRY","USERID" "USERID","AUDIT_TIME" "AUDIT_TIME","PURGE_DATE" "PURGE_DATE",FIRST_VALUE("DGROUP") OVER ( PARTITION BY "A"."ID_MEMBER" ORDER BY INTERNAL_FUNCTION("PURGE_DATE") DESC RANGE BETWEE N UNBOUNDED PRECEDING AND CURRENT ROW ) "FIRST" FROM ( (SELECT 1 "DGROUP","ID_MEM_CHANNEL" "ID_MEM_CHANNEL","CODE_CHANNEL" "CODE_CHANNEL","CODE_CHANNEL_TYPE" "CODE_CHANNEL_TYPE","CHANNEL_STATUS" "CHANNEL_STATUS","IND_PREFER" "IND_PREFER","STATUS" "STATUS","ID_MEMBER" "ID_MEMBER","BEG_DATE" "BEG_DATE","END_DATE" "END_DATE","CHANNEL_ADDR" "CHANNEL_ADDR","TITLE" "TITLE","COMPANY_NAME" "COMPANY_NAME","ADDR1" "ADDR1","ADDR2" "ADDR2","ADDR3" "ADDR3","CITY" "CITY","CODE_STATE" "CODE_STATE","ZIP" "ZIP","CODE_POSTAL" "CODE_POSTAL","CODE_COUNTRY" "CODE_COUNTRY","USERID" "USERID","AUDIT_TIME" "AUDIT_TIME",NULL "PURGE_DATE" FROM "HH"."T_MEM_CHANNEL" "T_MEM_CHANNEL") UNION ALL (SELECT 2 "DGROUP","A"."ID_MEM_CHANNEL" "ID_MEM_CHANNEL","A"."CODE_CHANNEL" "CODE_CHANNEL","A"."CODE_CHANNEL_TYPE" "CODE_CHANNEL_TYPE","A"."CHANNEL_STATUS" "CHANNEL_STATUS","A"."IND_PREFER" "IND_PREFER","A"."STATUS" "STATUS","A"."ID_MEMBER" "ID_MEMBER","A"."BEG_DATE" "BEG_DATE","A"."END_DATE" "END_DATE","A"."CHANNEL_ADD R" "CHANNEL_ADDR","A"."TITLE" "TITLE","A"."COMPANY_NAME" "COMPANY_NAME","A"."ADDR1" "ADDR1","A"."ADDR2" "ADDR2","A"."ADDR3" "ADDR3","A"."CITY" "CITY","A"."CODE_STATE" "CODE_STATE","A"."ZIP" "ZIP","A"."CODE_POSTAL" "CODE_POSTAL","A"."CODE_COUNTRY" "CODE_COUNTRY","A"."USERID" "USERID","A"."AUDIT_TIME" "AUDIT_TIME","A"."PURGE_DATE" "PURGE_DATE" FROM (SELECT "ID_MEMBER" "ID_MEMBER","PURGE_DATE" "PURGE_DATE","ID_MEM_CHANNEL" "ID_MEM_CHANNEL",FIRST_VALUE("PURGE_DATE") OVER ( PARTITION BY "ID_MEMBER" ORDER BY INTERNAL_FUNCTION("PURGE_DATE") DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) "MAX_PURGE_DATE" FROM "HHD"."A_MEM_CHANNEL" "A" WHERE "USERID"='PURGE') "from$_subquery$_042","HHD"."A_MEM_CHANNEL" "A" WHERE "A"."ID_MEM_CHANNEL"="ID_MEM_CHANNEL" AND "A"."PURGE_DATE"="PURGE_DATE" AND "A"."ID_MEMBER"="ID_MEMBER" AND "PURGE_DATE"="MAX_PURGE_DATE")) "A") "from$_subquery$_037" WHERE "CHANNEL_ADDR"=:B1 AND "AUDIT_TIME">=TO_DATE(' 2011-04-23 00:00:00 ', 'syyyy-mm-dd hh24:mi:ss') AND "CODE_CHANNEL"='EMAIL' AND "STATUS"='A' AND "CODE_CHANNEL_TYPE"=:B2 AND "DGROUP"="FIRST")) 15 - filter("CHANNEL_ADDR" IS NOT NULL AND "CODE_CHANNEL"='EMAIL' AND "STATUS"='A' AND "AUDIT_TIME">=TO_DATE(' 2011-04-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "DGROUP"="FIRST") 20 - access("A"."ID_MEMBER"="ID_MEMBER" AND "A"."PURGE_DATE"="PURGE_DATE" AND "A"."ID_MEM_CHANNEL"="ID_MEM_CHANNEL") 21 - filter("PURGE_DATE"="MAX_PURGE_DATE") 23 - filter("USERID"='PURGE') 25 - access("ID_MEMBER"="CDW"."ID_MEMBER") filter("ID_MEMBER"="CDW"."ID_MEMBER") 31 - filter("CHANNEL_ADDR"=:B1 AND "AUDIT_TIME">=TO_DATE(' 2011-04-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CODE_CHANNEL"='EMAIL' AND "STATUS"='A' AND "CODE_CHANNEL_TYPE"=:B2 AND "DGROUP"="FIRST") 36 - access("A"."ID_MEMBER"="ID_MEMBER" AND "A"."PURGE_DATE"="PURGE_DATE" AND "A"."ID_MEM_CHANNEL"="ID_MEM_CHANNEL") 37 - filter("PURGE_DATE"="MAX_PURGE_DATE") 39 - filter("USERID"='PURGE') 89 rows selected. Elapsed: 00:00:00.06