I've got a query that is taking over an hour to run, with most of the
time being spent on direct path reads and writes due to a massive sort,
but I'm not sure why the sort is so large (582M rows) considering the
step below it in the execution plan appears to be returning only 20K
rows. Oracle version 8.1.7.4. Any ideas?

Rows   Row Source Operation
------- ---------------------------------------------------
 2855 SORT ORDER BY
 2855  NESTED LOOPS
28977   NESTED LOOPS
  141   TABLE ACCESS FULL CT_ADDR_CODE
29116   TABLE ACCESS BY INDEX ROWID OE_LINE
29116    INDEX RANGE SCAN (object id 9532)
 2855   VIEW HM_PROJECT_COST_INVOICE
582591456  SORT UNIQUE
20106    UNION-ALL
 8547     SORT GROUP BY
10228     NESTED LOOPS
10229      NESTED LOOPS
10229       NESTED LOOPS OUTER
10229       TABLE ACCESS FULL PA_HISTORY
10228       TABLE ACCESS BY INDEX ROWID OE_HDR
20456        INDEX UNIQUE SCAN (object id 9518)
20456       TABLE ACCESS BY INDEX ROWID PA_JOB
20456       INDEX UNIQUE SCAN (object id 9667)
10228      TABLE ACCESS BY INDEX ROWID PA_PROJECT_MASTER
20456       INDEX UNIQUE SCAN (object id 9682)
11559     SORT GROUP BY
11562     HASH JOIN
   1      TABLE ACCESS FULL OE_CONTROL
11982      MERGE JOIN OUTER
11983       SORT JOIN
11982       NESTED LOOPS
 1929        TABLE ACCESS BY INDEX ROWID AR_DOC_HDR
 1929         INDEX RANGE SCAN (object id 8895)
11982        TABLE ACCESS BY INDEX ROWID AR_DOC_LINE
13910         INDEX RANGE SCAN (object id 8898)
11982       SORT JOIN
36990       TABLE ACCESS FULL OE_HDR




Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--
http://www.freelists.org/webpage/oracle-l