René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

December 11, 2005: On a table's first five rows

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... 
select name from animals where rownum < 6;
... and I get: 
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.
This can be proved by using explain plan: 
explain plan for 
  select name from animals where rownum < 6;

select * from table(dbms_xplan.display);
The first query uses an index... 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 2932623477

---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     5 |    45 |     1   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY   |              |       |       |            |          |
|   2 |   INDEX FULL SCAN| SYS_C0011133 |    10 |    90 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------
... while the second query .... 
explain plan for 
  select * from animals where rownum < 6;

select * from table(dbms_xplan.display);
does a full table scan: 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 2238517168

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     5 |   110 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |         |       |       |            |          |
|   2 |   TABLE ACCESS FULL| ANIMALS |    10 |   220 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.