3 Replies Latest reply: Dec 11, 2012 12:01 PM by jihuyao RSS

    Please help me on tune this query

    976208

      Hi Experts,

      Please hrlp me to tune this query.

        • 1. Re: Please help me on tune this query
          BluShadow
          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
            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
              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
              )