Which are Pseudo Columns? | Complete Guide

by

In this article, we will see Pseudo Columns and its use. This is one of the popular interview question.

At the end of this article, I also captured the video tutorial for your better understanding.

So make sure to watch that video as well.

Let us see what are Pseudo Columns and its use.

Here are the three columns in the department table.

Pseudo Columns

Apart from these three columns in the department table, there are some more columns that exist which are called Pseudo Columns or Invisible Columns.

These columns appear which do not exist in any of the table but actually they exist in all the tables.

These are Row ID and Row Number. We can write a simple query to see these columns in the department table.

Pseudo Columns

Pseudo Column Row ID

  • Row ID: is a physical location where the records are stored in the database hard disk. The row ID is generated by the Oracle once when each record is inserted into a table.
  • Whenever we insert a new record into a table Row ID will automatically inserted by Oracle.
  • Row ID doesn’t get changed when we update a record in a table.

For example, you can consider a rented house. The people inside the house keep change whereas the address of that particular house doesn’t get changed.

Now I want to display the first record in the department table, for that I can write a query something like below.

Pseudo Columns

Let us consider the department number 30 as well.

The diagram shows one Row Number for two different departments. So using Row ID we can display records how we want.

I hope you are clear up to here.

Pseudo Column Row Number

A Row Number is a serial number that is generated as when the record is retrieved. It will be assigned to a record in run time.

Row Number keeps change and it will not be stored in the database. It is a dynamic integer value.

To understand this we will retrieve the records from the department table where the department number is 10.

Row Number Dynamic view

If you can compare this table Row Number with the second table row number where the department is 30 you can see which is different.

This is all about Pseudo Columns (Row Number/ Row ID). I hope this article is useful to you.

Please leave your comments in the comment section below.