Oppolzer - Informatik / Blog

When looking at the original version of the SQL,
it seems to me as if the table VALID_VALUES is only used to
translate some of the results (codes) to other results (descriptions);
the main source for the results is the table PAYMENT_TRAN,
and since DELTA_FILE_BYTE IN ('A', 'C') is the only WHERE condition
for the main table, there is no good reason for an index select on this
table.

So I guess there is not much you can do to improve the performance
of the original SQL; the change that you did with your rewrite does not
help in my opinion. The table PAYMENT_TRAN must stay the outer table,
and if there is no index on DELTA_FILE_BYTE, you are stuck with tablespace
scan (which is maybe not at all bad, if the filter factor is high).

My expectation is that VALID_VALUES is small, compared to PAYMENT_TRAN;
so I would suggest that PAYMENT_TRAN be accessed once, and VALID_VALUES
many times, by a fast index. This is what is accomplished by the original SQL,
if I understand it right.

Only solution to speed things up IMHO is to add an index on DELTA_FILE_BYTE,
if there is no such index.

The translation from codes to descriptions via subselects on VALID_VALUES
in the SELECT clause seems no bad idea to me, if

a) it is really needed to do this in the same SQL statement, and

b) there are proper indexes to give a fast access on the table VALID_VALUES,
so that the translation from codes to descriptions will be fast

but: I would suggest not to change the original SQL, because the order of execution
forced by this SQL makes sense in my opinion.

HTH

Kind regards

Bernd



Am 31.12.2014 um 22:37 schrieb T.W.:
>
> I realize that I can't use my re-write because they have  A.IW_UV_CD = B.....
> in their SELECTs but still that does not explain why my re-write SQL when I add
> the 4th column from table PAYMENT_TRAN on the outer select DB2 quits using the
> index on PAYMENT_TRAN.  Doesn't matter which 4 columns I use.
> Three columns I get index usage add the 4th and no index usage.
> I suspect that is why the original SQL is not getting any index usage on PAYMENT_TRAN.
>
> Yes the production table as well as the test table indexes are clustered on PAYMENT_TRAN.
> Same as the clustering index on VALID_VALUES.
>
> QUALIFIER: INDEX=XXXX.MGPTRA1X.
> Column Name                    Column
> ----v----1----v----2----v----3----v--
> CLUSTERRATIO . . . . . . . . . 99
>
>
> Original SQL
>
>
> SELECT   LN_NO AS "LOAN_NUMBER"
>         , PMT_TRANS_DT AS "PMT_TRANSACTION_DATE"
>         , PMT_SEQ_NO AS "PMT_SEQUENCE_NUMBER"
>         , PMT_AH_INS_AM AS "PMT_A_H_INSURANCE_AMOUNT"
>         , PMT_ACTION_CD AS "PMT_ACTION_CODE"
>         , PMT_BATCH_NO AS "PMT_BATCH_NUMBER"
>         , PMT_ESCROW_AM AS "PMT_ESCROW_AMOUNT"
>         , PMT_FEE_AM AS "PMT_FEE_AMOUNT"
>         , PMT_FEE_CD AS "PMT_FEE_CODE"
>         , (SELECT  A.IW_VV_DE
>           FROM     XXXX.VALID_VALUES A
>           WHERE    A.IW_BV_BUS_NM = 'PAYMENT TRANSACTION'
>             AND    A.IW_BE_BUS_NM = 'PMT FEE CODE'
>             AND    A.IW_BE_SYS_NM = 'PMT_FEE_CD'
>             AND    A.CLIENT_NO = B.CLIENT_NO
>             AND    A.IW_UV_CD = B.PMT_FEE_CD
>           )  AS PMT_FEE_CODE_DESCRIPTION
>         , PMT_HUD_AM AS "PMT_HUD_AMOUNT"
>         , PMT_INTEREST_AM AS "PMT_INTEREST_AMOUNT"
>         , PMT_LIFE_INS_AM AS "PMT_LIFE_INSURANCE_AMOUNT"
>         , PMT_MISC_AM AS "PMT_MISC_AMOUNT"
>         , PMT_PRINCIPAL_AM AS "PMT_PRINCIPAL_AMOUNT"
>         , PMT_REPL_RESV_AM AS "PMT_REPLACEMENT_RES"
>         , PMT_RES_ESC_AM AS "PMT_RESTRICTED_ESC_AMOUNT"
>         , PMT_SVC_CHRG_AM AS "PMT_SERVICE_CHARGE_AMOUNT"
         , PMT_SERV_FEE_AM AS "PMT_SERVICE_FEE_AMOUNT"
