Thanks for the question, tammy .

Asked: September 04, 2002 - 9:52 pm UTC

Last updated: March 05, 2003 - 8:07 am UTC

Version: 8.1.7.3.0

Viewed 1000+ times

You Asked

Hi Tom:
Thank you for your replying my question so quick last Saturday.... I have another question here. Developer send me a script to create a Materialized View which is taking forever to run, the table is only around 3 million rows, there is no index on all the columns as well. Last week I tried to create index, it took forever as well.

Here is the MV creation script:
==========================
spool DW_CUR_HST_DHS_DLY.lst

alter table DOW_DATA.HST_DHS_DAILY_EXTRACT parallel 8;

alter session enable PARALLEL DML;

drop materialized view DOW_DATA.DW_CUR_HST_DHS_DLY
/

CREATE MATERIALIZED VIEW DOW_DATA.DW_CUR_HST_DHS_DLY
PCTFREE 10
PCTUSED 80
MAXTRANS 255
TABLESPACE DATA_4M
LOGGING
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
AS SELECT
A.DEC_APP_STATUS ,
A.APP_AUTOMATIC_NUMBER ,
A.APP_APPLICATION_DATE ,
A.APP_FIRST_NAME ,
A.APP_LAST_NAME ,
A.APP_MIDDLE_INITIAL ,
A.APP_DATE_OF_BIRTH ,
A.APP_SOCIAL_SECURITY ,
A.APP_ADDRESS_NUMBER ,
A.APP_ADDRESS_STREET ,
A.APP_STREET_TYPE ,
A.APP_UNIT_NO ,
A.APP_FULL_ADDRESS_CB ,
A.APP_CITY ,
A.APP_STATE ,
A.APP_ZIP ,
A.APP_ZIP_COMPLEMENT ,
A.APP_RENT_OWNER ,
A.APP_TIME_AT_RESIDENCE ,
A.APP_HOME_AREA_CODE ,
A.APP_HOME_PHONE ,
A.APP_MOTHER_S_MAIDEN_N ,
A.APP_ID_TYPE ,
A.APP_ID_STATE ,
A.APP_ID_NO ,
A.APP_EMPLOYER_NAME ,
A.APP_POSITION_CODE ,
A.APP_TIME_AT_JOB ,
A.APP_WORK_AREA_CODE ,
A.APP_WORK_PHONE_NUMBER ,
A.APP_E_MAIL_ADDRESS ,
A.CB_REPORTDATE ,
A.CB_MODEL_TYPE ,
A.CB_RISKSCORE ,
A.CB_FICO_SCORE ,
A.CB_FRAUDSHIELD_SCORE ,
A.CB_PUBLIC_RECORDSCOUN ,
A.CB_INSTALLMENT_BALANC ,
A.CB_REVOLVING_BALANCE ,
A.CB_PAST_DUE_AMOUNT ,
A.CB_MONTHLY_PAYMENT ,
A.CB_REVOLV_AVAIL_PERC ,
A.CB_TOTAL_TRADE_COUN ,
A.CB_SATISF_ACC_COUNTER ,
A.CB_NOW_DELINQ_DEROG_C ,
A.CB_WAS_DELINQ_DEROG_C ,
A.CB_OLDEST_TRADE_DATE ,
A.CB_DAY_30_COUNTER ,
A.CB_DAY_60_COUNTER ,
A.CB_DAY_90_COUNTER ,
A.CB_DEROG_COUNTER ,
A.CB_SCORE_FACTOR_CODE ,
A.CB_SCORE_FACTOR_CODE_1 ,
A.CB_SCORE_FACTOR_CODE_2 ,
A.CB_SCORE_FACTOR_CODE_3 ,
A.DEC_DFS_CUSTOMER_FLAG ,
A.DEC_DECISION ,
A.DEC_DECISION_DATE ,
A.DEC_CREDIT_LIMIT ,
A.DEC_APR ,
A.DEC_EXPLANATION_1 ,
A.DEC_TRS_CUSTOMER_FOUN ,
A.DEC_LAST_TRS_DEC_DATE ,
A.DEC_LAST_TRS_DECISION ,
A.DEC_LMS_CUSTOMER_FOUN ,
A.DEC_LAST_LMS_DEC_DATE ,
A.DEC_LAST_LMS_DECISION ,
A.DEC_LMS_CREDIT_ON_HOL ,
A.DEC_LMS_LAST_DECSTATU ,
A.DEC_LMS_LAST_DEC_CRED ,
A.COMPANY_NUMBER ,
A.LMS_LEASE_QUOTE_NUMBE ,
A.LMS_LEASE_CREATE_DATE ,
A.SALES_REP_NAME ,
A.ORDER_AMOUNT ,
A.DOMS_ORDER_NUMBER ,
A.CUST_DFS_CUSTOMER_NO_1 ,
A.CUST_DFS_CUSTOMER_NO_2 ,
A.CUST_DFS_CUSTOMER_NO_3 ,
A.CUST_DFS_CUSTOMER_NO_4 ,
A.CUST_DELL_CUSTOMER_NO_1 ,
A.CUST_DELL_CUSTOMER_NO_2 ,
A.CUST_DELL_CUSTOMER_NO_3 ,
A.CUST_DELL_CUSTOMER_NO_4 ,
A.ORIGIN ,
A.DECISION_CODE_1 ,
A.DECISION_CODE_2 ,
A.DECISION_CODE_3 ,
A.DECISION_CODE_4 ,
A.MATRIX_CODE ,
A.MATRIX_CODE_COMPARISO ,
A.BK_SCORE ,
A.CB_TYPE_CB_REPORT ,
A.LMS_CREDIT_USED ,
A.CB_FRAUDSHIELD_SCOR_1 ,
A.CB_FRAUDSHIELD_SCOR_2 ,
A.CB_FRAUDSHIELD_SCORE_3 ,
A.CB_FRAUDSHIELD_SCORE_4 ,
A.BK_SC_FACTOR_CODE_1 ,
A.BK_SC_FACTOR_CODE_2 ,
A.BK_SC_FACTOR_CODE_3 ,
A.BK_SC_FACTOR_CODE_4 ,
A.FS_SCORE ,
A.FS_SC_FACTOR_CODE_1 ,
A.FS_SC_FACTOR_CODE_2 ,
A.FS_SC_FACTOR_CODE_3 ,
A.FS_SC_FACTOR_CODE_4 ,
A.AU_INWALLET_SCORE ,
A.AU_ACCEPT_REFER_CODE ,
A.LMS_NEG_FILE_ACTION ,
A.LMS_60_DAY_ACTION ,
A.LMS_REF_ACTION ,
A.FIRST_BUREAU ,
A.EQ_CONNECTION_ERROR ,
A.EQ_PROC_ERROR_CODE ,
A.CB_BEACON ,
A.CB_REAS1 ,
A.CB_REAS2 ,
A.CB_REAS3 ,
A.CB_REAS4 ,
A.TR_DECL_SOURCE ,
A.FPDSC_SCORECARD_TOTAL ,
A.FRAUD_OVERRIDE_REASON ,
A.CHANNEL ,
A.OVERRIDE_REASON_CODE ,
A.UPDATE_DATE ,
A.UPDATE_NAME ,
A.INSERT_DATE ,
A.INSERT_NAME
FROM HST_DHS_DAILY_EXTRACT A
WHERE A.insert_date = (SELECT MAX(B.insert_date)
FROM HST_DHS_DAILY_EXTRACT B
WHERE A.APP_AUTOMATIC_NUMBER = B.APP_AUTOMATIC_NUMBER)

