-
How to Fine Tune this Query
Hi ,
I tried several ways to bring this Query performance time from 80 sec to 3 sec by way of UNION followed in the second Query still i couldnt include the
OR
(BUSINESS_UNIT = BUSINESS_UNIT_IU
Due to which i miss the actuall records to be returned.Can anybody suggest a good idea of how to rewrite this Query.
Original Query
===============
SELECT BUSINESS_UNIT
,JOURNAL_ID
,JOURNAL_DATE
,UNPOST_SEQ
,JRNL_HDR_STATUS
,FISCAL_YEAR
,ACCOUNTING_PERIOD
,LEDGER_GROUP
,SOURCE
,TRANS_REF_NUM
,POSTED_DATE
,JRNL_TOTAL_LINES
,JRNL_TOTAL_DEBITS
,JRNL_NET_UNITS
,CURRENCY_CD
,DESCR
FROM PS_JRNL_HEADER A
WHERE
(JRNL_HDR_STATUS IN ('N','E','V') OR
(BUSINESS_UNIT = BUSINESS_UNIT_IU
AND EXISTS (
SELECT 'X'
FROM PS_JRNL_HEADER
WHERE BUSINESS_UNIT_IU = A.BUSINESS_UNIT_IU
AND JOURNAL_ID=A.JOURNAL_ID
AND JOURNAL_DATE=A.JOURNAL_DATE
AND UNPOST_SEQ=A.UNPOST_SEQ
AND JRNL_HDR_STATUS IN ('N','E','V'))))
AND 'JRNL_LN'=(
SELECT T.RECNAME_JRNL_LN
FROM PS_LED_TMPLT_TBL T
, PS_LED_GRP_TBL D
, PS_SET_CNTRL_REC E
WHERE T.LEDGER_TEMPLATE=D.LEDGER_TEMPLATE
AND D.LEDGER_GROUP = A.LEDGER_GROUP
AND D.SETID=E.SETID
AND E.SETCNTRLVALUE=A.BUSINESS_UNIT
AND E.RECNAME = 'LED_GRP_TBL');
Modified Query
==============
SELECT BUSINESS_UNIT
,JOURNAL_ID
,JOURNAL_DATE
,UNPOST_SEQ
,JRNL_HDR_STATUS JRNL_IU_HDR_STATUS
,FISCAL_YEAR
,ACCOUNTING_PERIOD
,LEDGER_GROUP
,SOURCE
,TRANS_REF_NUM
,POSTED_DATE
,JRNL_TOTAL_LINES
,JRNL_TOTAL_DEBITS
,JRNL_NET_UNITS
,CURRENCY_CD
,DESCR
FROM PS_JRNL_HEADER
WHERE JRNL_HDR_STATUS IN ('N','E','V')
UNION
SELECT BUSINESS_UNIT
,JOURNAL_ID
,JOURNAL_DATE
,UNPOST_SEQ
,JRNL_HDR_STATUS JRNL_IU_HDR_STATUS
,FISCAL_YEAR
,ACCOUNTING_PERIOD
,LEDGER_GROUP
,SOURCE
,TRANS_REF_NUM
,POSTED_DATE
,JRNL_TOTAL_LINES
,JRNL_TOTAL_DEBITS
,JRNL_NET_UNITS
,CURRENCY_CD
,DESCR
FROM PS_JRNL_HEADER A
WHERE JRNL_HDR_STATUS IN ('N','E','V')
AND 'JRNL_LN'=(
SELECT T.RECNAME_JRNL_LN
FROM PS_LED_TMPLT_TBL T , PS_LED_GRP_TBL D , PS_SET_CNTRL_REC E
WHERE
T.LEDGER_TEMPLATE= D.LEDGER_TEMPLATE
AND D.SETID=E.SETID
AND D.LEDGER_GROUP = A.LEDGER_GROUP
AND E.SETCNTRLVALUE=A.BUSINESS_UNIT
AND E.RECNAME = 'LED_GRP_TBL');
07:39:04 SQL> select count(*) from PS_JRNL_HEADER;
COUNT(*)
----------
1374068
Also this is a Corelated Sub Query
Options other then Like Parallel Query or DOP(Degree of Parallelism) is welcome.
Advance thanks for anyone who can give a solution for this Problem.
Regds
Sambath
[Edited by krishsam on 05-08-2002 at 08:16 AM]
-
Re: How to Fine Tune this Query
Hi ,
Can anyone help me
Regds
Sambath
-
Hi,
I can't see much problems in queries.
Can you send EXPLAIN_PLAN of both the queries?
Also, did you check the buffers gets in both queries such as physical reads, logical buffers, consistent gets etc.
Thanks,
Sam
------------------------
To handle yourself, use your head. To handle others, use your heart
-
Re: How to Fine Tune this Query
Hi ,
I am Unable to see the message except my first message.
Regds
Sambath
-
Re: Re: How to Fine Tune this Query
Hi ,
AS PER ORIGINAL QUERY
=====================
OPERATION OPTIONS OBJECT_NAME COST
-------------------- ------------------------- -------------------- ----------
SELECT STATEMENT 3024
FILTER
TABLE ACCESS FULL PS_JRNL_HEADER 3024
INLIST ITERATOR
INDEX RANGE SCAN PSHJRNL_HEADER 2
NESTED LOOPS 3
NESTED LOOPS 2
TABLE ACCESS FULL PS_LED_GRP_TBL 1
TABLE ACCESS BY INDEX ROWID PS_LED_TMPLT_TBL 1
INDEX UNIQUE SCAN PS_LED_TMPLT_TBL
INDEX UNIQUE SCAN PSASET_CNTRL_REC 1
11 rows selected.
The Query rewrite as UNION cost even though involved is high it executes in 2 seconds.What my Question is How to include the OR cluase also along with UNION Operator as work around.
Regds
Sambath
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|