This discussion is archived
3 Replies Latest reply: Dec 11, 2012 10:01 AM by jihuyao RSS

Please help me on tune this query

976208 Explorer
Currently Being Moderated

Hi Experts,

Please hrlp me to tune this query.

  • 1. Re: Please help me on tune this query
    BluShadow Guru Moderator
    Currently Being Moderated
    You seem to have a few inline queries in your select statement that are doing aggregate functions on tables that are quite large... so potentially doing that aggregation again and again for each row retrieved.

    It may be better to factor out those inline queries and make them joins instead or use subquery factoring (WITH clause).
  • 2. Re: Please help me on tune this query
    BluShadow Guru Moderator
    Currently Being Moderated
    While I'm looking:
    80                         AND TRUNC (FH.DATE_RECEIVED) >='01-JAN-2010'
    81                         AND TRUNC (FH.DATE_RECEIVED) <='04-DEC-2012'
    Are you aware that you're comparing your dates as strings using range comparisons?
    SQL> select 'Wrong' from dual where '05-JAN-2010' > '01-DEC-2012';
    
    'WRON
    -----
    Wrong
    You should always treat dates as dates...
    AND TRUNC (FH.DATE_RECEIVED) >=to_date('01-JAN-2010','DD-MON-YYYY')
    AND TRUNC (FH.DATE_RECEIVED) <=to_date('04-DEC-2012','DD-MON-YYYY')
  • 3. Re: Please help me on tune this query
    jihuyao Journeyer
    Currently Being Moderated
    Return only 20 rows from the inner joins, and then do the left outer joins
    with FH as (
    select * from (
    SELECT FROM ORL.FAX_HEADER FH
    INNER JOIN ORL.WORKGROUP WG
    ON (WG.ID = FH.WORKGROUP_ID)
    INNER JOIN APPS_GLOBAL.GLOBAL_BU_MAPPING GBM
    ON (GBM.BU_ID = WG.BUID AND WG.BUID = 3535)
    WHERE FH.WORKGROUP_ID = 245
    AND TRUNC (FH.DATE_RECEIVED) >='01-JAN-2010'
    AND TRUNC (FH.DATE_RECEIVED) <='04-DEC-2012'
    order by FH.ID
    ) where rownum<21
    )

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points