/
===================

Here is the indexes creation I tried to use:

=======================

spool HST_DHS_DAILY_EXTRACT_IND.lst

alter session enable PARALLEL DML;

CREATE INDEX DOW_DATA.HST_DHS_DLY_EXT_INS_DTE
ON DOW_DATA.HST_DHS_DAILY_EXTRACT(INSERT_DATE)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE INDEX_128M
STORAGE(FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
NOLOGGING
PARALLEL(DEGREE 8 INSTANCES 1)
/

CREATE INDEX DOW_DATA.HST_DHS_DLY_EXT_APP_AUT_NUM
ON DOW_DATA.HST_DHS_DAILY_EXTRACT(APP_AUTOMATIC_NUMBER)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE INDEX_128M
STORAGE(FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
NOLOGGING
PARALLEL(DEGREE 8 INSTANCES 1)
/

CREATE INDEX DOW_DATA.HST_DHS_DLY_EXT_AUT_NUM_INS_DTE
ON DOW_DATA.HST_DHS_DAILY_EXTRACT(APP_AUTOMATIC_NUMBER,INSERT_DATE)
PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE INDEX_128M
STORAGE(FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
NOLOGGING
PARALLEL(DEGREE 8 INSTANCES 1)
/
====================

neither of them are successful. today I use MV creation script without indexes, I kicked off at 2:45pm, it is still running right now (9:00pm). What recommendation I should give to developer? I believe this MV will need to be refreshed frequently (complete refresh). table HST_DHS_DAILY_EXTRACT is around 3 millions rows of data.

and Tom said...

Well, I personally cannot understand what you mean by "neither of them are successful" (it only takes me a couple of minutes to create indexes on a one million row table on my LAPTOP). Would need more info about that.

Their query should be:

AS SELECT
A.DEC_APP_STATUS ,
A.APP_AUTOMATIC_NUMBER ,
A.APP_APPLICATION_DATE ,
....
A.INSERT_DATE ,
A.INSERT_NAME
from ( select hst_dhs_daily_extract.*,
row_number() over ( partition by app_automatic_number
order by insert_date DESC nulls last ) rn
FROM HST_DHS_DAILY_EXTRACT ) A
where rn = 1
/

it'll do in a single pass what you are trying to do with a correlated subquery.

(you guys do use the COST Based optimizer and have upto date stats right???)

Rating

(7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 05, 2002 - 10:08 am UTC

Hi Tom:
in the init.ora file the setting is:"optimizer_mode = choose". so I think we are using choose mode, and we do analyze all table estimate statistics sample 10 percent;

Is it wrong? I thought choose mode is the right way to go for data warehouse?

September 05, 2002 - 9:21 pm UTC

if you analyze and have CHOOSE set, you are using the Cost based optimizer

Please explain

Neeraj Verma, September 05, 2002 - 5:04 pm UTC

could you please explain this subquery in little more detail
...
...
from ( select hst_dhs_daily_extract.*,
row_number() over ( partition by app_automatic_number
order by insert_date DESC nulls last ) rn
FROM HST_DHS_DAILY_EXTRACT ) A
where rn = 1

Thanks

September 05, 2002 - 10:45 pm UTC

search this site for

analytic

if you have my book "expert one on one" read the chapter on analytic functions.

or read the data warehousing guide (on otn.oracle.com) -- the chapter on analytics

window function versus corellated subquery

mikito harakiri, September 05, 2002 - 8:21 pm UTC

They should have the same performance, if you have an index on INSERT_DATE. I compared both on 9.2 and got about 42K buffer gets each. Window query is much faster, of course, if there is no index on semijoin column. Check your plan, whether it's something like this:

SELECT STATEMENT
FILTER
TABLE ACCESS FULL HST_DHS_DAILY_EXTRACT
SORT AGGREGATE
FILTER
FIRST ROW
INDEX FULL SCAN (MIN/MAX) HST_DHS_DLY_EXT_INS_DTE

confused

tammy pickett, October 04, 2002 - 4:23 pm UTC

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

October 04, 2002 - 7:19 pm UTC

and what is the runtime.

it is so fast

tammy pickett, October 04, 2002 - 9:46 pm UTC

Hi Tom: I did run the query you provide:
from ( select DW_DHS_DAILY_EXTR_HST.*,
row_number() over ( partition by APP_AUTOMATIC_NBR
order by INS_DT DESC nulls last ) rn
FROM DW_DHS_DAILY_EXTR_HST ) A
where rn = 1
/

it took 24 minutes to create view contains 2352155 rows;
the original query:
FROM DW_DHS_DAILY_EXTR_HST A
WHERE A.INS_DT = (SELECT MAX(B.INS_DT)
FROM DW_DHS_DAILY_EXTR_HST B
WHERE A.APP_AUTOMATIC_NBR = B.APP_AUTOMATIC_NBR)
/
it took about 2 hour and 20 minutes.

October 05, 2002 - 11:02 am UTC

Well then, it is the right answer ;)

You cannot compare the costs of two different queries. The cost is used WITHIN a query to compare the alternative plans for A QUERY to find the one that should be used for THAT QUERY.

These are two wholly different queries.

different result

A reader, October 04, 2002 - 11:13 pm UTC

Tom: there is some difference between first select statement and the one you wrote:

SQL> select count(*) from DW_CUR_HST_DHS_DLY_TP;-- this is the improved query result

  COUNT(*)
----------
   2352155
        
   
SQL> select count (*) from DW_CUR_HST_DHS_DLY;-- this is the original query result

  COUNT(*)
----------
   2678051
not sure why??

October 05, 2002 - 11:09 am UTC

The one with row_number is getting the first record for each app_automatic_number, your original gets one or more records for each. Sorry about that -- use DENSE_RANK() instead of ROW_NUMBER() -- thats the only change you need to make.

What Type of Query & Indexing is best

A reader, March 05, 2003 - 2:59 am UTC

Hi tom,
If we have a table on DW with historrical data which would be the best query and what type of indexing should be used.

Query currently being used
SELECT *
FROM CUSTOMERS
WHERE (ID,START_DATE) IN (SELECT ID,MAX(START_DATE)
FROM CUSTOMERS
WHERE START_DATE <= TO_DATE(SYSDATE)
AND (END_DATE IS NULL
OR END_DATE > TO_DATE(SYSDATE))
GROUP BY ID)

sometimes there maybe more than 1 column which uniquely identify the unique record foe eg many queries

March 05, 2003 - 8:07 am UTC

NEVER to_date a DATE -- do you know what that does, here is the short list:

o puts a bug in your code
o adds overhead to everything you do

You are taking something that is already a DATE -- turning it into a string -- then turning it back into a date. if you want to lose the time component, use a function that works on a date already -- TRUNC().

the optimal query will depend on many factors. do you commonly select ALL of the rows from this table? If so, a materialized view would be very appropriate here -- full refreshed once a day. that way, it becomes a trivial "select * from t"

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Please explain

window function versus corellated subquery

confused

it is so fast

different result

What Type of Query & Indexing is best

More to Explore

Analytics

query performance

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Please explain

window function versus corellated subquery

confused

it is so fast

different result

What Type of Query & Indexing is best

More to Explore

Analytics