No. In Oracle DBMS, ROWNUM and ROWID are pseudocolumns that can be accessed in SQL queries, but they aren’t the same column… Let’s take a look at each of those columns and learn how to use them.
ROWNUM and query processing
In Oracle, ROWNUM is a pseudocolumn that contains numbers that are assigned to rows during SQL execution. Understanding when and how the DBMS assigns such valuesis crucial in order to correctly use ROWNUM.
During query processing, Oracle processes the From and Where clauses. Then, it assigns a number (i.e. ROWNUM) for each fetched row in the temporary resultset. The number 1 is assigned for the first fetched row. The number 2 for the second and so on. After assigning ROWNUM, Oracle processes Select, Group by, Having and Order by clauses.
It should be noticed that ROWNUM values may be ordered in a distinct order from the one specified in Order by. This happens as ROWNUM assignment is processed before Order by processing.
In the following query, resultset rows are ordered by the name column and ROWNUM values are not ordered by such column.
If one intends to make ROWNUM assignment according to the values of a certain column (or of a set columns specified in an Order by clause), then he/she should use a nested query. The desired sorting columns and clauses should be specified in an Order by clause used in the nested query and ROWNUM should be used in the outer query. Oracle processes the nested query and uses its ordered results to assign ROWNUM in the outer query.
When executing the following query, the DBMS would assign ROWNUM values according to the values in the Name column.
ROWNUM and Top-N queries
ROWNUM can be used to limit the results of a SQL query, enabling the execution of top-N queries. For instance, in following query, ROWNUM is used to limit the query resultset in 10 rows. The DBMS sorts the rows in the nested table (i.e. inner query) according to values in the Salary column (in decrescent order) and uses such sorted rows in the outer query. Then, the SQL command returns 10 rows corresponding the top salaries.
ROWID: the address of a row
In Oracle, the ROWID pseudocolumn returns the address of a row. Such address contains all the information used by Oracle to locate the row, which includes an identification of the datafile and of the datablock in which the row is stored, and the position of the row in the datablock.
In the following, we present a query to list the ROWID of each row from the Customers table.
