What is ROWID Pseudocolumns ::
For
each row in the database, the ROWID pseudocolumn returns the address of the
row. Oracle Database rowid values contain information necessary to locate a
row:
- The
data object number of the object
- The
data block in the datafile in which the row resides
- The
position of the row in the data block (first row is 0)
- The
datafile in which the row resides (first file is 1). The file number is
relative to the tablespace.
Usually,
a rowid value uniquely identifies a row in the database. However, rows in
different tables that are stored together in the same cluster can have the same
rowid.
Values
of the ROWID pseudocolumn have the datatype ROWID or UROWID. Please refer to "ROWID
Datatype" and "UROWID
Datatype"for more information.
Rowid
values have several important uses:
- They
are the fastest way to access a single row.
- They
can show you how the rows in a table are stored.
- They
are unique identifiers for rows in a table.
You
should not use ROWID as the primary key of a table. If you
delete and reinsert a row with the Import and Export utilities, for example,
then its rowid may change. If you delete a row, then Oracle may reassign its
rowid to a new row inserted later.
Although
you can use the ROWID pseudocolumn in the SELECT and WHERE clause of a
query, these pseudocolumn values are not actually stored in the database. You
cannot insert, update, or delete a value of the ROWID pseudocolumn.
Don't use ROW_ID with MIN and MAX as MAX(ROWID) won't always return me last inserted row because
that row should not have the biggest
ROWID value.
The value of ROW_ID is
not in sequence but it's only unique value.
What is ROWNUM Pseudocolumns ::
For
each row returned by a query, the ROWNUM pseudocolumn returns a number indicating
the order in which Oracle selects the row from a table or set of joined rows.
The first row selected has a ROWNUM of 1, the second has 2, and so on.
You
can use ROWNUM to limit the number of rows returned by a query, as in
this example:
SELECT * FROM employees
WHERE ROWNUM < 10;
If
an ORDER BY clause follows ROWNUM in the same
query, then the rows will be reordered by the ORDER BY clause. The
results can vary depending on the way the rows are accessed. For example, if
the ORDER BY clause causes Oracle to use an index to
access the data, then Oracle may retrieve the rows in a different order than
without the index. Therefore, the following statement will not have the same
effect as the preceding example:
SELECT * FROM employees
WHERE ROWNUM < 11 ORDER BY last_name;
If
you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the
top-level query, then you can force theROWNUM condition to be applied after the
ordering of the rows. For example, the following query returns the employees
with the 10 smallest employee numbers. This is sometimes referred to as top-N reporting:
SELECT * FROM
(SELECT * FROM employees ORDER BY
employee_id)
WHERE ROWNUM < 11;
In
the preceding example, the ROWNUM values are those of the top-level SELECT statement, so
they are generated after the rows have already been ordered by employee_id in
the subquery.
Conditions
testing for ROWNUM values greater than a positive integer are always false.
For example, this query returns no rows:
SELECT * FROM employees
WHERE ROWNUM > 1;
The
first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second
row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes
the condition false. All rows subsequently fail to satisfy the condition, so no
rows are returned.
You
can also use ROWNUM to assign unique values to each row of a table, as in this
example:
UPDATE my_table
SET column1 = ROWNUM;
Please
refer to the function ROW_NUMBER for
an alternative method of assigning unique numbers to rows.
