This content has been marked as final. Show 3 replies
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).
While I'm looking:
Are you aware that you're comparing your dates as strings using range comparisons?
80 AND TRUNC (FH.DATE_RECEIVED) >='01-JAN-2010' 81 AND TRUNC (FH.DATE_RECEIVED) <='04-DEC-2012'
You should always treat dates as dates...
SQL> select 'Wrong' from dual where '05-JAN-2010' > '01-DEC-2012'; 'WRON ----- Wrong
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')
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 )