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, and XML.
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.
SELECT rowid FROM sys.icol$ INTERSECT SELECT rowid FROM sys.ind$;
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.
SELECT * FROM departments WHERE rownum < 10;
ROWNUM can be used to limit the number of rows returned by a query.
However, be aware that an ORDER BY will reorder the columns.
SELECT department_name, rownum FROM departments; SELECT department_name, rownum FROM departments ORDER BY department_name; SELECT department_name, rownum FROM departments WHERE rownum < 10 ORDER BY department_name;
The first two queries show the results of a SELECT without and then with an ORDER BY clause without a ROWNUM reference in the WHERE clause. Note the first few rows returned in each query. As expected the sort order is different. However, notice that the values in the ROWNUM column are consistent.
The third query limits by ROWNUM, which the RDBMS processes prior to the ORDER BY. You will see that, although the rows returned are in order by department_name, they are not the first ten rows from the previous query. They are, however, the rows with the ROWNUM values 1-10. Also notice that these are consistent values with the first two queries.
SELECT * FROM ( SELECT * FROM departments ORDER BY department_name ) WHERE rownum < 10;
You can force the order by to follow the rownum limitation by using a subquery. Subqueries will be covered in an upcoming chapter on using subqueries. For now, simply be aware that there is a way to cause rownums to be evaluated after an ORDER BY clause. Also, bear in mind that this query will retrieve all of the data from the table listed in the inner query because it is selecting all columns from all rows. This might result in a significant performance issue. In class where you are the only person connected to the database, using SELECT * is acceptable. However when in a production environment, limit its use due to the performance issue.
ROWNUM can be used to assign unique values to each row in a table when using an UPDATE statement.
CREATE TABLE mytable AS SELECT * FROM departments; ALTER TABLE mytable ADD test_col number; UPDATE mytable SET test_col=rownum; SELECT * FROM mytable;
In this example, we first create a table with the structure and content of the departments table. Next, we add an additional column to the table that we will be populating. Both of these actions will be described in greater detail in an upcoming lesson on Data Definition Language (DDL).
Now an update statement is run. We can verify the results by running a simple SELECT query on the table and see that a different number was assigned to the new column in each row.
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.