group by does not sort
An user just posted an interresting question today. Why the Group By is crazy?
I summarize his example
SQL> SELECT NUM FROM (select 400 num from dual union select 310 from dual union select 220000 from dual) group by num,null;
NUM
-------
400
220000
310
Well, group by is "sorting", but how? this seems crazy. Oracle use the sort algorythm he wants. He can ascending-sort, descending-sort, hash-sort, or any other internal algorythm.
Here, 220000 is before 310 because it is smaller in bytes.
Have a look
SELECT num,dump(num) FROM (select 400 num from dual union select 310 from dual union select 220000 from dual) group by num,null;
NUM DUMP(NUM)
---------- -------------------------
400 Typ=2 Len=2: 194,5
220000 Typ=2 Len=2: 195,23
310 Typ=2 Len=3: 194,4,11
Well, if you need to sort, use order by and read tom blog
I summarize his example
SQL> SELECT NUM FROM (select 400 num from dual union select 310 from dual union select 220000 from dual) group by num,null;
NUM
-------
400
220000
310
Well, group by is "sorting", but how? this seems crazy. Oracle use the sort algorythm he wants. He can ascending-sort, descending-sort, hash-sort, or any other internal algorythm.
Here, 220000 is before 310 because it is smaller in bytes.
Have a look
SELECT num,dump(num) FROM (select 400 num from dual union select 310 from dual union select 220000 from dual) group by num,null;
NUM DUMP(NUM)
---------- -------------------------
400 Typ=2 Len=2: 194,5
220000 Typ=2 Len=2: 195,23
310 Typ=2 Len=3: 194,4,11
Well, if you need to sort, use order by and read tom blog
0 Comments:
Post a Comment
<< Home