How to Use RowID and Rownum
A "pseudocolumn" is an expression that you can reference on an SQL statement. Two important pseudocolumns are
To learn how to use pseudocolumns, follow these steps:
- You'll need to download and install Oracle 12c. The instructions for the setup can be found in How to use sample schemas. Follow steps 1 through 4 in this topic before proceeding to the next step.
rowidpseudocolumn contains data that Oracle is able to use to fetch a row directly from a database file. For example, here is the rowid of the IT Helpdesk department in the
- Now let's use the
rowidvalue we just retrieved to fetch the the IT Helpdesk department:
This technique might be useful in an application program. It saves time retrieving a row.
Departmentstable in the HR schema contains 27 departments. Let's imagine we only want to show the first 10 departments. The
rownumpseudocolumn can help us out. Here is the query to show the first 10 departments:
Each department that is retrieved is assigned a row number. The first department selected ("Administration") is assigned a row number value of 1, the next department ("Marketing") is assigned a row number value of 2 and so on. The select statement stops displaying departments after the 10th department has been retrieved. Unlike the
rowidpseudocolumn data, the
rownumdata is not stored on the database.