select Oldaccount ||'|'|| gam.foracid ||'|'|| substr(gam.acct_name,1,25) ||'|'|| gam.schm_code ||'|'|| substr(gsp.schm_desc,1,25) ||'|'|| gsh.gl_sub_head_desc ||'|'|| gam.clr_bal_amt ||'|'|| tam.cumulative_int_credited ||'|'|| tam.cumulative_int_paid ||'|'|| gam.cust_id ||'|'|| tam.deposit_period_mths ||'|'|| tam.deposit_period_days ||'|'|| acct_opn_date ||'|'|| tam.maturity_date||'|'|| tam.maturity_amount ||'|'|| tam.cumulative_principal ||'|'|| tam.deposit_amount ||'|'|| tam.TDS_TOTAL_FROM_SELF_ACCT ||'|'|| eit.last_interest_run_date_cr ||'|'|| deposit_status ||'|'|| drt.RECEIPT_NUMBER ||'|'|| cmg.cust_const ||'|'|| rct.ref_desc ||'|'|| itc.int_tbl_code ||'|'|| itc.pegged_flg ||'|'|| cmg.cust_minor_flg ||'|'|| (itc.NRML_PCNT_CR + itc.ID_CR_PREF_PCNT)||'|'|| tsd.FLOW_FREQ_MTHS ||'|'|| tam.link_oper_account ||'|'|| 'DUMMY' from gam,tam,itc,tds,rct,eit,drt,tsp,onam,cmg,gsh,gsp, tsd where gam.foracid = trim(onam.newaccount) and gam.acid=tam.acid and gam.schm_code=tsp.schm_code and gam.schm_code=gsp.schm_code and tam.acid = itc.entity_id and gam.cust_id = cmg.cust_id and tam.acid = drt.acid and gam.acid = tds.acid (+) and gam.acid = eit.entity_id (+) and gam.acct_cls_flg != 'Y' and gam.del_flg = 'N' and gam.entity_cre_flg = 'Y' and gam.sol_id = '0575' and gam.acct_cls_flg = 'N' and gam.gl_sub_head_code=gsh.gl_sub_head_code and gsh.crncy_code='INR' and gsh.sol_id=gam.sol_id and gam.schm_code = tsd.schm_code and tsd.flow_code IN ('II','IO') and rct.ref_rec_type='44' and rct.ref_code=cmg.cust_const and gam.gl_sub_head_code!='11060' group by Oldaccount, gam.foracid, substr(gam.acct_name,1,25), gam.schm_code, gsh.gl_sub_head_desc, gsp.schm_desc, gam.cust_id, acct_opn_date, tam.deposit_amount, tam.cumulative_int_paid, tam.cumulative_int_credited, tam.cumulative_principal, tam.TDS_TOTAL_FROM_SELF_ACCT, gam.clr_bal_amt, tam.maturity_amount, tam.maturity_date, eit.last_interest_run_date_cr, cmg.cust_const, cmg.cust_minor_flg, (itc.NRML_PCNT_CR + itc.ID_CR_PREF_PCNT), rct.ref_desc, deposit_status, drt.RECEIPT_NUMBER, tam.deposit_period_mths, tam.deposit_period_days, itc.int_tbl_code ,itc.pegged_flg, tam.int_flow_freq_mths, tam.link_oper_account, tsd.FLOW_FREQ_MTHS, oldaccount order by gam.schm_code, oldaccount / +++++++++++++++++++++++++++++++++++++++ UAT +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 255 | 93585 | 2934 (1)| 00:00:36 | | 1 | SORT GROUP BY | | 255 | 93585 | 2934 (1)| 00:00:36 | | 2 | NESTED LOOPS OUTER | | 255 | 93585 | 2933 (1)| 00:00:36 | | 3 | NESTED LOOPS | | 66 | 23562 | 2801 (1)| 00:00:34 | | 4 | NESTED LOOPS OUTER | | 61 | 20313 | 2618 (1)| 00:00:32 | | 5 | NESTED LOOPS | | 61 | 19215 | 2442 (1)| 00:00:30 | | 6 | NESTED LOOPS | | 61 | 18178 | 2258 (1)| 00:00:28 | |* 7 | HASH JOIN | | 60 | 14580 | 2181 (1)| 00:00:27 | |* 8 | HASH JOIN | | 64 | 13120 | 2164 (1)| 00:00:26 | | 9 | INLIST ITERATOR | | | | | | | 10 | TABLE ACCESS BY INDEX ROWID | TD_SCHM_DEFN_TABLE | 33 | 396 | 2 (0)| 00:00:01 | |* 11 | INDEX RANGE SCAN | IDX_TSD_FLOW_CODE | 33 | | 1 (0)| 00:00:01 | |* 12 | HASH JOIN | | 70 | 13510 | 2161 (1)| 00:00:26 | | 13 | TABLE ACCESS BY INDEX ROWID | REFERENCE_CODE_TABLE | 26 | 676 | 10 (0)| 00:00:01 | |* 14 | INDEX RANGE SCAN | IDX_REFERENCE_CODE_TABLE | 26 | | 2 (0)| 00:00:01 | | 15 | NESTED LOOPS | | 231 | 38577 | 2151 (1)| 00:00:26 | |* 16 | HASH JOIN | | 231 | 34881 | 1689 (1)| 00:00:21 | | 17 | TABLE ACCESS FULL | GEN_SCHM_PARM_TABLE | 154 | 4158 | 5 (0)| 00:00:01 | |* 18 | HASH JOIN | | 232 | 28768 | 1683 (1)| 00:00:21 | |* 19 | TABLE ACCESS BY INDEX ROWID| GENERAL_ACCT_MAST_TABLE | 7 | 651 | 7 (0)| 00:00:01 | | 20 | NESTED LOOPS | | 319 | 31581 | 275 (0)| 00:00:04 | | 21 | INDEX FULL SCAN | IDX_TD_SCHM_PARAM_TABLE | 45 | 270 | 1 (0)| 00:00:01 | |* 22 | INDEX RANGE SCAN | IDX_GAM_SCHM_SOL | 27 | | 2 (0)| 00:00:01 | | 23 | TABLE ACCESS FULL | PSB_OLD_NEW_ACCTNO_TABLE | 619K| 14M| 1405 (1)| 00:00:17 | | 24 | TABLE ACCESS BY INDEX ROWID | CUST_MAST_GEN_TABLE | 1 | 16 | 2 (0)| 00:00:01 | |* 25 | INDEX UNIQUE SCAN | IDX_CUST_MAST_GEN_TABLE | 1 | | 1 (0)| 00:00:01 | | 26 | TABLE ACCESS BY INDEX ROWID | GL_SUB_HEAD_TABLE | 505 | 19190 | 16 (0)| 00:00:01 | |* 27 | INDEX RANGE SCAN | IDX_GL_SUB_HEAD_TABLE | 506 | | 4 (0)| 00:00:01 | | 28 | TABLE ACCESS BY INDEX ROWID | TD_ACCT_MASTER_TABLE | 1 | 55 | 2 (0)| 00:00:01 | |* 29 | INDEX UNIQUE SCAN | IDX_TD_ACCT_MASTER_TABLE | 1 | | 1 (0)| 00:00:01 | | 30 | TABLE ACCESS BY INDEX ROWID | DEPOSIT_RECEIPT_TABLE | 1 | 17 | 3 (0)| 00:00:01 | |* 31 | INDEX RANGE SCAN | IDX_DEPOSIT_RECEIPT_TABLE | 1 | | 2 (0)| 00:00:01 | | 32 | TABLE ACCESS BY INDEX ROWID | ENTITY_INTEREST_TABLE | 1 | 18 | 3 (0)| 00:00:01 | |* 33 | INDEX RANGE SCAN | IDX_ENTITY_INTEREST_TABLE | 1 | | 2 (0)| 00:00:01 | | 34 | TABLE ACCESS BY INDEX ROWID | INT_TBL_CODE_TABLE | 1 | 24 | 3 (0)| 00:00:01 | |* 35 | INDEX RANGE SCAN | IDX_INT_TBL_CODE_TABLE | 1 | | 2 (0)| 00:00:01 | |* 36 | INDEX RANGE SCAN | IDX_TDS_ACID_TRANDATE | 4 | 40 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 7 - access("GAM"."GL_SUB_HEAD_CODE"="GSH"."GL_SUB_HEAD_CODE" AND "GSH"."SOL_ID"="GAM"."SOL_ID") 8 - access("GAM"."SCHM_CODE"="TSD"."SCHM_CODE") 11 - access("TSD"."FLOW_CODE"='II' OR "TSD"."FLOW_CODE"='IO') 12 - access("RCT"."REF_CODE"="CMG"."CUST_CONST") 14 - access("RCT"."REF_REC_TYPE"='44') 16 - access("GAM"."SCHM_CODE"="GSP"."SCHM_CODE") 18 - access("GAM"."FORACID"=TRIM("ONAM"."NEWACCOUNT")) 19 - filter("GAM"."CUST_ID" IS NOT NULL AND "GAM"."ACCT_CLS_FLG"='N' AND "GAM"."ACCT_CLS_FLG"<>'Y' AND "GAM"."GL_SUB_HEAD_CODE"<>'11060' AND "GAM"."DEL_FLG"='N' AND "GAM"."ENTITY_CRE_FLG"='Y') 22 - access("GAM"."SCHM_CODE"="TSP"."SCHM_CODE" AND "GAM"."SOL_ID"='0575') 25 - access("GAM"."CUST_ID"="CMG"."CUST_ID") 27 - access("GSH"."SOL_ID"='0575' AND "GSH"."CRNCY_CODE"='INR') filter("GSH"."CRNCY_CODE"='INR' AND "GSH"."GL_SUB_HEAD_CODE"<>'11060') 29 - access("GAM"."ACID"="TAM"."ACID") 31 - access("TAM"."ACID"="DRT"."ACID") 33 - access("GAM"."ACID"="EIT"."ENTITY_ID"(+)) 35 - access("TAM"."ACID"="ITC"."ENTITY_ID") 36 - access("GAM"."ACID"="TDS"."ACID"(+)) 65 rows selected. ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Production +++++++++++++++++++++++++++++++++++++ ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 372 | 1482 (1)| 00:00:18 | | 1 | SORT GROUP BY | | 1 | 372 | 1482 (1)| 00:00:18 | | 2 | NESTED LOOPS | | 1 | 372 | 1480 (1)| 00:00:18 | | 3 | NESTED LOOPS OUTER | | 1 | 347 | 75 (3)| 00:00:01 | | 4 | NESTED LOOPS OUTER | | 1 | 337 | 74 (3)| 00:00:01 | | 5 | NESTED LOOPS | | 1 | 319 | 73 (3)| 00:00:01 | | 6 | NESTED LOOPS | | 1 | 295 | 72 (3)| 00:00:01 | | 7 | NESTED LOOPS | | 1 | 279 | 71 (3)| 00:00:01 | | 8 | NESTED LOOPS | | 1 | 224 | 70 (3)| 00:00:01 | | 9 | NESTED LOOPS | | 1 | 197 | 69 (3)| 00:00:01 | | 10 | NESTED LOOPS | | 1 | 159 | 68 (3)| 00:00:01 | | 11 | MERGE JOIN | | 1 | 147 | 67 (3)| 00:00:01 | | 12 | SORT JOIN | | 243 | 27945 | 65 (2)| 00:00:01 | | 13 | NESTED LOOPS | | 243 | 27945 | 64 (0)| 00:00:01 | | 14 | NESTED LOOPS | | 239 | 23661 | 28 (0)| 00:00:01 | | 15 | INDEX FULL SCAN | IDX_TD_SCHM_PARAM_TABLE | 45 | 270 | 1 (0)| 00:00:01 | |* 16 | TABLE ACCESS BY INDEX ROWID| GENERAL_ACCT_MAST_TABLE | 5 | 465 | 1 (0)| 00:00:01 | |* 17 | INDEX RANGE SCAN | IDX_GAM_SCHM_SOL | 22 | | 1 (0)| 00:00:01 | | 18 | TABLE ACCESS BY INDEX ROWID | CUST_MAST_GEN_TABLE | 1 | 16 | 1 (0)| 00:00:01 | |* 19 | INDEX UNIQUE SCAN | IDX_CUST_MAST_GEN_TABLE | 1 | | 1 (0)| 00:00:01 | |* 20 | SORT JOIN | | 18 | 576 | 2 (50)| 00:00:01 | | 21 | TABLE ACCESS BY INDEX ROWID | REFERENCE_CODE_TABLE | 18 | 576 | 1 (0)| 00:00:01 | |* 22 | INDEX RANGE SCAN | IDX_REFERENCE_CODE_TABLE | 18 | | 1 (0)| 00:00:01 | |* 23 | TABLE ACCESS BY INDEX ROWID | TD_SCHM_DEFN_TABLE | 1 | 12 | 1 (0)| 00:00:01 | |* 24 | INDEX RANGE SCAN | IDX_TD_SCHM_DEFN_TABLE | 3 | | 1 (0)| 00:00:01 | | 25 | TABLE ACCESS BY INDEX ROWID | GL_SUB_HEAD_TABLE | 1 | 38 | 1 (0)| 00:00:01 | |* 26 | INDEX UNIQUE SCAN | IDX_GL_SUB_HEAD_TABLE | 1 | | 1 (0)| 00:00:01 | | 27 | TABLE ACCESS BY INDEX ROWID | GEN_SCHM_PARM_TABLE | 1 | 27 | 1 (0)| 00:00:01 | |* 28 | INDEX UNIQUE SCAN | IDX_GEN_SCHM_PARM_TABLE | 1 | | 1 (0)| 00:00:01 | | 29 | TABLE ACCESS BY INDEX ROWID | TD_ACCT_MASTER_TABLE | 1 | 55 | 1 (0)| 00:00:01 | |* 30 | INDEX UNIQUE SCAN | IDX_TD_ACCT_MASTER_TABLE | 1 | | 1 (0)| 00:00:01 | | 31 | TABLE ACCESS BY INDEX ROWID | DEPOSIT_RECEIPT_TABLE | 1 | 16 | 1 (0)| 00:00:01 | |* 32 | INDEX RANGE SCAN | IDX_DEPOSIT_RECEIPT_TABLE | 1 | | 1 (0)| 00:00:01 | | 33 | TABLE ACCESS BY INDEX ROWID | INT_TBL_CODE_TABLE | 1 | 24 | 1 (0)| 00:00:01 | |* 34 | INDEX RANGE SCAN | IDX_INT_TBL_CODE_TABLE | 1 | | 1 (0)| 00:00:01 | | 35 | TABLE ACCESS BY INDEX ROWID | ENTITY_INTEREST_TABLE | 1 | 18 | 1 (0)| 00:00:01 | |* 36 | INDEX RANGE SCAN | IDX_ENTITY_INTEREST_TABLE | 1 | | 1 (0)| 00:00:01 | |* 37 | INDEX RANGE SCAN | IDX_TDS_ACID_TRANDATE | 4 | 40 | 1 (0)| 00:00:01 | |* 38 | TABLE ACCESS FULL | PSB_OLD_NEW_ACCTNO_TABLE | 1 | 25 | 1405 (1)| 00:00:17 | ----------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 16 - filter("GAM"."CUST_ID" IS NOT NULL AND "GAM"."ACCT_CLS_FLG"='N' AND "GAM"."ACCT_CLS_FLG"<>'Y' AND "GAM"."GL_SUB_HEAD_CODE"<>'11060' AND "GAM"."DEL_FLG"='N' AND "GAM"."ENTITY_CRE_FLG"='Y') 17 - access("GAM"."SCHM_CODE"="TSP"."SCHM_CODE" AND "GAM"."SOL_ID"='0575') 19 - access("GAM"."CUST_ID"="CMG"."CUST_ID") 20 - access("RCT"."REF_CODE"="CMG"."CUST_CONST") filter("RCT"."REF_CODE"="CMG"."CUST_CONST") 22 - access("RCT"."REF_REC_TYPE"='44') 23 - filter("TSD"."FLOW_CODE"='II' OR "TSD"."FLOW_CODE"='IO') 24 - access("GAM"."SCHM_CODE"="TSD"."SCHM_CODE") 26 - access("GSH"."SOL_ID"='0575' AND "GAM"."GL_SUB_HEAD_CODE"="GSH"."GL_SUB_HEAD_CODE" AND "GSH"."CRNCY_CODE"='INR') filter("GSH"."GL_SUB_HEAD_CODE"<>'11060') 28 - access("GAM"."SCHM_CODE"="GSP"."SCHM_CODE") 30 - access("GAM"."ACID"="TAM"."ACID") 32 - access("TAM"."ACID"="DRT"."ACID") 34 - access("TAM"."ACID"="ITC"."ENTITY_ID") 36 - access("GAM"."ACID"="EIT"."ENTITY_ID"(+)) 37 - access("GAM"."ACID"="TDS"."ACID"(+)) 38 - filter("GAM"."FORACID"=TRIM("ONAM"."NEWACCOUNT"))