How to Fine Tune This SQL Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to Fine Tune This SQL Query

  1. #1
    Join Date
    May 2002
    Posts
    4

    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]

  2. #2
    Join Date
    May 2002
    Posts
    4

    Re: How to Fine Tune this Query

    Hi ,

    Can anyone help me

    Regds

    Sambath

  3. #3
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    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

  4. #4
    Join Date
    May 2002
    Posts
    4

    Re: How to Fine Tune this Query

    Hi ,

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

    Regds

    Sambath


  5. #5
    Join Date
    May 2002
    Posts
    4

    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
  •  


Click Here to Expand Forum to Full Width