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 [EMAIL PROTECTED] 214.954.1781 > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of > [EMAIL PROTECTED] > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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)
