SQL QUERY -------------------- SELECT BI.ACCOUNT_NUMBER, BI.MONTH, BI.YEAR, BI.ACTION_DATE, A.NEW_REG_NUMBER, A.TYPE, A.PRD_CODE, TL.LANG, BC.BAL_DL, BC.STATEMENT_DT , A.START_DT, A.MONTHLY_ACCRUAL, BC.MONTHLY_BALANCE FROM ACCOUNT A, BC_ACCOUNT BC, PRODUCT P, T_LANG TL, PRODUCT_CAT PCAT, ACCOUNT_BI BI WHERE A.ACCOUNT_NUMBER = BC.ACCOUNT_NUMBER AND A.PRD_CODE = BI.CODE AND BI.ID = TL.ID AND A.PRD_CODE = PCAT.CODE AND A.STATUS IS NULL AND ((BC.BAL_FLAG = 'Y' AND BI.BAL_REG_NP = BC.PRIMARY_ACCOUNT_NUMBER AND A.REL_ID = (SELECT REL_ID FROM ACCOUNT WHERE NEW_REG_NUMBER = BI.ACCOUNT_NUMBER AND STATUS IS NULL )) OR (BC.CODE = 'E' AND EXISTS (SELECT 1 FROM ACCOUNT AC, BC_ACCOUNT BC WHERE AC.ACCOUNT_NUMBER = BC.ACCOUNT_NUMBER AND AC.NEW_REG_NUMBER = BI.ACCOUNT_NUMBER AND AC.CODE = 'E' ) AND A.REL_ID = (SELECT REL_ID FROM ACCOUNT WHERE NEW_REG_NUMBER = BI.ACCOUNT_NUMBER AND STATUS IS NULL )) OR (BC.IS_CRD_FLG = 'Y' AND BC.DB_AC_NUMBER = BI.ACCOUNT_NUMBER) OR (A.NEW_REG_NUMBER = BI.ACCOUNT_NUMBER AND PCAT.TYPE = 'P')) AND BI.ACTION_DATE = SYSDATE; Plan hash value: 88070583 -------------------------------------------------------------------------------- ----------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | | 0 | SELECT STATEMENT | | | | 230K(100)| | | | | | 1 | CONCATENATION | | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ60000 | | | | | Q5,02 | P->S | QC (RAND) | | 4 | NESTED LOOPS | | | | | | Q5,02 | PCWP | | | 5 | NESTED LOOPS | | 158 | 35866 | 20741 (1)| 00:04:09 | Q5,02 | PCWP | | | 7 | PX RECEIVE | | 158 | 20856 | 20558 (1)| 00:04:07 | Q5,02 | PCWP | | | 8 | PX SEND BROADCAST | :TQ60001 | 158 | 20856 | 20558 (1)| 00:04:07 | Q5,01 | P->P | BROADCAST | | 6 | HASH JOIN | | 158 | 30020 | 20565 (1)| 00:04:07 | Q5,02 | PCWP | | | 9 | HASH JOIN | | 158 | 20856 | 20558 (1)| 00:04:07 | Q5,01 | PCWP | | | 10 | PX BLOCK ITERATOR | | 305 | 9760 | 4 (0)| 00:00:01 | Q5,01 | PCWC | | | 11 | TABLE ACCESS FULL | PRODUCT_CAT | 305 | 9760 | 4 (0)| 00:00:01 | Q5,01 | PCWP | | | 12 | BUFFER SORT | | | | | | Q5,01 | PCWC | | | 13 | PX RECEIVE | | 158 | 15800 | 20553 (1)| 00:04:07 | Q5,01 | PCWP | | | 14 | PX SEND BROADCAST | :TQ60000 | 158 | 15800 | 20553 (1)| 00:04:07 | | S->P | BROADCAST | | 15 | HASH JOIN | | 158 | 15800 | 20553 (1)| 00:04:07 | | | | | 16 | TABLE ACCESS BY INDEX ROWID | PRODUCT | 3 | 24 | 2 (0)| 00:00:01 | | | | | 17 | INDEX RANGE SCAN | IDX_PROD_ID | 37 | | 1 (0)| 00:00:01 | | | | | 18 | NESTED LOOPS | | | | | | | | | | 19 | NESTED LOOPS | | 8795 | 790K| 20551 (1)| 00:04:07 | | | | | 20 | TABLE ACCESS BY INDEX ROWID | ACCOUNT_BI | 8341 | 219K| 2010 (1)| 00:00:25 | | | | | 21 | INDEX RANGE SCAN | IDX_ACCT_R_ACTION_DT | 8341 | | 29 (0)| 00:00:01 | | | | | 22 | PX COORDINATOR | | | | | | | | | | 23 | PX SEND QC (RANDOM) | :TQ20000 | 1 | 8 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 24 | PX BLOCK ITERATOR | | 1 | 8 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | | 25 | INDEX RANGE SCAN | IDX_ACCT_01 | 1 | | 2 (0)| 00:00:01 | | | | | 26 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 65 | 4 (0)| 00:00:01 | | | | | 27 | PX BLOCK ITERATOR | | 2836 | 160K| 7 (0)| 00:00:01 | Q5,02 | PCWC | | | 28 | TABLE ACCESS FULL | T_LANG | 2836 | 160K| 7 (0)| 00:00:01 | Q5,02 | PCWP | | | 29 | INDEX UNIQUE SCAN | PK_BC_ACCOUNT | 1 | | 1 (0)| 00:00:01 | Q5,02 | PCWP | | | 30 | TABLE ACCESS BY INDEX ROWID | BC_ACCOUNT | 1 | 37 | 2 (0)| 00:00:01 | Q5,02 | PCWP | | | 31 | PX COORDINATOR | | | | | | | | | | 32 | PX SEND QC (RANDOM) | :TQ40005 | 38 | 8626 | 413 (1)| 00:00:05 | Q4,05 | P->S | QC (RAND) | | 33 | HASH JOIN | | 38 | 8626 | 413 (1)| 00:00:05 | Q4,05 | PCWP | | | 34 | HASH JOIN | | 36 | 7200 | 179 (2)| 00:00:03 | Q4,05 | PCWP | | | 35 | PX RECEIVE | | 36 | 5112 | 172 (1)| 00:00:03 | Q4,05 | PCWP | | | 36 | PX SEND BROADCAST | :TQ40004 | 36 | 5112 | 172 (1)| 00:00:03 | Q4,04 | P->P | BROADCAST | | 37 | HASH JOIN | | 36 | 5112 | 172 (1)| 00:00:03 | Q4,04 | PCWP | | | 38 | PX RECEIVE | | 36 | 3960 | 167 (1)| 00:00:03 | Q4,04 | PCWP | | | 39 | PX SEND BROADCAST | :TQ40003 | 36 | 3960 | 167 (1)| 00:00:03 | Q4,03 | P->P | BROADCAST | | 40 | HASH JOIN BUFFERED | | 36 | 3960 | 167 (1)| 00:00:03 | Q4,03 | PCWP | | | 41 | BUFFER SORT | | | | | | Q4,03 | PCWC | | | 42 | PX RECEIVE | | | | | | Q4,03 | PCWP | | | 43 | PX SEND HASH | :TQ40000 | | | | | | S->P | HASH | | 44 | NESTED LOOPS | | | | | | | | | | 45 | NESTED LOOPS | | 53 | 5406 | 164 (0)| 00:00:02 | | | | | 46 | TABLE ACCESS BY INDEX ROWID| BC_ACCOUNT | 53 | 1961 | 58 (0)| 00:00:01 | | | | | 47 | INDEX SKIP SCAN | IDX_ACCT_05 | 53 | | 5 (0)| 00:00:01 | | | | | 48 | INDEX UNIQUE SCAN | ACCOUNT_PK | 1 | | 1 (0)| 00:00:01 | | | | | 49 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 65 | 2 (0)| 00:00:01 | | | | | 50 | PX RECEIVE | | 112 | 896 | 2 (0)| 00:00:01 | Q4,03 | PCWP | | | 51 | PX SEND HASH | :TQ40002 | 112 | 896 | 2 (0)| 00:00:01 | Q4,02 | P->P | HASH | | 52 | PX BLOCK ITERATOR | | 112 | 896 | 2 (0)| 00:00:01 | Q4,02 | PCWC | | | 53 | TABLE ACCESS FULL | PRODUCT | 112 | 896 | 2 (0)| 00:00:01 | Q4,02 | PCWP | | | 54 | PX BLOCK ITERATOR | | 305 | 9760 | 4 (0)| 00:00:01 | Q4,04 | PCWC | | | 55 | TABLE ACCESS FULL | PRODUCT_CAT | 305 | 9760 | 4 (0)| 00:00:01 | Q4,04 | PCWP | | | 56 | PX BLOCK ITERATOR | | 2836 | 160K| 7 (0)| 00:00:01 | Q4,05 | PCWC | | | 57 | TABLE ACCESS FULL | T_LANG | 2836 | 160K| 7 (0)| 00:00:01 | Q4,05 | PCWP | | | 58 | BUFFER SORT | | | | | | Q4,05 | PCWC | | | 59 | PX RECEIVE | | 8341 | 219K| 233 (0)| 00:00:03 | Q4,05 | PCWP | | | 60 | PX SEND BROADCAST | :TQ40001 | 8341 | 219K| 233 (0)| 00:00:03 | | S->P | BROADCAST | | 61 | TABLE ACCESS BY INDEX ROWID | ACCOUNT_BI | 8341 | 219K| 233 (0)| 00:00:03 | | | | | 62 | INDEX RANGE SCAN | IDX_ACCT_R_ACTION_DT | 8341 | | 29 (0)| 00:00:01 | | | | | 63 | PX COORDINATOR | | | | | | | | | | 64 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 8 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 65 | PX BLOCK ITERATOR | | 1 | 8 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | | 66 | FILTER | | | | | | | | | | 67 | PX COORDINATOR | | | | | | | | | | 68 | PX SEND QC (RANDOM) | :TQ20005 | 13M| 2825M| 182K (53)| 00:36:36 | Q2,05 | P->S | QC (RAND) | | 69 | HASH JOIN | | 13M| 2825M| 182K (53)| 00:36:36 | Q2,05 | PCWP | | | 70 | PX RECEIVE | | 112 | 896 | 2 (0)| 00:00:01 | Q2,05 | PCWP | | | 71 | PX SEND BROADCAST | :TQ20003 | 112 | 896 | 2 (0)| 00:00:01 | Q2,03 | P->P | BROADCAST | | 72 | PX BLOCK ITERATOR | | 112 | 896 | 2 (0)| 00:00:01 | Q2,03 | PCWC | | | 73 | TABLE ACCESS FULL | PRODUCT | 112 | 896 | 2 (0)| 00:00:01 | Q2,03 | PCWP | | | 74 | HASH JOIN | | 19M| 4064M| 182K (53)| 00:36:35 | Q2,05 | PCWP | | | 75 | BUFFER SORT | | | | | | Q2,05 | PCWC | | | 76 | PX RECEIVE | | 2333 | 86321 | 2341 (1)| 00:00:29 | Q2,05 | PCWP | | | 77 | PX SEND BROADCAST | :TQ20000 | 2333 | 86321 | 2341 (1)| 00:00:29 | | S->P | BROADCAST | | 78 | TABLE ACCESS BY INDEX ROWID | BC_ACCOUNT | 2333 | 86321 | 2341 (1)| 00:00:29 | | | | | 79 | INDEX RANGE SCAN | IDX_ACCT_02 | 2333 | | 11 (0)| 00:00:01 | | | | | 80 | HASH JOIN | | 27G| 4652G| 135K (38)| 00:27:01 | Q2,05 | PCWP | | | 81 | HASH JOIN | | 2544K| 283M| 4457 (1)| 00:00:54 | Q2,05 | PCWP | | | 82 | PX RECEIVE | | 2836 | 160K| 7 (0)| 00:00:01 | Q2,05 | PCWP | | | 83 | PX SEND BROADCAST | :TQ20004 | 2836 | 160K| 7 (0)| 00:00:01 | Q2,04 | P->P | BROADCAST | | 84 | PX BLOCK ITERATOR | | 2836 | 160K| 7 (0)| 00:00:01 | Q2,04 | PCWC | | | 85 | TABLE ACCESS FULL | T_LANG | 2836 | 160K| 7 (0)| 00:00:01 | Q2,04 | PCWP | | | 86 | MERGE JOIN CARTESIAN | | 2544K| 143M| 4446 (1)| 00:00:54 | Q2,05 | PCWP | | | 87 | BUFFER SORT | | | | | | Q2,05 | PCWC | | | 88 | PX RECEIVE | | 8341 | 219K| 233 (0)| 00:00:03 | Q2,05 | PCWP | | | 89 | PX SEND BROADCAST | :TQ20001 | 8341 | 219K| 233 (0)| 00:00:03 | | S->P | BROADCAST | | 90 | TABLE ACCESS BY INDEX ROWID | ACCOUNT_BI | 8341 | 219K| 233 (0)| 00:00:03 | | | | | 91 | INDEX RANGE SCAN | IDX_ACCT_R_ACTION_DT | 8341 | | 29 (0)| 00:00:01 | | | | | 92 | PX COORDINATOR | | | | | | | | | | 93 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 8 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 94 | PX BLOCK ITERATOR | | 1 | 8 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | | 95 | BUFFER SORT | | 305 | 9760 | 4212 (1)| 00:00:51 | Q2,05 | PCWP | | | 96 | PX BLOCK ITERATOR | | 305 | 9760 | 4 (0)| 00:00:01 | Q2,05 | PCWC | | | 97 | TABLE ACCESS FULL | PRODUCT_CAT | 305 | 9760 | 4 (0)| 00:00:01 | Q2,05 | PCWP | | | 98 | BUFFER SORT | | | | | | Q2,05 | PCWC | | | 99 | PX RECEIVE | | 3291K| 204M| 80060 (1)| 00:16:01 | Q2,05 | PCWP | | | 100 | PX SEND BROADCAST | :TQ20002 | 3291K| 204M| 80060 (1)| 00:16:01 | | S->P | BROADCAST | | 101 | TABLE ACCESS FULL | ACCOUNT | 3291K| 204M| 80060 (1)| 00:16:01 | | | | | 102 | NESTED LOOPS | | 1 | 45 | 5 (0)| 00:00:01 | | | | | 103 | INDEX RANGE SCAN | IDX_ACCT_03 | 1 | 28 | 3 (0)| 00:00:01 | | | | | 104 | INDEX RANGE SCAN | IDX_ACCT_02 | 1 | 17 | 2 (0)| 00:00:01 | | | | | 105 | INDEX RANGE SCAN | IDX_ACCT_01 | 1 | 30 | 3 (0)| 00:00:01 | | | | | 106 | FILTER | | | | | | | | | | 107 | PX COORDINATOR | | | | | | | | | | 108 | PX SEND QC (RANDOM) | :TQ30004 | 14102 | 3126K| 26116 (1)| 00:05:14 | Q3,04 | P->S | QC (RAND) | | 109 | HASH JOIN | | 14102 | 3126K| 26116 (1)| 00:05:14 | Q3,04 | PCWP | | | 110 | PX RECEIVE | | 2836 | 160K| 7 (0)| 00:00:01 | Q3,04 | PCWP | | | 111 | PX SEND BROADCAST | :TQ30001 | 2836 | 160K| 7 (0)| 00:00:01 | Q3,01 | P->P | BROADCAST | | 112 | PX BLOCK ITERATOR | | 2836 | 160K| 7 (0)| 00:00:01 | Q3,01 | PCWC | | | 113 | TABLE ACCESS FULL | T_LANG | 2836 | 160K| 7 (0)| 00:00:01 | Q3,01 | PCWP | | | 114 | HASH JOIN | | 14102 | 2327K| 26109 (1)| 00:05:14 | Q3,04 | PCWP | | | 115 | PX RECEIVE | | 305 | 9760 | 4 (0)| 00:00:01 | Q3,04 | PCWP | | | 116 | PX SEND BROADCAST | :TQ30002 | 305 | 9760 | 4 (0)| 00:00:01 | Q3,02 | P->P | BROADCAST | | 117 | PX BLOCK ITERATOR | | 305 | 9760 | 4 (0)| 00:00:01 | Q3,02 | PCWC | | | 118 | TABLE ACCESS FULL | PRODUCT_CAT | 305 | 9760 | 4 (0)| 00:00:01 | Q3,02 | PCWP | | | 119 | HASH JOIN | | 14102 | 1886K| 26104 (1)| 00:05:14 | Q3,04 | PCWP | | | 120 | PX RECEIVE | | 112 | 896 | 2 (0)| 00:00:01 | Q3,04 | PCWP | | | 121 | PX SEND HASH | :TQ30003 | 112 | 896 | 2 (0)| 00:00:01 | Q3,03 | P->P | HASH | | 122 | PX BLOCK ITERATOR | | 112 | 896 | 2 (0)| 00:00:01 | Q3,03 | PCWC | | | 123 | TABLE ACCESS FULL | PRODUCT | 112 | 896 | 2 (0)| 00:00:01 | Q3,03 | PCWP | | | 124 | BUFFER SORT | | | | | | Q3,04 | PCWC | | | 125 | PX RECEIVE | | | | | | Q3,04 | PCWP | | | 126 | PX SEND HASH | :TQ30000 | | | | | | S->P | HASH | | 127 | NESTED LOOPS | | | | | | | | | | 128 | NESTED LOOPS | | 21027 | 2648K| 26101 (1)| 00:05:14 | | | | | 129 | HASH JOIN | | 21027 | 1314K| 2731 (1)| 00:00:33 | | | | | 130 | TABLE ACCESS BY INDEX ROWID | ACCOUNT_BI | 8341 | 219K| 2010 (1)| 00:00:25 | | | | | 131 | INDEX RANGE SCAN | IDX_ACCT_R_ACTION_DT | 8341 | | 29 (0)| 00:00:01 | | | | | 132 | PX COORDINATOR | | | | | | | | | | 133 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 8 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 134 | PX BLOCK ITERATOR | | 1 | 8 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | | 135 | TABLE ACCESS BY INDEX ROWID | BC_ACCOUNT | 56843 | 2053K| 720 (0)| 00:00:09 | | | | | 136 | INDEX SKIP SCAN | IDX_ACCT_07 | 554 | | 228 (0)| 00:00:03 | | | | | 137 | INDEX UNIQUE SCAN | ACCOUNT_PK | 1 | | 1 (0)| 00:00:01 | | | | | 138 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 65 | 2 (0)| 00:00:01 | | | | | 139 | INDEX RANGE SCAN | IDX_ACCT_01 | 1 | 30 | 3 (0)| 00:00:01 | | | | | 140 | NESTED LOOPS | | 1 | 45 | 5 (0)| 00:00:01 | | | | | 141 | INDEX RANGE SCAN | IDX_ACCT_03 | 1 | 28 | 3 (0)| 00:00:01 | | | | | 142 | INDEX RANGE SCAN | IDX_ACCT_02 | 1 | 17 | 2 (0)| 00:00:01 | | | | | 143 | INDEX RANGE SCAN | IDX_ACCT_01 | 1 | 30 | 3 (0)| 00:00:01 | | | |