How to Fine Tune This SQL 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]

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,

Hi ,

I am Unable to see the message except my first message.

Regds

Sambath

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