Re: group by and sorting

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Wed, 01 Jun 2005 09:27:56 +0200
Message-ID: <d7jo1s$q93$1@news.BelWue.DE>

fitzjarrell_at_cox.net wrote:
>
> Eugene wrote:
>

>>Hi all,
>>Is there a way to "tell" Oracle not to sort when it's doing GROUP BY? I
>>think I saw something like ... group by nosort somewhere.
>>
>>Eugene

>
>
> Oracle doesn't 'sort' by default with group by, and such is noted in
> the documentation:
>
> "The GROUP BY clause groups rows but does not guarantee the order of
> the result set. To order the groupings, use the ORDER BY clause."
>
> If the operation doesn't sort there is no need for a 'nosort' option,
> which is invalid syntax:
>
> SQL> select deptno, count(*) from emp group by deptno nosort;
> select deptno, count(*) from emp group by deptno nosort
> *
> ERROR at line 1:
> ORA-00933: SQL command not properly ended
>
>
> SQL>
>
> I cannot imagine where you might have 'seen' such syntax; certainly not
> with respect to Oracle. And, according to your post, neither do you.
>
>
> David Fitzjarrell
>

Could it be that the OP was talking about this: (Note the SORT (GROUP BY) step in the query plan)

SQL> create table test as select * from all_objects;

Table created.

SQL> set autotrace on

SQL> select owner, count (*) from test group by owner;

<snip>

18 rows selected.

Execution Plan

---

    0 SELECT STATEMENT Optimizer=CHOOSE     1 0 SORT (GROUP BY)
    2 1 TABLE ACCESS (FULL) OF 'TEST' (TABLE) Statistics

---

           1  recursive calls
           0  db block gets
        1052  consistent gets
        1048  physical reads
           0  redo size
         893  bytes sent via SQL*Net to client
         523  bytes received via SQL*Net from client
           3  SQL*Net roundtrips to/from client
           1  sorts (memory)
           0  sorts (disk)
          18  rows processed

I think I read somewhere (but don't remember where) that if Oracle doesn't know that data will be ordered e.g. because the query used an indexed access path, it will do that sort. But beware, the sort does not mean that the data will be sorted, it's just there to build the aggregates. (And at least my tests with 10g didn't confirm this).

But since this all comes from the nebulous past, I'm probably way off the path.

Holger Received on Wed Jun 01 2005 - 02:27:56 CDT