Online Tutorials & Training Materials | STechies.com
Register Login
Advertisement

Difference between ROWID and ROWNUM

|| 0

Difference between ROWID and ROWNUM
Stechies

What is ROWID 

  • ROWID is a pseudo column in a table which store and return row address in HEXADECIMAL format with database tables.ROWID is the permanent unique identifiers for each row in the database.
  • ROWID consists of 18 character string with the format. BBBBBBBBB.RRRR.FFFF Where B is Block, R is Row, F is FIle

An example query would be:

SELECT ROWID, first_name  
   FROM employees
   WHERE department_id = 30;
  • A user can access a row quickly and easily using its row ID. 
  • ROWID can also be used to delete the duplicate records from a tame. 

What is ROWNUM 

  • For each row returned by a query, the ROWNUM pseudo column returns a number which indicates the order in which a row was selected from a table. For example, the 1st row gets the number 1, 2nd gets the number 2 and so on. 
  • ROWNUM can be used to limit the number of rows returned by a query, as shown in the example below:

SELECT * FROM employees WHERE ROWNUM < 10;

  • The row numbers of the records might change if an order by clause is used in the query. 
  • ROWNUM can also be used for assigning unique values for every row in a table. 
  • The user can also use ROWNUM to present the dataset in a report with serial numbers. 

Difference between RowNum and RowId

ROWID

ROWNUM

ROWID is representative of the allocation of physical memory.

ROWNUM is representative of the sequence allocated to any data retrieval bunch.

ROWID is the permanent identity or address of a row.

ROWNUM is a temporarily assigned sequence to a row.

ROWID is a 16-digit Hexadecimal number in the format BBBBBBBB.RRRR.FFFF. Here B is Block, R is Row, and F is File.

ROWNUM is a numeric sequence number.

The output of ROWID is the physical address of a row.

The output of ROWNUM is the sequence number of a row.

ROWID helps to retrieve data from a row.

ROWNUM allows retrieving a row containing data.

ROWID comprises of the position of the row, data object number, the data block in the data file, as well as data file in which row resides.

ROWNUM comprises of sequence numbers of the rows.

Oracle automatically generates a unique ROWID at the time of insertion of a row.

ROWNUM is a dynamic value that is retrieved automatically with certain statement outputs.

 

The fastest way of accessing data is by using ROWID.

Accessing data is unrelated to ROWNUM.


Advertisement
Advertisement
Advertisement