René Nyffenegger's collection of things on the web | |
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
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...
... 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 OracleThis is an on Oracle article. The most current articles of this series can be found here.
|