I see you've printed the 'execution plan' lines from
the tkprof output - can you confirm that the
'row source operation' lines show exactly
the same plan. If they differ, this is the one
that is telling lies.


Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated June 22nd 2005






----- Original Message -----
From: "Allen, Brandon" <Brandon.Allen@(protected)>
To: <oracle-l@(protected)>
Sent: Thursday, July 07, 2005 12:29 AM
Subject: Why the monstrous SORT?


Hi,

I've got an application query that is taking hours to run. After tracing
and running tkprof, I see the execution plan below. Yes, it's a monster of
a query, but Oracle seems to handle it pretty well except for the huge
amount of rows being returned by the SORT (GROUP BY) step. How can it
possibly have to sort so many rows (1.1 Billion!), when it is only getting
237,018 rows from the previous NESTED LOOP step? Any idea how to prevent or
minimize this sort?

Thanks!
Brandon


Rows   Execution Plan
------- ---------------------------------------------------
   0 SELECT STATEMENT  GOAL: CHOOSE
  11  MINUS
  14   SORT (UNIQUE)


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