Ø 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;