Re: Why the monstrous SORT?

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_at_OneNeck.com> To: <oracle-l_at_freelists.org> 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