Why the huge unique sort?
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 25 Jul 2006 17:23:02 -0700
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts: