Just as your home address
uniquely identifies where you live, an Oracle ROWID uniquely identifies where a
row resides on disk. The information in a ROWID gives Oracle everything
he needs to find your row, the disk number, the cylinder, block and offset into
the block.
The ROWNUM is a
"pseudo-column", a placeholder that you can reference in
SQL*Plus. The ROWNUM can be used to write specialized SQL and tune SQL.
For example, to only
display the first-10 rows, you might apply a ROWNUM filter:
select *
from (
select * from my_view where alert_level=3 order by alert_time desc)
where
rownum<=10;
from (
select * from my_view where alert_level=3 order by alert_time desc)
where
rownum<=10;
In
sum, the difference between ROWNUM and ROWID is that ROWNUM is temporary while
ROWID is permanent. Another difference is that ROWID can be used to fetch
a row, while ROWNUM only has meaning within the context of a single SQL
statement, a way of referencing rows within a fetched result set.
Alternatives to rownum
Using rownum is
very dangerous, especially in the hands of beginners, and there are always
alternatives to using rownum:
- The WITH clause - Another approach to
tuning rownum queries is to separate-out the subquery
using the powerful WITH clause. Another benefit of
separating-out the sorted subquery is that you can easily apply either
a parallel hint or an index hint, if
it's faster to retrieve the rows in pre-sorted order.
- The rank or row_number analytics - You can replace rownum in
top-n queries with analytics functions, using rank() or row_number() instead,
getting the same top-in result, but with much faster response time.
- Optimizer goal hint - In cases where rownum is
used to change the optimizer mode tofirst_rows_n, it may be
possible to negate this effect by using an all_rows hint.
- Index hint - In cases
where rownum is used to force an index in a subquery,
again, deploy an index hint to duplicate the faster execution
plan.
