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]