Re: Why the huge unique sort?

From: amit poddar <amit.poddar_at_yale.edu>
Date: Tue, 25 Jul 2006 20:51:38 -0400
Message-ID: <44C6BC9A.5060906@yale.edu>

So it looks like the view hm_project_cost_invoice is being instantiated 28977 times inside the nested loop marked as bold.

It would perform better if the Nested loops marked was an hash join

 28977 * 20106 = *582591456*

     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

amit

Allen, Brandon wrote:
> 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

>
>
>

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

Received on Tue Jul 25 2006 - 19:51:38 CDT