>         , PMT_SUB_CD AS "PMT_SUB_CODE"
>         , (SELECT  A.IW_VV_DE
>           FROM     XXXX.VALID_VALUES A
>           WHERE    A.IW_BV_BUS_NM = 'PAYMENT TRANSACTION'
>             AND    A.IW_BE_BUS_NM = 'PMT SUB CODE'
>             AND    A.IW_BE_SYS_NM = 'PMT_SUB_CD'
>             AND    A.CLIENT_NO = B.CLIENT_NO
>             AND    A.IW_UV_CD = B.PMT_SUB_CD
>           )  AS PMT_SUB_CODE_DESCRIPTION
>         , PMT_SUSPENSE_AM AS "PMT_SUSPENSE_AMOUNT"
>         , PMT_AM AS "PMT_TOTAL_AMOUNT"
>         , PMT_TRANS_CD AS "PMT_TRANSACTION_CODE"
>         , PMT_DUE_DT AS "PMT_DUE_DATE"
>         , PMT_DUE_NULL AS "PMT_DUE_DT_ERR_FLG"
>         , PMT_EFFECTIVE_DT AS "PMT_EFFECTIVE_DATE"
>         , PMT_EFFECTIVE_NULL AS "PMT_EFFECTIVE_DT_ERR_FLG"
>         , PMT_HI_TY AS "PMT_HI_TYPE"
>         , (SELECT  A.IW_VV_DE
>           FROM     XXXX.VALID_VALUES A
>           WHERE    A.IW_BV_BUS_NM = 'PAYMENT TRANSACTION'
>             AND    A.IW_BE_BUS_NM = 'PMT HI TYPE'
>             AND    A.IW_BE_SYS_NM = 'PMT_HI_TY'
>             AND    A.CLIENT_NO = B.CLIENT_NO
>             AND    A.IW_UV_CD = B.PMT_HI_TY
>           )  AS PMT_HI_TYPE_DESCRIPTION
>         , PMT_MPL_PLAN_ID AS "PMT_MODIFIED_PAYMENT_PLAN_ID"
>         , CLIENT_NO AS "CLIENT_NUMBER"
>         , AUTO_ADJUST_IND_CD AS "PMT_AUTO_ADJUSTMENT_INDICATOR"
>         , PMT_PREV_POSTED_AM AS "PMT_PREVIOUSLY_POSTED_AMOUNT"
>         , PMT_INT_DUE_PD_AM AS "PMT_INTEREST_DUE_PAID_AMOUNT"
>         , PMT_REVERSL_RSN_CD AS "PMT_REVERSAL_REASON_CODE"
>         , PMT_DIST_TY AS "PMT_DISTRIBUTION_TYPE"
>         , PMT_SUBSIDY_SRC_CD AS "PMT_SUBSIDY_SOURCE_CODE"
>         , PMT_INCN_PROC_CD AS "PMT_INCENTIVE_PROCESS_CODE"
>         , (SELECT  A.IW_VV_DE
>           FROM     XXXX.VALID_VALUES A
>           WHERE    A.IW_BV_BUS_NM = 'PAYMENT TRANSACTION'
>             AND    A.IW_BE_BUS_NM = 'PMT INCENTIVE PROCESS CODE'
>             AND    A.IW_BE_SYS_NM = 'PMT_INCN_PROC_CD'
>             AND    A.CLIENT_NO = B.CLIENT_NO
>             AND    A.IW_UV_CD = B.PMT_INCN_PROC_CD
>           )  AS PMT_INCENTIVE_PROCESS_CD_DESC
>         , PMT_PRN_ADJ_RSN_CD AS "PMT_PRIN_ADJ_REASON_CODE"
>         , PMT_PRIN_REDCTN_AM AS "PMT_PRIN_REDUCTION_AMOUNT"
>         , PMT_PRIN_FORB_AM AS "PMT_PRIN_FORBEARANCE_AMOUNT"
>         , LST_MNT_TMS AS "LAST_MAINTENANCE_TIMESTAMP"
>  FROM     XXXX.PAYMENT_TRAN B
>  WHERE    DELTA_FILE_BYTE IN('A', 'C')
>
>
> My attempt to re-write
>
>  SELECT   X.LN_NO AS "LOAN_NUMBER"
>         , X.PMT_TRANS_DT AS "PMT_TRANSACTION_DATE"
>         , X.PMT_SEQ_NO AS "PMT_SEQUENCE_NUMBER"
>         , X.PMT_AH_INS_AM AS "PMT_A_H_INSURANCE_AMOUNT"
>         , X.PMT_ACTION_CD AS "PMT_ACTION_CODE"
>         , X.PMT_BATCH_NO AS "PMT_BATCH_NUMBER"
>         , X.PMT_ESCROW_AM AS "PMT_ESCROW_AMOUNT"
>         , X.PMT_FEE_AM AS "PMT_FEE_AMOUNT"
>         , X.PMT_FEE_CD AS "PMT_FEE_CODE"
>         , Y.PMT_FEE_CODE_DESCRIPTION
>         , X.PMT_HUD_AM AS "PMT_HUD_AMOUNT"
>         , X.PMT_INTEREST_AM AS "PMT_INTEREST_AMOUNT"
>         , X.PMT_LIFE_INS_AM AS "PMT_LIFE_INSURANCE_AMOUNT"
>         , X.PMT_MISC_AM AS "PMT_MISC_AMOUNT"
>         , X.PMT_PRINCIPAL_AM AS "PMT_PRINCIPAL_AMOUNT"
>         , X.PMT_REPL_RESV_AM AS "PMT_REPLACEMENT_RES"
>         , X.PMT_RES_ESC_AM AS "PMT_RESTRICTED_ESC_AMOUNT"
>         , X.PMT_SVC_CHRG_AM AS "PMT_SERVICE_CHARGE_AMOUNT"
>         , X.PMT_SERV_FEE_AM AS "PMT_SERVICE_FEE_AMOUNT"
>         , X.PMT_SUB_CD AS "PMT_SUB_CODE"
>         , Y.PMT_SUB_CODE_DESCRIPTION
>         , X.PMT_SUSPENSE_AM AS "PMT_SUSPENSE_AMOUNT"
>         , X.PMT_AM AS "PMT_TOTAL_AMOUNT"
>         , X.PMT_TRANS_CD AS "PMT_TRANSACTION_CODE"
>         , X.PMT_DUE_DT AS "PMT_DUE_DATE"
>         , X.PMT_DUE_NULL AS "PMT_DUE_DT_ERR_FLG"
>         , X.PMT_EFFECTIVE_DT AS "PMT_EFFECTIVE_DATE"
>         , X.PMT_EFFECTIVE_NULL AS "PMT_EFFECTIVE_DT_ERR_FLG"
>         , X.PMT_HI_TY AS "PMT_HI_TYPE"
>         , Y.PMT_HI_TYPE_DESCRIPTION
>         , X.PMT_MPL_PLAN_ID AS "PMT_MODIFIED_PAYMENT_PLAN_ID"
>         , X.CLIENT_NO AS "CLIENT_NUMBER"
>         , X.AUTO_ADJUST_IND_CD AS "PMT_AUTO_ADJUSTMENT_INDICATOR"
>         , X.PMT_PREV_POSTED_AM AS "PMT_PREVIOUSLY_POSTED_AMOUNT"
>         , X.PMT_INT_DUE_PD_AM AS "PMT_INTEREST_DUE_PAID_AMOUNT"
>         , X.PMT_REVERSL_RSN_CD AS "PMT_REVERSAL_REASON_CODE"
>         , X.PMT_DIST_TY AS "PMT_DISTRIBUTION_TYPE"
>         , X.PMT_SUBSIDY_SRC_CD AS "PMT_SUBSIDY_SOURCE_CODE"
>         , X.PMT_INCN_PROC_CD AS "PMT_INCENTIVE_PROCESS_CODE"
>         , Y.PMT_INCENTIVE_PROCESS_CD_DESC
>         , X.PMT_PRN_ADJ_RSN_CD AS "PMT_PRIN_ADJ_REASON_CODE"
>         , X.PMT_PRIN_REDCTN_AM AS "PMT_PRIN_REDUCTION_AMOUNT"
>         , X.PMT_PRIN_FORB_AM AS "PMT_PRIN_FORBEARANCE_AMOUNT"
>         , X.LST_MNT_TMS AS "LAST_MAINTENANCE_TIMESTAMP"
>  FROM     XXXX.PAYMENT_TRAN X,
>           (SELECT  A.CLIENT_NO
>                  , A.PMT_FEE_CODE_DESCRIPTION
>                  , B.PMT_SUB_CODE_DESCRIPTION
>                  , C.PMT_HI_TYPE_DESCRIPTION
>                  , D.PMT_INCENTIVE_PROCESS_CD_DESC
>           FROM     (SELECT  CLIENT_NO
>                           , IW_VV_DE AS PMT_FEE_CODE_DESCRIPTION
>                    FROM     XXXX.VALID_VALUES
>                    WHERE    IW_BV_BUS_NM = 'PAYMENT TRANSACTION'
>                      AND    IW_BE_BUS_NM = 'PMT FEE CODE'
>                      AND    IW_BE_SYS_NM = 'PMT_FEE_CD'
>                    )  A,
>                    (SELECT  CLIENT_NO
>                           , IW_VV_DE AS PMT_SUB_CODE_DESCRIPTION
>                    FROM     XXXX.VALID_VALUES
>                    WHERE    IW_BV_BUS_NM = 'PAYMENT TRANSACTION'
>                      AND    IW_BE_BUS_NM = 'PMT SUB CODE'
>                      AND    IW_BE_SYS_NM = 'PMT_SUB_CD'
>                    )  B,
>                    (SELECT  CLIENT_NO
>                           , IW_VV_DE AS PMT_HI_TYPE_DESCRIPTION
>                    FROM     XXXX.VALID_VALUES
>                    WHERE    IW_BV_BUS_NM = 'PAYMENT TRANSACTION'
>                      AND    IW_BE_BUS_NM = 'PMT HI TYPE'
>                      AND    IW_BE_SYS_NM = 'PMT_HI_TY'
>                    )  C,
>                    (SELECT  CLIENT_NO
>                           , IW_VV_DE AS PMT_INCENTIVE_PROCESS_CD_DESC
>                    FROM     XXXX.VALID_VALUES
>                    WHERE    IW_BV_BUS_NM = 'PAYMENT TRANSACTION'
>                      AND    IW_BE_BUS_NM = 'PMT INCENTIVE PROCESS CODE'
>                      AND    IW_BE_SYS_NM = 'PMT_INCN_PROC_CD'
>                    )  D
>           WHERE    A.CLIENT_NO = B.CLIENT_NO
>             AND    A.CLIENT_NO = C.CLIENT_NO
>             AND    A.CLIENT_NO = D.CLIENT_NO
>           )  Y
>  WHERE    X.CLIENT_NO = Y.CLIENT_NO
>  AND      X.DELTA_FILE_BYTE IN  ('A', 'C')
>