RE: sort aggregate vs sort order by

Jack,

I assume you are asking this question in reference to the access path, or explain plan, for a query.

Sort (Aggregate) -- Query returns a single row using a summary function but does not include a GROUP BY clause.
Sort (Order By) -- Query includes an ORDER BY clause. Sort (Group By) -- Query includes a GROUP BY clause.

In the 8i documentation, this info can be found in Table 5-4 in the "Designing and Tuning for Performance" manual. FWIW, though mentioned elsewhere in the same manual, Table 5-4 doesn't mention the SORT (GROUP BY NO SORT) -- ordered data is being fed into the GROUP BY step negating the need for the sort operation that is needed for a GROUP BY. Can occur under certain conditions -- sometimes when index access supporting the ordering is used, after a sort merge, etc.

I attached some examples to illustrate.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of
> nlzanen1_at_EY.NL
> Sent: Friday, December 28, 2001 5:00 AM
> To: Multiple recipients of list ORACLE-L
> Subject: sort aggregate vs sort order by
>
>
>
>
> Hi All,
>
>
> I have tried the Oracle Doc's but can't find the answer to what is the
> difference between the two
>
> Anybody can explain??
>
>
> TIA
>
>
>
> Jack
>
> ===================================================================
> De informatie verzonden met dit E-mail bericht is uitsluitend bestemd voor
> de geadresseerde. Gebruik van deze informatie door anderen dan de
> geadresseerde is verboden. Openbaarmaking, vermenigvuldiging, verspreiding
> en/of verstrekking van deze informatie aan derden is niet toegestaan.
> Ernst & Young staat niet in voor de juiste en volledige
> overbrenging van de
> inhoud van een verzonden E-mail, noch voor tijdige ontvangst daarvan.
> ===================================================================
> The information contained in this communication is confidential and may be
> legally privileged. It is intended solely for the use of the individual or
> entity to whom it is addressed and others authorised to receive it. If you
> are not the intended recipient you are hereby notified that any
> disclosure,
> copying, distribution or taking any action in reliance on the contents of
> this information is strictly prohibited and may be unlawful. Ernst &
> Young is neither liable for the proper and complete transmission of the
> information contained in this communication nor for any delay in its
> receipt.
> ===================================================================
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: nlzanen1_at_EY.NL
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

Examples:

SQL> select min(sal) from emp;

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)    1 0 SORT (AGGREGATE)
   2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=8 Bytes=16)

SQL> select ename from emp order by ename;

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes=40)    1 0 SORT (ORDER BY) (Cost=3 Card=8 Bytes=40)    2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=8 Bytes=40)

SQL> select min(sal) from emp group by deptno;

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=2 Bytes=8)    1 0 SORT (GROUP BY) (Cost=3 Card=2 Bytes=8)    2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=8 Bytes=32)

  1 select foo_date, count(*)
  2 from code_master
  3 where foo_date < sysdate - 1000
  4* group by foo_date -- foo_date is indexed

SQL> / Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=5000 Bytes=35000)    1 0 SORT (GROUP BY NOSORT) (Cost=4 Card=5000 Bytes=35000)    2 1 INDEX (RANGE SCAN) OF 'CM_FD_IDX' (NON-UNIQUE) (Cost=4 Card=5000 Bytes=35000)

  1 select foo_date
  2 from code_master
  3 where foo_date < sysdate - 1000
  4* order by foo_date -- foo_date is indexed

SQL> / Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=5000 Bytes=35000)    1 0 INDEX (RANGE SCAN) OF 'CM_FD_IDX' (NON-UNIQUE) (Cost=4 Card=5000 Bytes=35000) Received on Fri Dec 28 2001 - 07:56:11 CST