A pseudo-column is not an actual column in a table.
It is like a function in that it returns a value when selected. Pseudocolumns
exist that provide functionality related to hierarchical queries,
sequences (CURRVAL and NEXTVAL), flashback queries, system generated identifiers,
A Rowid provide quick access to a row.
It is a unique identifier for a row within a cluster (not necessarily the entire database).
It is the fastest possible access to a row of a given table.
To retrieve a Rowid use the ROWID pseudocolumn in the SELECT clause.
To access a row using Rowid use the ROWID pseudocolumn in the WHERE clause.
A Rowid is not necessarily unique throughout the database.
PLEASE NOTE: The following example involves the use of a cluster (an object that contains data from one
or more tables). Use of clusters is an advanced topic related to performance tuning. It is only introduced here to
demonstrate that there is not a guarantee that a rownum will be unique throughout the entire database.
Often a Rowid value uniquely identifies a row for the entire database.
However, rows in different tables and stored in the same cluster might have duplicate Rowids.
In the following example, a DBA may query a data dictionary table (which holds metadata about the database itself).
A number of tables are returned that share the same cluster name (C_OBJ#).
SELECT cluster_name, table_name
FROM dba_tables WHERE cluster_name='C_OBJ#';
A DBA could now query two of the objects and show the resulting Rowids that appear in both clusters.
Remember, although Rowids are often unique, they are not guaranteed to be unique in the entire
database - only a given cluster.
The ROWNUM pseudocolumn returns a number indicating the order in which
Oracle selects the row result of a query. It can be used to limit the results
returned from a query. Unlike ROWID, which is associated with a row in a table,
a ROWNUM is assigned when a number is retrieved into memory. The ROWNUM assigned
remains with the row when an ORDER BY is used.
However, be aware that an ORDER BY will reorder the columns.
ROWNUM can be used to assign unique values to each row in a table when using an UPDATE statement.
Though ROWNUM is commonly used to limit results, be aware of the
behavior related to ordering mentioned earlier. One common misuse
of ROWNUM is to limit data returned to a single row. If not correctly
specified, an arbitrary or incorrect row may be returned when the
desired result is the first row based upon a specified order.
The ROW_NUMBER function can be used to assign numbers in this manner as well.