By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,677 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

Help me on Query fine tune.Explain report details provided.

P: 3
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
Aug 11 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.