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)
     14     FILTER
    285      SORT (GROUP BY)
   4768       NESTED LOOPS (OUTER)
   4769        NESTED LOOPS (OUTER)
   4769         NESTED LOOPS
   4769          NESTED LOOPS
    285           HASH JOIN
    197            VIEW OF 'AR_SALESPERSON_CODE'
    197             SORT (UNIQUE)
    197              SORT (GROUP BY)
    197               HASH JOIN
    212                TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'CT_TEAM_MEMBER'
   5637                TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'CT_ADD_NAMES'
    284            NESTED LOOPS
      2             MERGE JOIN (CARTESIAN)
      2              TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'AR_CUSTOMER_MASTER'
      2               INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'AR_CUSTOMER_MASTER_PK' (UNIQUE)
      2              SORT (JOIN)
      1               TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'OE_CONTROL'
    284             TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'OE_HDR'
    285              INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'OE_HDR_2' (NON-UNIQUE)
   5052           TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'OE_LINE'
   5052            INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'OE_LINE_PK' (UNIQUE)
   9536          TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'OE_SUB_ENTITY'
   9536           INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'HM_OE_SUB_ENTITY_UK1' (UNIQUE)
   4768         TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'HM_INVOICE_SUB_ENTITY_CONTROL'
   9536          INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'HM_INVOICE_SE_CONTROL_PK' (UNIQUE)
   4334        VIEW
1127322080      SORT (GROUP BY)
 237018          NESTED LOOPS
      2           TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'OE_CONTROL'
 237018           TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'AR_DOC_LINE'
      3    SORT (UNIQUE)
      3     FILTER
      4      SORT (GROUP BY)
      8       NESTED LOOPS (OUTER)
      9        NESTED LOOPS (OUTER)
      9         NESTED LOOPS
      9          NESTED LOOPS
      4           HASH JOIN
      3            NESTED LOOPS
      2             MERGE JOIN (CARTESIAN)
      2              TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'AR_CUSTOMER_MASTER'
      2               INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'AR_CUSTOMER_MASTER_PK' (UNIQUE)
      2              SORT (JOIN)
      1               TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'OE_CONTROL'
      3             TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'OE_HDR'
    285              INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'OE_HDR_2' (NON-UNIQUE)
    197            VIEW OF 'AR_SALESPERSON_CODE'
    197             SORT (UNIQUE)
    197              SORT (GROUP BY)
    197               HASH JOIN
    212                TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'CT_TEAM_MEMBER'
   5637                TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'CT_ADD_NAMES'
     11           TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID)
                      OF 'OE_LINE'
     11            INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'OE_LINE_PK' (UNIQUE)
     16          TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'OE_SUB_ENTITY'
     16           INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'HM_OE_SUB_ENTITY_UK1' (UNIQUE)
      8         TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'HM_INVOICE_SUB_ENTITY_CONTROL'
     16          INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'HM_INVOICE_SE_CONTROL_PK' (UNIQUE)
      0        VIEW
1891480         SORT (GROUP BY)
 237018          NESTED LOOPS
      2           TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'OE_CONTROL'
 237018           TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'AR_DOC_LINE'

********************************************************************************

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 Wed Jul 06 2005 - 18:30:35 CDT