Re: Newbie-Limiting rows returned in query

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 20 Dec 1999 07:45:33 -0500
Message-ID: <339s5s4scvo8ctlfi1cj996sdhg65l9ol5@4ax.com>

A copy of this was sent to spamfilter_at_rosinowski.de (Jan-Erik Rosinowski) (if that email address didn't require changing) On Mon, 20 Dec 1999 10:27:12 GMT, you wrote:

>>select *
>> from ( select * from T order by name )
>> where rownum <= 10
>>/
>
>in oracle 7
>
>select *
>from ( select * from T group by name )
>where rownum <= 10
>
>does it if one doesnt use any hash-stuff.
>
>ciao, jan
>
>http://www.rsp.de - Software zur effizienten Erstellung und Verwaltung von
>Gutachten, Analysen, Pruefprotokollen etc.

careful with that one -- I don't understand the reference to hash-stuff. It has always been true that group by does not have to sort (and in different character sets you can get truely strange results as group by does a binary sort when it does sort -- not a character set sort).

Here are 2 simple examples of group by not sorting:

SQL> create table emp as select * from scott.emp where rownum <= 5; Table created.

SQL> create index emp_idx on emp(job,ename); Index created.

SQL> select ename, job from emp where ename >chr(0) and job >chr(0)   2 group by ename, job ;

ENAME JOB
---------- ---------

smith      CLERK
jones      MANAGER
allen      SALESMAN
martin     SALESMAN
ward       SALESMAN

group by ename,job != order by ename, job. Here the optimizer used an index to get the answer and realizing the Group by ename, job was the same as group by job, ename -- did it that way. think about what the effect on your application would be if the emp_idx was added AFTER your application went production.

Here is another small example that doesn't even need the where clause on a table. All that happened was someone analyzed the table. All of a sudden, the group by that sorted -- stops sorting!

SQL> create table the_table ( pk1 int,
  2                           pk2 int,
  3                           nk1 int,
  4                           nk2 int,
  5                           primary key(pk1,pk2) )

  6 /
Table created.

SQL> create index the_table_idx on the_table(pk1,nk1,pk2,nk2); Index created.

SQL> insert into the_table values (1, 1, 2, 2 ); 1 row created.

SQL> insert into the_table values (2, 1, 1, 1 ); 1 row created.

SQL> select nk1, nk2, pk1, pk2, count(*) a_count_1   2 from the_table
  3 group by nk1, nk2, pk1, pk2;

       NK1 NK2 PK1 PK2 A_COUNT_1 ---------- ---------- ---------- ---------- ----------

         1          1          2          1          1
         2          2          1          1          1

SQL> analyze table the_table compute statistics; Table analyzed.

SQL> select nk1, nk2, pk1, pk2, count(*) a_count_2   2 from the_table
  3 group by nk1, nk2, pk1, pk2;

       NK1 NK2 PK1 PK2 A_COUNT_2 ---------- ---------- ---------- ---------- ----------

         2          2          1          1          1
         1          1          2          1          1

The use of other features (IOTs, partitions, etc) allow for many other examples as well.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Dec 20 1999 - 06:45:33 CST