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

Stephen has over 30 years of experience in training, development, and consulting in a variety of technology areas including Python, Java, C, C++, XML, JavaScript, Tomcat, JBoss, Oracle, and DB2. His background includes design and implementation of business solutions on client/server, Web, and enterprise platforms. Stephen has a degree in Computer Science and Physics from Florida State University.

About Webucator

Webucator provides instructor-led training to students throughout the US and Canada. We have trained over 90,000 students from over 16,000 organizations on technologies such as Microsoft ASP.NET, Microsoft Office, Azure, Windows, Java, Adobe, Python, SQL, JavaScript, Angular and much more. Check out our complete course catalog.