Pseudo Column in Oracle #Diksha Oracle Training Centre

 

Pseudo Column in Oracle.

Hello Everyone,

Today I will discuss about Pseudo column in Oracle. This topic is very important so before going to read this blog, I suggest you to please see my previous blogs which is in archive folder for your reference so that it’s easy for you to understand this topic. Let’s discuss about Pseudo Column in Oracle.

Pseudo column: A pseudo-column is dynamic in Oracle as it behaves like a table column but is not stored in the table. Pseudo column behaves like a table column, but is not actually stored in the table. You can select from pseudo columns, but you cannot insert, update, or delete their values. A pseudo column is also similar to a function without arguments. In  Simple word we can say Pseudo column is a column that yields a value when selected, but which is not an actual column of the table. An example is RowID or SysDate. It can be use in combination with the DUAL table.

Below are the Pseudo Column commonly used in Oracle Database:

ROWID

ROWNUM

LEVEL

CURVAL

NEXTVAL

 

ROWID: When you create table and insert the data in a table , For Each row in the database , the ROWID column return’s a row address. The ROWID contains three information about row address i.e. File no , Data Block No  and Record No.

ROWID can returns values by using select statement in any table. For each row there is unique ID generated by Oracle even for Duplicate values. Using any query ROWID is very fast as it’s now where the value for each row in a table.

Example :

Select Max(ROWID) , emp_id

from emp

group by emp_id;

Result you will see in Hexadecimal for each row you will see different ROWID that’s why it’s easy to know the ROWID for duplicate rows as well.

Here in table emp we have duplicates records so query for removing duplicate rows from a table is as below:

Delete from emp where rowid not in

 (select max(rowid)

from emp group by emp_id);

Here we use subquery where inner query will return only 1 value for each dept_no as we use function and group by clause and in main query we mention not in so it will delete all the duplicate records apart from inner query which returns the value. We can also use MIN function to eliminate duplicate records.

Output of both Query is as below.

 

Note : Removing Duplicate records by using self join or other will perform slow as compared to ROWID as  as it’s know the position and value for each row.

ROWNUM : ROWNUM is a Pseudo column where  Oracle engine maintains the number of each record inserted by users in table. With the help of ROWNUM we can see the data according to the record inserted in the table.

Example:

 

SELECT * FROM EMP WHERE ROWNUM <= 4; 

SELECT * FROM EMP WHERE ROWNUM <=7;

Here 2nd query will give the first 7 rows of the table and 1st query give result against the condition mentioned in where condition.

Output of both the query is as below:

 

 

LEVEL: Level is a Pseudo column and it’s for each row returned by a hierarchical query, the LEVEL of Pseudo column returns 1 for a root node, 2 for a child of a root, and so on depends how you use.

Example :

SELECT Level AS Sequence

FROM Dual

CONNECT BY Level <= 7

Explanation:

Above query will execute Level having initial value 1 from dual, which is dummy table. In query, condition will be checked and relationship created using Connect By between Level having value 1 and the specified condition.

Note : Level can be helpful to start a sequence of number from any particular initial value.

Output of the above query is as below:

 

 

CURVAL and NEXTVAL

Before Describing Pseudo Column we need to know about Sequence. Sequence is an object which is stored in database that can generate unique sequential values , Sequence basically used in primary or unique Key . We can use Pseudo Column CURVAL and NEXTVAL in SQL statements. 

Syntax for creating Sequence:

 

CREATE SEQUENCE emp_seq

 START WITH  1

 INCREMENT BY   1

 NOCACHE

 NOCYCLE;

 

Here we created sequence emp_seq which can be used on any tables to select or insert the value in table by using pseudo column i.e CURVAL and NEXTVAL

CURVAL : CURVAL is a Pseudo Column which mainly used in sequence to find the current value of particular column . It’s return the current value of Sequence.

Example

 SELECT emp_seq.curval FROM DUAL;

Output of above query is as below:

 

NEXTVAL : NEXTVAL is a Psuedo Column which used to insert next vale in table .

 

It simply increments the sequence and returns the next value.

Example : I created a temporary table test where for ID column I use sequence emp_seq for next value.

INSERT INTO test VALUES (emp_seq.nextval, 'DHEERAJ', 'MDB', 'BIHAR');

INSERT INTO test VALUES (emp_seq.nextval, 'Sakshi', 'Ghaziabad', 'UP');

INSERT INTO test VALUES (emp_seq.nextval, 'Diksha', 'Delhi', 'NEW DELHI');

INSERT INTO test VALUES (emp_seq.nextval, 'Abhishek', 'PATNA', 'BIHAR');

COMMIT;

Select ID , Name from test where rownum <=5;

Output of above statement is as below:

 

NOTE : if we want to see the current value of sequence by using select statement it will give you the last value which we inserted in the table . Sequence is important to insert the increment value directly to the tables and we can see the exact value of sequence by using sequence.CURRVAL .

Hope you are able to understand the importance of Pseudo Column in Oracle. Pseudo column in Oracle database is very frequently used and in interview they can ask you about Pseudo column questions  and also they can ask how it works and it's benefit. As in Interview I saw lot’s of candidate only talk about ROWID . So please go through all the Pseudo column which I mentioned and try to use in query and see the performance and also get the benefits of using sequence by using Pseudo column CURVAL and NEXTVAL in any tables.

This Blog is important for Candidate who is preparing for interview and also good for candidate who is learning RDBMS. Please read this blog carefully and let me know if you have any questions.

Thanks.