Pseudo Column


Ø  Pseudo Columns behave like a table column, but is not actually stored in a table.
Ø  Upon pseudo columns only SELECT statements can be implemented, but INSERT, UPDATE or DELETE cannot be implemented.
Ø  The Available pseudo columns are…
o   CURRVAL
o   NEXTVAL
o   LEVEL
o   ROWID
o   ROWNUM
Note: CURRVAL, NEXTVAL, LEVEL will discuss later…
ROWNUM Pseudo Column:
Ø  For Each row returned by a query, the ROWNUM pseudo column returns a number indicating the order in which oracle selects the rows from a set of joined rows or non joined rows.
Ø  The first row selected has a ROWNUM of 1, the second has 2, and so on…
Ø  The ROWNUM can be used to limit the number of rows returned by the query.
Ø  When Order by clause follows a ROWNUM, the rows will be re-ordered by order by clause.
Ø  If order by clause is embedded in a sub query and ROWNUM condition is placed in the TOP_LEVEL query, then the ROWNUM condition can be forced to get applied after the ordering of the rows.
Ø  Conditions testing for ROWNUM Values greater than a positive integer are always FALSE.
Ex:
  • Select Lpad(’ ’,ROWNUM,’*’) From Emp;
  • Select ROWNUM, Ename, Sal From Emp;

Querying For Top ‘N’ Records:
Ø  We can ask for Nth largest or smallest values of a column.
Ø  Never use ROWNUM and ORDER BY clause together as oracle first fetches the rows according to ROWNUM and then sort’s the found rows.
Ø  From Oracle 8i, Order by clause can be used in line views.
Ex:
  • Select ROWNUM, Ename, Sal From Emp where ROWNUM < 6 Order by Sal DESC; à Wrong Way.
  • Select * From (select * From Emp order by Sal Desc ) where Rownum < 6 ; à Proper Way.
ROWID Pseudo Column:
Ø  This Pseudo Column Returns a ROW’s address for each row stored in the data base.
Ø  ROWID values contain Information necessary to locate the physical area of the data base row.
Ø  The Rows in different tables that are stored together in the same cluster can have the same ROWID.
Ø  The data type of the values belonging to the ROWID is of ROWID data type.
Uses of ROWID Values:
Ø  ROWID is the fastest means of accessing a single row from data base.
Ø  ROWID can show how table’s rows are physically stored.
Ø  ROWID’s are UNIQUE identifiers for a row in a table.
Ø  A ROWID can never change during the life time of its row.
Ø  ROWID’s should not be assigned as primary keys as there is a chance of ROWID to change when the database is EXPORTED or IMPORTED.
Ø  When a row is deleted, oracle may reassign its ROWID to a new row that is inserted.
Ø  The ROWID Can never be inserted, updated and deleted manually.
Ø  The ROWID Pseudo column can be used in select and where clauses.

Ex:

  • Select ROWID, Ename, Job From Emp where Deptno = 20;
  • Select Ename, Sal, Job From Emp where ROWID = ’AACQQAACAAAAHAAA’; à just replace one ROWID manually.
  • Select B.Sal, Sum(A.Sal) “Comm Sal” From Emp A, Emp B where A.ROWID < = B.ROWID Group by B.ROWID, B.Sal;