tom: I did run the query you provide but explain plan tells me that it is more expensive. could you explain why?
I have indexes on app_automatic_number and ins_dt,plus composite index on (app_automatic_number, ins_dt).
15:22:13 SQL>
15:22:13 SQL>
15:22:13 SQL> set autotrace traceonly exp
15:22:13 SQL>
15:22:13 SQL>
15:22:13 SQL> SELECT
15:22:13 2 A.DEC_APP_STT AS DEC_APP_STATUS,
15:22:13 3 A.APP_AUTOMATIC_NBR AS APP_AUTOMATIC_NUMBER,
15:22:13 4 A.APP_APPLCTN_DT AS APP_APPLICATION_DATE,
15:22:13 5 A.APP_FRST_NM AS APP_FIRST_NAME ,
15:22:13 6 A.APP_LST_NM AS APP_LAST_NAME ,
15:22:13 7 A.APP_MID_INIT AS APP_MIDDLE_INITIAL,
15:22:13 8 A.APP_DT_OF_BRTH AS APP_DATE_OF_BIRTH ,
15:22:13 9 A.APP_SOC_SEC AS APP_SOCIAL_SECURITY ,
15:22:13 10 A.APP_ADDR_NBR AS APP_ADDRESS_NUMBER ,
15:22:13 11 A.APP_ADDR_STR AS APP_ADDRESS_STREET,
15:22:13 12 A.APP_STR_TYP AS APP_STREET_TYPE ,
15:22:13 13 A.APP_UNT_NBR AS APP_UNIT_NO ,
15:22:13 14 A.APP_FULL_ADDR_CB AS APP_FULL_ADDRESS_CB ,
15:22:13 15 A.APP_CTY AS APP_CITY,
15:22:13 16 A.APP_ST AS APP_STATE,
15:22:13 17 A.APP_ZIP AS APP_ZIP ,
15:22:13 18 A.APP_ZIP_COMPLM AS APP_ZIP_COMPLEMENT,
15:22:13 19 A.APP_RENT_OWNR AS APP_RENT_OWNER,
15:22:13 20 A.APP_TM_AT_RSDNC AS APP_TIME_AT_RESIDENCE ,
15:22:13 21 A.APP_HOME_AREA_CD AS APP_HOME_AREA_CODE ,
15:22:13 22 A.APP_HOME_PH AS APP_HOME_PHONE ,
15:22:13 23 A.APP_MOTHERS_MAIDEN_NM AS APP_MOTHER_S_MAIDEN_N,
15:22:13 24 A.APP_ID_TYP AS APP_ID_TYPE,
15:22:13 25 A.APP_ID_ST AS APP_ID_STATE,
15:22:13 26 A.APP_ID_NBR AS APP_ID_NO,
15:22:13 27 A.APP_EMPLR_NM AS APP_EMPLOYER_NAME,
15:22:13 28 A.APP_POS_CD AS APP_POSITION_CODE,
15:22:13 29 A.APP_TM_AT_JOB AS APP_TIME_AT_JOB ,
15:22:13 30 A.APP_WRK_AREA_CD AS APP_WORK_AREA_CODE,
15:22:13 31 A.APP_WRK_PH_NBR AS APP_WORK_PHONE_NUMBER,
15:22:13 32 A.APP_E_MAIL_ADDR AS APP_E_MAIL_ADDRESS,
15:22:13 33 A.CB_RPTDT AS CB_REPORTDATE,
15:22:13 34 A.CB_MOD_TYP AS CB_MODEL_TYPE ,
15:22:13 35 A.CB_RSKSCORE AS CB_RISKSCORE ,
15:22:13 36 A.CB_FICO_SCORE AS CB_FICO_SCORE ,
15:22:13 37 A.CB_FRAUDSHIELD_SCORE AS CB_FRAUDSHIELD_SCORE,
15:22:13 38 A.CB_PUBLC_RECS_CNT AS CB_PUBLIC_RECORDSCOUN,
15:22:13 39 A.CB_INSTLMNT_BAL AS CB_INSTALLMENT_BALANC,
15:22:13 40 A.CB_REVOLVING_BAL AS CB_REVOLVING_BALANCE,
15:22:13 41 A.CB_PAST_DUE_AMT AS CB_PAST_DUE_AMOUNT,
15:22:13 42 A.CB_MTHLY_PMT AS CB_MONTHLY_PAYMENT,
15:22:13 43 A.CB_REVOLV_AVAIL_PERC AS CB_REVOLV_AVAIL_PERC,
15:22:13 44 A.CB_TOT_TRD_CNT AS CB_TOTAL_TRADE_COUN,
15:22:13 45 A.CB_SATISF_ACC_CTR AS CB_SATISF_ACC_COUNTER,
15:22:13 46 A.CB_NOW_DELINQ_DEROG_C AS CB_NOW_DELINQ_DEROG_C,
15:22:13 47 A.CB_WAS_DELINQ_DEROG_C AS CB_WAS_DELINQ_DEROG_C ,
15:22:13 48 A.CB_OLDEST_TRD_DT AS CB_OLDEST_TRADE_DATE,
15:22:13 49 A.CB_DY_30_CTR AS CB_DAY_30_COUNTER,
15:22:13 50 A.CB_DY_60_CTR AS CB_DAY_60_COUNTER,
15:22:13 51 A.CB_DY_90_CTR AS CB_DAY_90_COUNTER,
15:22:13 52 A.CB_DEROG_CTR AS CB_DEROG_COUNTER ,
15:22:13 53 A.CB_SCORE_FACTOR_CD AS CB_SCORE_FACTOR_CODE ,
15:22:13 54 A.CB_SCORE_FACTOR_CD_1 AS CB_SCORE_FACTOR_CODE_1 ,
15:22:13 55 A.CB_SCORE_FACTOR_CD_2 AS CB_SCORE_FACTOR_CODE_2 ,
15:22:13 56 A.CB_SCORE_FACTOR_CD_3 AS CB_SCORE_FACTOR_CODE_3 ,
15:22:13 57 A.DEC_DFS_CUST_FL AS DEC_DFS_CUSTOMER_FLAG,
15:22:13 58 A.DEC_DCSN AS DEC_DECISION ,
15:22:13 59 A.DEC_DCSN_DT AS DEC_DECISION_DATE,
15:22:13 60 A.DEC_CRD_LIMIT AS DEC_CREDIT_LIMIT,
15:22:13 61 A.DEC_APR AS DEC_APR,
15:22:13 62 A.DEC_EXPLANATION_1 AS DEC_EXPLANATION_1 ,
15:22:13 63 A.DEC_TRS_CUST_FOUN AS DEC_TRS_CUSTOMER_FOUN ,
15:22:13 64 A.DEC_LST_TRS_DEC_DT AS DEC_LAST_TRS_DEC_DATE,
15:22:13 65 A.DEC_LST_TRS_DCSN AS DEC_LAST_TRS_DECISION ,
15:22:13 66 A.DEC_LMS_CUST_FOUN AS DEC_LMS_CUSTOMER_FOUN,
15:22:13 67 A.DEC_LST_LMS_DEC_DT AS DEC_LAST_LMS_DEC_DATE,
15:22:13 68 A.DEC_LST_LMS_DCSN AS DEC_LAST_LMS_DECISION,
15:22:13 69 A.DEC_LMS_CRD_ON_HOL AS DEC_LMS_CREDIT_ON_HOL,
15:22:13 70 A.DEC_LMS_LST_DECSTATU AS DEC_LMS_LAST_DECSTATU ,
15:22:13 71 A.DEC_LMS_LST_DEC_CRED AS DEC_LMS_LAST_DEC_CRED,
15:22:13 72 A.COMPANY_NBR AS COMPANY_NUMBER,
15:22:13 73 A.LMS_LEASE_QUOTE_NBRBE AS LMS_LEASE_QUOTE_NUMBE,
15:22:13 74 A.LMS_LEASE_CREATE_DT AS LMS_LEASE_CREATE_DATE ,
15:22:13 75 A.SALES_REP_NM AS SALES_REP_NAME ,
15:22:13 76 A.ORD_AMT AS ORDER_AMOUNT,
15:22:13 77 A.DOMS_ORD_NBR AS DOMS_ORDER_NUMBER,
15:22:13 78 A.CUST_DFS_CUST_NO_1 AS CUST_DFS_CUSTOMER_NO_1,
15:22:13 79 A.CUST_DFS_CUST_NO_2 AS CUST_DFS_CUSTOMER_NO_2 ,
15:22:13 80 A.CUST_DFS_CUST_NO_3 AS CUST_DFS_CUSTOMER_NO_3,
15:22:13 81 A.CUST_DFS_CUST_NO_4 AS CUST_DFS_CUSTOMER_NO_4,
15:22:13 82 A.CUST_DELL_CUST_NO_1 AS CUST_DELL_CUSTOMER_NO_1,
15:22:13 83 A.CUST_DELL_CUST_NO_2 AS CUST_DELL_CUSTOMER_NO_2,
15:22:13 84 A.CUST_DELL_CUST_NO_3 AS CUST_DELL_CUSTOMER_NO_3,
15:22:13 85 A.CUST_DELL_CUST_NO_4 AS CUST_DELL_CUSTOMER_NO_4,
15:22:13 86 A.ORIG AS ORIGIN ,
15:22:13 87 A.DCSN_CD_1 AS DECISION_CODE_1 ,
15:22:13 88 A.DCSN_CD_2 AS DECISION_CODE_2 ,
15:22:13 89 A.DCSN_CD_3 AS DECISION_CODE_3 ,
15:22:13 90 A.DCSN_CD_4 AS DECISION_CODE_4 ,
15:22:13 91 A.MATRIX_CD AS MATRIX_CODE,
15:22:13 92 A.MATRIX_CD_CMPRSN AS MATRIX_CODE_COMPARISO ,
15:22:13 93 A.BK_SCORE AS BK_SCORE ,
15:22:13 94 A.CB_TYP_CB_RPT AS CB_TYPE_CB_REPORT,
15:22:13 95 A.LMS_CRD_USED AS LMS_CREDIT_USED ,
15:22:13 96 A.CB_FRAUDSHIELD_SCORE_1 AS CB_FRAUDSHIELD_SCOR_1,
15:22:13 97 A.CB_FRAUDSHIELD_SCORE_2 AS CB_FRAUDSHIELD_SCOR_2 ,
15:22:13 98 A.CB_FRAUDSHIELD_SCORE_3 AS CB_FRAUDSHIELD_SCORE_3 ,
15:22:13 99 A.CB_FRAUDSHIELD_SCORE_4 AS CB_FRAUDSHIELD_SCORE_4 ,
15:22:13 100 A.BK_SC_FACTOR_CD_1 AS BK_SC_FACTOR_CODE_1,
15:22:13 101 A.BK_SC_FACTOR_CD_2 AS BK_SC_FACTOR_CODE_2 ,
15:22:13 102 A.BK_SC_FACTOR_CD_3 AS BK_SC_FACTOR_CODE_3,
15:22:13 103 A.BK_SC_FACTOR_CD_4 AS BK_SC_FACTOR_CODE_4 ,
15:22:13 104 A.FS_SCORE AS FS_SCORE ,
15:22:13 105 A.FS_SC_FACTOR_CD_1 AS FS_SC_FACTOR_CODE_1 ,
15:22:13 106 A.FS_SC_FACTOR_CD_2 AS FS_SC_FACTOR_CODE_2,
15:22:13 107 A.FS_SC_FACTOR_CD_3 AS FS_SC_FACTOR_CODE_3,
15:22:13 108 A.FS_SC_FACTOR_CD_4 AS FS_SC_FACTOR_CODE_4,
15:22:13 109 A.AU_INWALLET_SCORE AS AU_INWALLET_SCORE,
15:22:13 110 A.AU_ACPT_REFER_CD AS AU_ACCEPT_REFER_CODE,
15:22:13 111 A.LMS_NEG_FILE_ACTN AS LMS_NEG_FILE_ACTION,
15:22:13 112 A.LMS_60_DY_ACTN AS LMS_60_DAY_ACTION ,
15:22:13 113 A.LMS_REF_ACTN AS LMS_REF_ACTION,
15:22:13 114 A.FRST_BUREAU AS FIRST_BUREAU,
15:22:13 115 A.EQ_CONNECTION_ERR AS EQ_CONNECTION_ERROR,
15:22:13 116 A.EQ_PROC_ERR_CD AS EQ_PROC_ERROR_CODE,
15:22:13 117 A.CB_BEACON AS CB_BEACON ,
15:22:13 118 A.CB_REAS1 AS CB_REAS1 ,
15:22:13 119 A.CB_REAS2 AS CB_REAS2 ,
15:22:13 120 A.CB_REAS3 AS CB_REAS3,
15:22:13 121 A.CB_REAS4 AS CB_REAS4 ,
15:22:13 122 A.TR_DECL_SRC AS TR_DECL_SOURCE ,
15:22:13 123 A.FPDSC_SCORECARD_TOT AS FPDSC_SCORECARD_TOTAL,
15:22:13 124 A.FRAUD_OVERRIDE_RSN AS FRAUD_OVERRIDE_REASON,
15:22:13 125 A.CHNL AS CHANNEL,
15:22:13 126 A.OVERRIDE_RSN_CD AS OVERRIDE_REASON_CODE,
15:22:13 127 A.UPDT_DT AS UPDATE_DATE,
15:22:13 128 A.UPDT_NM AS UPDATE_NAME ,
15:22:13 129 A.INS_DT AS INSERT_DATE ,
15:22:13 130 A.INS_NM AS INSERT_NAME
15:22:13 131 from ( select DW_DHS_DAILY_EXTR_HST.*,
15:22:13 132 row_number() over ( partition by APP_AUTOMATIC_NBR
15:22:13 133 order by INS_DT DESC nulls last ) rn
15:22:13 134 FROM DW_DHS_DAILY_EXTR_HST ) A
15:22:13 135 where rn = 1
15:22:13 136 /
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=255560 Card=3703478
Bytes=3066479784)
1 0 VIEW* (Cost=255560 Card=3703478 Bytes=3066479784) :Q347700
1
2 1 WINDOW* (SORT) :Q347700
1
3 2 TABLE ACCESS* (FULL) OF 'DW_DHS_DAILY_EXTR_HST' (Cost= :Q347700
8146 Card=3703478 Bytes=3129438910) 0
1 PARALLEL_TO_SERIAL SELECT C2,C0,C4,C5,C6,C7,C8,C9,C10,C11,C12,C
13,C14,C15,C16,C17,C18,C19,C20,C21,C
2 PARALLEL_COMBINED_WITH_PARENT
3 PARALLEL_TO_PARALLEL SELECT /*+ Q3477000 NO_EXPAND ROWID(A1) */ A
1."APP_AUTOMATIC_NBR" C0,A1."INS_DT"
15:22:13 SQL>
15:22:13 SQL>
15:22:13 SQL>
15:22:13 SQL> SELECT
15:22:13 2 A.DEC_APP_STT AS DEC_APP_STATUS,
15:22:13 3 A.APP_AUTOMATIC_NBR AS APP_AUTOMATIC_NUMBER,
15:22:13 4 A.APP_APPLCTN_DT AS APP_APPLICATION_DATE,
15:22:13 5 A.APP_FRST_NM AS APP_FIRST_NAME ,
15:22:13 6 A.APP_LST_NM AS APP_LAST_NAME ,
15:22:13 7 A.APP_MID_INIT AS APP_MIDDLE_INITIAL,
15:22:13 8 A.APP_DT_OF_BRTH AS APP_DATE_OF_BIRTH ,
15:22:13 9 A.APP_SOC_SEC AS APP_SOCIAL_SECURITY ,
15:22:13 10 A.APP_ADDR_NBR AS APP_ADDRESS_NUMBER ,
15:22:13 11 A.APP_ADDR_STR AS APP_ADDRESS_STREET,
15:22:13 12 A.APP_STR_TYP AS APP_STREET_TYPE ,
15:22:13 13 A.APP_UNT_NBR AS APP_UNIT_NO ,
15:22:13 14 A.APP_FULL_ADDR_CB AS APP_FULL_ADDRESS_CB ,
15:22:13 15 A.APP_CTY AS APP_CITY,
15:22:13 16 A.APP_ST AS APP_STATE,
15:22:13 17 A.APP_ZIP AS APP_ZIP ,
15:22:13 18 A.APP_ZIP_COMPLM AS APP_ZIP_COMPLEMENT,
15:22:13 19 A.APP_RENT_OWNR AS APP_RENT_OWNER,
15:22:13 20 A.APP_TM_AT_RSDNC AS APP_TIME_AT_RESIDENCE ,
15:22:13 21 A.APP_HOME_AREA_CD AS APP_HOME_AREA_CODE ,
15:22:13 22 A.APP_HOME_PH AS APP_HOME_PHONE ,
15:22:13 23 A.APP_MOTHERS_MAIDEN_NM AS APP_MOTHER_S_MAIDEN_N,
15:22:13 24 A.APP_ID_TYP AS APP_ID_TYPE,
15:22:13 25 A.APP_ID_ST AS APP_ID_STATE,
15:22:13 26 A.APP_ID_NBR AS APP_ID_NO,
15:22:13 27 A.APP_EMPLR_NM AS APP_EMPLOYER_NAME,
15:22:13 28 A.APP_POS_CD AS APP_POSITION_CODE,
15:22:13 29 A.APP_TM_AT_JOB AS APP_TIME_AT_JOB ,
15:22:13 30 A.APP_WRK_AREA_CD AS APP_WORK_AREA_CODE,
15:22:13 31 A.APP_WRK_PH_NBR AS APP_WORK_PHONE_NUMBER,
15:22:13 32 A.APP_E_MAIL_ADDR AS APP_E_MAIL_ADDRESS,
15:22:13 33 A.CB_RPTDT AS CB_REPORTDATE,
15:22:13 34 A.CB_MOD_TYP AS CB_MODEL_TYPE ,
15:22:13 35 A.CB_RSKSCORE AS CB_RISKSCORE ,
15:22:13 36 A.CB_FICO_SCORE AS CB_FICO_SCORE ,
15:22:13 37 A.CB_FRAUDSHIELD_SCORE AS CB_FRAUDSHIELD_SCORE,
15:22:13 38 A.CB_PUBLC_RECS_CNT AS CB_PUBLIC_RECORDSCOUN,
15:22:13 39 A.CB_INSTLMNT_BAL AS CB_INSTALLMENT_BALANC,
15:22:13 40 A.CB_REVOLVING_BAL AS CB_REVOLVING_BALANCE,
15:22:13 41 A.CB_PAST_DUE_AMT AS CB_PAST_DUE_AMOUNT,
15:22:13 42 A.CB_MTHLY_PMT AS CB_MONTHLY_PAYMENT,
15:22:13 43 A.CB_REVOLV_AVAIL_PERC AS CB_REVOLV_AVAIL_PERC,
15:22:13 44 A.CB_TOT_TRD_CNT AS CB_TOTAL_TRADE_COUN,
15:22:13 45 A.CB_SATISF_ACC_CTR AS CB_SATISF_ACC_COUNTER,
15:22:13 46 A.CB_NOW_DELINQ_DEROG_C AS CB_NOW_DELINQ_DEROG_C,
15:22:13 47 A.CB_WAS_DELINQ_DEROG_C AS CB_WAS_DELINQ_DEROG_C ,
15:22:13 48 A.CB_OLDEST_TRD_DT AS CB_OLDEST_TRADE_DATE,
15:22:13 49 A.CB_DY_30_CTR AS CB_DAY_30_COUNTER,
15:22:13 50 A.CB_DY_60_CTR AS CB_DAY_60_COUNTER,
15:22:13 51 A.CB_DY_90_CTR AS CB_DAY_90_COUNTER,
15:22:13 52 A.CB_DEROG_CTR AS CB_DEROG_COUNTER ,
15:22:13 53 A.CB_SCORE_FACTOR_CD AS CB_SCORE_FACTOR_CODE ,
15:22:13 54 A.CB_SCORE_FACTOR_CD_1 AS CB_SCORE_FACTOR_CODE_1 ,
15:22:13 55 A.CB_SCORE_FACTOR_CD_2 AS CB_SCORE_FACTOR_CODE_2 ,
15:22:13 56 A.CB_SCORE_FACTOR_CD_3 AS CB_SCORE_FACTOR_CODE_3 ,
15:22:13 57 A.DEC_DFS_CUST_FL AS DEC_DFS_CUSTOMER_FLAG,
15:22:13 58 A.DEC_DCSN AS DEC_DECISION ,
15:22:13 59 A.DEC_DCSN_DT AS DEC_DECISION_DATE,
15:22:13 60 A.DEC_CRD_LIMIT AS DEC_CREDIT_LIMIT,
15:22:13 61 A.DEC_APR AS DEC_APR,
15:22:13 62 A.DEC_EXPLANATION_1 AS DEC_EXPLANATION_1 ,
15:22:13 63 A.DEC_TRS_CUST_FOUN AS DEC_TRS_CUSTOMER_FOUN ,
15:22:13 64 A.DEC_LST_TRS_DEC_DT AS DEC_LAST_TRS_DEC_DATE,
15:22:13 65 A.DEC_LST_TRS_DCSN AS DEC_LAST_TRS_DECISION ,
15:22:13 66 A.DEC_LMS_CUST_FOUN AS DEC_LMS_CUSTOMER_FOUN,
15:22:13 67 A.DEC_LST_LMS_DEC_DT AS DEC_LAST_LMS_DEC_DATE,
15:22:13 68 A.DEC_LST_LMS_DCSN AS DEC_LAST_LMS_DECISION,
15:22:13 69 A.DEC_LMS_CRD_ON_HOL AS DEC_LMS_CREDIT_ON_HOL,
15:22:13 70 A.DEC_LMS_LST_DECSTATU AS DEC_LMS_LAST_DECSTATU ,
15:22:13 71 A.DEC_LMS_LST_DEC_CRED AS DEC_LMS_LAST_DEC_CRED,
15:22:13 72 A.COMPANY_NBR AS COMPANY_NUMBER,
15:22:13 73 A.LMS_LEASE_QUOTE_NBRBE AS LMS_LEASE_QUOTE_NUMBE,
15:22:13 74 A.LMS_LEASE_CREATE_DT AS LMS_LEASE_CREATE_DATE ,
15:22:13 75 A.SALES_REP_NM AS SALES_REP_NAME ,
15:22:13 76 A.ORD_AMT AS ORDER_AMOUNT,
15:22:13 77 A.DOMS_ORD_NBR AS DOMS_ORDER_NUMBER,
15:22:13 78 A.CUST_DFS_CUST_NO_1 AS CUST_DFS_CUSTOMER_NO_1,
15:22:13 79 A.CUST_DFS_CUST_NO_2 AS CUST_DFS_CUSTOMER_NO_2 ,
15:22:13 80 A.CUST_DFS_CUST_NO_3 AS CUST_DFS_CUSTOMER_NO_3,
15:22:13 81 A.CUST_DFS_CUST_NO_4 AS CUST_DFS_CUSTOMER_NO_4,
15:22:13 82 A.CUST_DELL_CUST_NO_1 AS CUST_DELL_CUSTOMER_NO_1,
15:22:13 83 A.CUST_DELL_CUST_NO_2 AS CUST_DELL_CUSTOMER_NO_2,
15:22:13 84 A.CUST_DELL_CUST_NO_3 AS CUST_DELL_CUSTOMER_NO_3,
15:22:13 85 A.CUST_DELL_CUST_NO_4 AS CUST_DELL_CUSTOMER_NO_4,
15:22:13 86 A.ORIG AS ORIGIN ,
15:22:13 87 A.DCSN_CD_1 AS DECISION_CODE_1 ,
15:22:13 88 A.DCSN_CD_2 AS DECISION_CODE_2 ,
15:22:13 89 A.DCSN_CD_3 AS DECISION_CODE_3 ,
15:22:13 90 A.DCSN_CD_4 AS DECISION_CODE_4 ,
15:22:13 91 A.MATRIX_CD AS MATRIX_CODE,
15:22:13 92 A.MATRIX_CD_CMPRSN AS MATRIX_CODE_COMPARISO ,
15:22:13 93 A.BK_SCORE AS BK_SCORE ,
15:22:13 94 A.CB_TYP_CB_RPT AS CB_TYPE_CB_REPORT,
15:22:13 95 A.LMS_CRD_USED AS LMS_CREDIT_USED ,
15:22:13 96 A.CB_FRAUDSHIELD_SCORE_1 AS CB_FRAUDSHIELD_SCOR_1,
15:22:13 97 A.CB_FRAUDSHIELD_SCORE_2 AS CB_FRAUDSHIELD_SCOR_2 ,
15:22:13 98 A.CB_FRAUDSHIELD_SCORE_3 AS CB_FRAUDSHIELD_SCORE_3 ,
15:22:13 99 A.CB_FRAUDSHIELD_SCORE_4 AS CB_FRAUDSHIELD_SCORE_4 ,
15:22:13 100 A.BK_SC_FACTOR_CD_1 AS BK_SC_FACTOR_CODE_1,
15:22:13 101 A.BK_SC_FACTOR_CD_2 AS BK_SC_FACTOR_CODE_2 ,
15:22:13 102 A.BK_SC_FACTOR_CD_3 AS BK_SC_FACTOR_CODE_3,
15:22:13 103 A.BK_SC_FACTOR_CD_4 AS BK_SC_FACTOR_CODE_4 ,
15:22:13 104 A.FS_SCORE AS FS_SCORE ,
15:22:13 105 A.FS_SC_FACTOR_CD_1 AS FS_SC_FACTOR_CODE_1 ,
15:22:13 106 A.FS_SC_FACTOR_CD_2 AS FS_SC_FACTOR_CODE_2,
15:22:13 107 A.FS_SC_FACTOR_CD_3 AS FS_SC_FACTOR_CODE_3,
15:22:13 108 A.FS_SC_FACTOR_CD_4 AS FS_SC_FACTOR_CODE_4,
15:22:13 109 A.AU_INWALLET_SCORE AS AU_INWALLET_SCORE,
15:22:13 110 A.AU_ACPT_REFER_CD AS AU_ACCEPT_REFER_CODE,
15:22:13 111 A.LMS_NEG_FILE_ACTN AS LMS_NEG_FILE_ACTION,
15:22:13 112 A.LMS_60_DY_ACTN AS LMS_60_DAY_ACTION ,
15:22:13 113 A.LMS_REF_ACTN AS LMS_REF_ACTION,
15:22:13 114 A.FRST_BUREAU AS FIRST_BUREAU,
15:22:13 115 A.EQ_CONNECTION_ERR AS EQ_CONNECTION_ERROR,
15:22:13 116 A.EQ_PROC_ERR_CD AS EQ_PROC_ERROR_CODE,
15:22:13 117 A.CB_BEACON AS CB_BEACON ,
15:22:13 118 A.CB_REAS1 AS CB_REAS1 ,
15:22:13 119 A.CB_REAS2 AS CB_REAS2 ,
15:22:13 120 A.CB_REAS3 AS CB_REAS3,
15:22:13 121 A.CB_REAS4 AS CB_REAS4 ,
15:22:13 122 A.TR_DECL_SRC AS TR_DECL_SOURCE ,
15:22:13 123 A.FPDSC_SCORECARD_TOT AS FPDSC_SCORECARD_TOTAL,
15:22:13 124 A.FRAUD_OVERRIDE_RSN AS FRAUD_OVERRIDE_REASON,
15:22:13 125 A.CHNL AS CHANNEL,
15:22:13 126 A.OVERRIDE_RSN_CD AS OVERRIDE_REASON_CODE,
15:22:13 127 A.UPDT_DT AS UPDATE_DATE,
15:22:13 128 A.UPDT_NM AS UPDATE_NAME ,
15:22:13 129 A.INS_DT AS INSERT_DATE ,
15:22:13 130 A.INS_NM AS INSERT_NAME
15:22:13 131 FROM DW_DHS_DAILY_EXTR_HST A
15:22:13 132 WHERE A.INS_DT = (SELECT MAX(B.INS_DT)
15:22:13 133 FROM DW_DHS_DAILY_EXTR_HST B
15:22:13 134 WHERE A.APP_AUTOMATIC_NBR = B.APP_AUTOMATIC_NBR)
15:22:13 135 /
Elapsed: 00:00:00.16
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8146 Card=42569 Byte
s=35970805)
1 0 FILTER
2 1 TABLE ACCESS* (FULL) OF 'DW_DHS_DAILY_EXTR_HST' (Cost=81 :Q347900
46 Card=42569 Bytes=35970805) 0
3 1 SORT (AGGREGATE)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'DW_DHS_DAILY_EXTR_HS
T' (Cost=5 Card=2 Bytes=32)
5 4 INDEX (RANGE SCAN) OF 'DW_DHS_EXTR_HST_APP_AUT_NBR'
(NON-UNIQUE) (Cost=3 Card=2)
2 PARALLEL_TO_SERIAL SELECT /*+ Q3479000 NO_EXPAND ROWID(A1) */ A
1."DEC_APP_STT",A1."APP_AUTOMATIC_NB
15:22:13 SQL>
15:22:13 SQL> spool off