SQL Difference Between SQL Tutorials

What is ROWID and ROWNUM in SQL with Examples?

Amit S February 2, 2017

In this article I would like  to explain ‘What is ROWID and ROWNUM in SQL?’. Pseudocolumns are actually associated with the table data but it has nothing to do with table data.ROWID & ROWNUM are Pseudocolumns which are not actual columns in the table but behave like actual columns. You can select the values from Pseudocolumns like ROWID & ROWNUM.ROWID & ROWNUM are very important Pseudocolumns in oracle which is used in data retrieval.

CLICK HERE TO GET 20 MOST IMPORTANT COMPLEX SQL QUERIES..

Pseudocolumns are nothing but logical columns which behaves like a physical columns in database.

ROWID and ROWNUM
                Rowid and Rownum in SQL

What is ROWID?

  1. ROWID is nothing but the physical memory location on which that data/row is stored.ROWID basically returns address of row.
  2. ROWID uniquely identifies row in database.
  3. ROWID is combination of data object number,data block in datafile,position of row and datafile in which row resides.
  4. ROWID is 16 digit hexadecimal number whose datatype is also ROWID Or UROWID
  5. The fastest way to access a single row is ROWID
  6. ROWID is unique identifier of the ROW.

CLICK HERE TO GET 20 IMPORTANT QUESTIONS ON PERFORMANCE TUNING

Example:- select rowid from dual;

AAAAECAABAAAAgiAAA

  • What is ROWNUM?

  1. ROWNUM is magical column in Oracle which assigns the sequence number to the rows retreives in the table.
  2. To limit the values in the table you can use rownum pseudocolumn
  3. ROWNUM is nothing but logical sequence number given to the rows fetched from the table.
  4. ROWNUM is logical number assigned temporarily to  the physical location of the row.
  5. You can limit the values in the table using rownum
  6. ROWNUM is also unique temparary sequence number assigned to that row.

Example:

Select Rownum from dual;

Answer- 1

So in above article we have dicussed the difference between ROWID & ROWNUM.Following is the difference between ROWID & ROWNUM in tabular format:

                       ROWID                            ROWNUM 
1.ROWID is nothing but Physical memory allocation

2.ROWID is permanant to that row which identifies the address of that row.

3.ROWID is 16 digit Hexadecimal number which is uniquely identifies the rows.

4.ROWID returns PHYSICAL ADDRESS of that row.

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.

 1. ROWNUM is nothing but the sequence which is allocated to that data retreival bunch.

2. ROWNUM is tempararily allocated sequence to the rows.

3.ROWNUM is numeric sequence number allocated to that row temporarily.

4.ROWNUM returns the sequence number to that row.

5. ROWNUM is an dynamic value automatically
retrieved along with select statement output.

6.ROWNUM is not related to access of data.

Hope you like this article on What is ROWID and ROWNUM in SQL? . Kindly comment if any suggestions .

CLICK HERE TO GET 20 SQL INTERVIEW QUESTIONS FOR FRESHER.. 

HOME

4 Replies to “What is ROWID and ROWNUM in SQL with Examples?”

  1. This is the Query , i have written :
    delete from test11 t4 where rowid in(
    select t2.rowid from test11 t2 ,
    (select t1.x , t1.y , min(t1.rowid) as id from test11 t1 ,
    (select x , y, count(1) from test11
    group by x , y
    having count(1) >1
    ) t
    where t1.x = t.x
    and t1.y = t.y
    group by t1.x , t1.y
    ) t3
    where t2.rowid = t3.id
    )

  2. Hi Amit ,
    Is it possible to create this queries without using ROWID & ROWNUM. Because ROWID & ROWNUM are not useful on other database

Comments are closed.