ROWNUM Pseudocolumn in Oracle

Oracle selects a row from a table and ROWNUM returns a number indicating the order in which Oracle selects the row from a table. Thus the first row returned by a query has ROWNUM value 1, second row returned by the query has a ROWNUM value 2 and so on.

Use of ROWNUM is particularly useful whenever we want to see some rows from a big table. For example in a table there is 10000 row and we want to see the sample of 10 rows. In this case we can use rownum as ,

SELECT * FROM T WHERE ROWNUM<=10;

One thing we need to remember that ROWNUM does not ensure any order of the row. To return a row in an order you must have to use order by keyword. The following example will make you clear.

SQL> create table rownum_test( a number);
Table created.

SQL> insert into rownum_test values(1);
1 row created.

SQL> insert into rownum_test values(2);
1 row created.

SQL> insert into rownum_test values(3);
1 row created.

SQL> select a, rownum from rownum_test ;
A ROWNUM
---------- ----------
1 1
2 2
3 3


SQL> delete from rownum_test where A=2;
1 row deleted.

SQL> insert into rownum_test values(4);
1 row created.

SQL> select a, rownum from rownum_test ;
A ROWNUM
---------- ----------
1 1
3 2
4 3

SQL> insert into rownum_test values(2);
1 row created.

SQL> select a, rownum from rownum_test ;
A ROWNUM
---------- ----------
1 1
3 2
4 3
2 4

To use rownum and return top 4 roes in a ascending order use it as,
SQL> select * from (select * from rownum_test order by a) where rownum<=4;

A
----------
1
2
3
4

Always remember conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows,

SQL> select * from rownum_test where rownum>1;
no rows selected

This is because the first row fetched is assigned a ROWNUM of 1 and makes the condition false as 1is not grater than 1. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and again makes the condition false. Thus all rows subsequently fail to satisfy the condition, so no rows are returned.

Related Documents:
----------------------------
Pseudocolumns in Oracle

Can I get an idea when a row is created (ORA_ROWSCN Pseudocolumn)

To know the exact time of when a row is inserted or updated or deleted you have to use audit system or use logminer utility. But to use these two tool you have to configure your environment previously. However if you don't enable either audit option or supplemental logging (which is required for analysis in logminer) you can still get a rough idea about a row in a table about when it is modified/inserted more precisely the most recent change of a row.

This is possible by using ORA_ROWSCN psedocolumn.

•ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change for each row.

•The resultant value of ORA_ROWSCN is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides.

•But a better result of the row chaging time of a table can by found by creating tables with row-level dependency tracking. This is done by CREATE TABLE ... ROWDEPENDENCIES statement. If you create your table with this statement then you can get more precise result.

•ORA_ROWSCN can't be used while querying view.

•It is not supported for external tables and also not supported for Flashback Query..

Example:
----------------
The get a rough idea of the rows of table a when the rows were created as well as SCN query as,
SQL> select scn_to_timestamp(ORA_ROWSCN), ORA_ROWSCN , a from a;

SCN_TO_TIMESTAMP(ORA_ROWSCN) ORA_ROWSCN A
--------------------------------------------------------------------------- ---------- ----------
09-JUL-08 06.53.59.000000000 AM 9020925 5
13-JUL-08 06.40.33.000000000 AM 9791113 1
13-JUL-08 06.40.33.000000000 AM 9791113 9

Related Documents:
-----------------------------
Pseudocolumns in Oracle

Pseudocolumns in Oracle

The word pseudo means flase. Pseudocolumn means false column which indicate the column is not stored within a table but they behaves as they are table columns.

You can only do select operation on pseudocolumns but you can't do insert, update, or delete their values.

You can compare pseudocolumn to a function which takes no argument.

The difference between pseudocolumn and function without argument is pseudocolumns typically return a different value for each row but functions without arguments typically return the same value for every row in the result set.

Based on the functions pseudocolumn can be divided into several types.

1)Hierarchical Query Pseudocolumns: Discussed in
2)Sequence Pseudocolumns: Discussed in
3)Version Query Pseudocolumns: Discussed in http://arjudba.blogspot.com/2008/07/version-query-pseudocolumns.html
4)COLUMN_VALUE Pseudocolumn: Discussed in
5)OBJECT_ID Pseudocolumn: Discussed in
6)OBJECT_VALUE Pseudocolumn: Discussed in
7)ORA_ROWSCN Pseudocolumn: Discussed in http://arjudba.blogspot.com/2008/07/can-i-get-idea-when-row-is-created.html
8)ROWID Pseudocolumn: Discussed in
9)ROWNUM Pseudocolumn: Discussed in http://arjudba.blogspot.com/2008/07/rownum-pseudocolumn-in-oracle.html
10)XMLDATA Pseudocolumn: Discussed in