467,154 Members | 1,225 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,154 developers. It's quick & easy.

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

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
  • viewed: 1013
Share:

Post your reply

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

Similar topics

28 posts views Thread by stu_gots | last post: by
6 posts views Thread by Steven D.Arnold | last post: by
3 posts views Thread by gilgantic | last post: by
5 posts views Thread by Steve Patrick | last post: by
11 posts views Thread by =?Utf-8?B?cmtibmFpcg==?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.