Saturday 25 January 2014

Difference Between ROWID AND ROWNUM in Oracle

Difference between ROWID and ROWNUM.
                       Row id
                           RowNum
1.Physical address of the rows.

2.Rowid is permanent
3.Rowid is 16-bit hexadecimal
4.Rowid gives address of rows or records
5. Rowid is automatically generated unique id of a row and it is generated at the time of insertion of row.
6. ROWID is the fastest means of accessing data.
7. They are unique identifiers for the any row in a table.

1. Rownum is the sequential number, allocated to each returned row during query execution.
2. Rownum is temporary.
3.Rownum is numeric
4.Rownum gives count of records
5. Rownum is an dynamic value automatically
retrieved along with select statement output.
6. It represents the sequential order in which Oracle has retrieved the row.


Courtesy: http://oracle-surya.blogspot.in/2012/04/difference-between-rowid-and-rownum.html

More Explanation:

Question:  Does Oracle make a distinction between a ROWID and ROWNUM?  If so, what is the difference between ROWNUM and ROWID?
Answer:  Just as your home address uniquely identifies where you live, an Oracle ROWID uniquely identifies where a row resides on disk.  The information in a ROWID gives Oracle everything he needs to find your row, the disk number, the cylinder, block and offset into the block. 
The ROWNUM is a "pseudo-column", a placeholder that you can reference in SQL*Plus.  The ROWNUM can be used to write specialized SQL and tune SQL.
For example, to only display the first-10 rows, you might apply a ROWNUM filter:
select *
from (
   select * from my_view where alert_level=3 order by alert_time desc)
where
    rownum<=10;
 In sum, the difference between ROWNUM and ROWID is that ROWNUM is temporary while ROWID is permanent.  Another difference is that ROWID can be used to fetch a row, while ROWNUM only has meaning within the context of a single SQL statement, a way of referencing rows within a fetched result set.

Courtesy: http://www.dba-oracle.com/t_rownum_rowid_difference.htm

No comments:

Post a Comment