Hello,
Thankyou for helping me.
Here we have two quires on same table , but FROM classs differs.
But both quiries uses the same INDEX.I have provided the index details below.Is there any chances to improve performance.
Please help me. Thank you.
STATEMENET :1
---------------------------
SELECT DRMT_DET_PAR_NBR
,DRMT_SYS_ID
,DRMT_DET_PAR_DTE
,DRMT_DR_CR_IND
,DRMT_DET_AMT
,DRMT_ACH_TRAN_CD
,DRMT_DIST_ACCT_NBR
,DRMT_ORIG_ACCT_NBR
INTO :DCLCH-DET-RTN-MTCH.DRMT-DET-PAR-NBR, :DCLCH-DET-RTN-MTCH
.DRMT-SYS-ID, :DCLCH-DET-RTN-MTCH.DRMT-DET-PAR-DTE, :
DCLCH-DET-RTN-MTCH.DRMT-DR-CR-IND, :DCLCH-DET-RTN-MTCH.
DRMT-DET-AMT, :DCLCH-DET-RTN-MTCH.DRMT-ACH-TRAN-CD, :
DCLCH-DET-RTN-MTCH.DRMT-DIST-ACCT-NBR, :
DCLCH-DET-RTN-MTCH.DRMT-ORIG-ACCT-NBR
FROM CH_DET_RTN_MTCH
WHERE DRMT_DET_PAR_NBR = :DCLCH-DET-RTN-MTCH.DRMT-DET-PAR-NBR
AND DRMT_SYS_ID = :DCLCH-DET-RTN-MTCH.DRMT-SYS-ID
AND DRMT_DET_PAR_DTE = :DCLCH-DET-RTN-MTCH.DRMT-DET-PAR-DTE
AND DRMT_DR_CR_IND = :DCLCH-DET-RTN-MTCH.DRMT-DR-CR-IND
AND DRMT_DET_AMT = :DCLCH-DET-RTN-MTCH.DRMT-DET-AMT
AND DRMT_DIST_SETT_DTE >= :DCLCH-DET-RTN-MTCH.
EXPLAIN REPORT :
----------------------------
QB PL MIX QTYPE METH ACC MTCH IX TBNAME IXNAME
1 1 0 SELECT 0 I 3 N CH_DET_RTN_MTCH X1CHDRMT
NU J O G CU J O G LCK
N N N N N N N N IS
STATEMENET :2
---------------------------
SELECT DRMT_DET_PAR_NBR
,DRMT_SYS_ID
,DRMT_DET_PAR_DTE
,DRMT_DR_CR_IND
,DRMT_ACH_TRAN_CD
,DRMT_DIST_ACCT_NBR
,DRMT_ORIG_ACCT_NBR
INTO :DCLCH-DET-RTN-MTCH.DRMT-DET-PAR-NBR, :DCLCH-DET-RTN-MTCH
.DRMT-SYS-ID, :DCLCH-DET-RTN-MTCH.DRMT-DET-PAR-DTE, :
DCLCH-DET-RTN-MTCH.DRMT-DR-CR-IND, :DCLCH-DET-RTN-MTCH.
DRMT-ACH-TRAN-CD, :DCLCH-DET-RTN-MTCH.DRMT-DIST-ACCT-NBR,
:DCLCH-DET-RTN-MTCH.DRMT-ORIG-ACCT-NBR
FROM CH_DET_RTN_MTCH
WHERE DRMT_DET_PAR_NBR = :DCLCH-DET-RTN-MTCH.DRMT-DET-PAR-NBR
AND DRMT_SYS_ID = :DCLCH-DET-RTN-MTCH.DRMT-SYS-ID
AND DRMT_DET_PAR_DTE = :DCLCH-DET-RTN-MTCH.DRMT-DET-PAR-DTE
AND DRMT_DR_CR_IND = :DCLCH-DET-RTN-MTCH.DRMT-DR-CR-IND
AND DRMT_DIST_SETT_DTE >= :DCLCH-DET-RTN-MTCH.
EXPLAIN REPORT :
----------------------------
QB PL MIX QTYPE METH ACC MTCH IX TBNAME IXNAME
1 1 0 SELECT 0 I 3 N CH_DET_RTN_MTCH X1CHDRMT
NU J O G CU J O G LCK
N N N N N N N N IS
Index Key Information for: X1CHDRMT
-------------------------------------------------------
COLUMN NAME COLNO COLSEQ ORDERING
DRMT_SYS_ID 1 1 ASCENDING
DRMT_DET_PAR_NBR 2 2 ASCENDING
DRMT_DIST_SETT_DTE 3 3 ASCENDING
DRMT_DET_AMT 4 4 ASCENDING
DRMT_DR_CR_IND 5 5 ASCENDING
DRMT_CBAT_CO_ID 6 6 ASCENDING
DRMT_DET_PAR_DTE 7 7 ASCENDING
|