Why the huge unique sort?

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Tue, 25 Jul 2006 17:23:02 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C45059E1BBD@NT15.oneneck.corp>

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 Received on Tue Jul 25 2006 - 19:23:02 CDT