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

actually, the FROM clause is the same -- the only difference between the two queries is that the second query retrieves one less field

and both queries use the same index, yes?

but since that field is the 4th column in the index, and you're providing a value for it in the first query but not the second, i would expect the first query to run faster than the second

to make it run even faster, provide a value for DRMT_CBAT_CO_ID as well

is that what you're after?

Thanks for your advice.
Both queires different in WHERE clause but uses the same TABLE and same INDEX.

Here my question is Can i improve performance by

1.create another index ?
2.by changing WHERE clause field sequance, based on index key field sequance ? will it improve performance ?

No, definitely not. The order of WHERE predicates does not influence the optimizer's decisions.

Of course.
E.g., an index on (DRMT_DET_PAR_NBR, DRMT_SYS_ID, DRMT_DET_PAR_DTE, DRMT_DR_CR_IND, DRMT_DIST_SETT_DTE, DRMT_DET_AMT) would help both queries.
Also an index on (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) could help, since its use would make the query use just index-only access.
Finally, adding column DRMT_DIST_SETT_DTE to that last index would be useful for both purposes, so it would "certainly" be picked by the optimizer.

On the other hand, I don't expect creating any of these three indexes would be a good idea since "best overall performance" is more complex than just "tune the system for this particular query only".