ROWNUM Magic in SQL !!!!!

[Marimuthu Udayakumar]

Dear Once,

I have a table that stores animals along with their average life
spans:

desc animals;

Name Null? Type
------------------------------- -------- -------------
NAME NOT NULL VARCHAR2(15)
LIFE_SPAN_YEARS NUMBER(2)


Now, I want to select the table's first five animals...

NAME
---------------
Bison
Box turtle
Canada goose
Crocodile
Elk


Now, I not only want to select the animal's names, but also their
average life span. Again, I am interested in the first five rows:

select * from animals where rownum < 6;

This time, I get:

NAME LIFE_SPAN_YEARS
--------------- ---------------
Crocodile 50
Whale 40
Lion 10
Canada goose 30
Box turtle 40



Note, this select statement does not return the same animals as the
first one, for example, the lion did not appear in the first result.
This begs the question: why is that? The answer is rather trivial: I
have previously put a primary key on the name column:

alter table animals add primary key (name);


... and when a select is made against the primary key's columns (in
this case: only name), Oracle will get the names from the index rather
than the table itself. This, by the way, is the reason why the names
appear alphabetically sorted in the first query.



Yours,
Marimuthu Udayakumar