How to Use RowID and Rownum

A "pseudocolumn" is an expression that you can reference on an SQL statement. Two important pseudocolumns are rowid and rownum.

To learn how to use pseudocolumns, follow these steps:

  1. 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.
  2. The rowid pseudocolumn 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 Departments table:
    Display rowid of IT Helpdesk Department
  3. Now let's use the rowid value we just retrieved to fetch the the IT Helpdesk department:
    Fetch IT Helpdesk Department with rowid
    This technique might be useful in an application program. It saves time retrieving a row.
  4. The Departments table in the HR schema contains 27 departments. Let's imagine we only want to show the first 10 departments. The rownum pseudocolumn can help us out. Here is the query to show the first 10 departments:
    Display 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 rowid pseudocolumn data, the rownum data is not stored on the database.
Author: Stephen Withrow


About Webucator
