Saturday 2 August 2014

Rownum and Row-id Feature of Oracle Database



Rownum:

  • Rownum is pseudo column and behave like table column.
  •  It having temporary value.
  •  It assign number  to each row in table at time of selection.
rownum has value < ,<= value it can't worked with > value

Row-id:


when we are inserting data into table Oracle server automatically generates an unique 
identification number for identifying record uniquely.



The Main Difference between Rownum and Row-id is
Rownum is temporary values where Row-id having fixed valued it cannot changed.

Some Example:


SQL> select * from t1;

       SNO NAME       ADDRESS
---------- ---------- ----------
         1 kunal      nagar
        21 sachin     parbhani
        11 vabhaiv    satara
        44 jags       daund

SQL> select * from t1 minus (select * from t1 where rownum <=(select count(*)-1 from t1));

       SNO NAME       ADDRESS
---------- ---------- ----------
        44 jags       daund

SQL> select * from t1 minus (select * from t1 where rownum <=(select count(*)-2 from t1));

       SNO NAME       ADDRESS
---------- ---------- ----------
        11 vabhaiv    satara
        44 jags       daund


SQL> select rowid,rownum,sno,name from t1;

ROWID                  ROWNUM        SNO NAME
------------------ ---------- ---------- ----------
AAARC0AAEAAAAA/AAA          1          1 kunal
AAARC0AAEAAAAA/AAB          2         21 sachin
AAARC0AAEAAAAA/AAC          3         11 vabhaiv
AAARC0AAEAAAAA/AAD          4         44 jags

SQL>



No comments:

Post a Comment