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 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 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 specific statement outputs.|
|The fastest way of accessing data is by using ROWID.||Accessing data is unrelated to ROWNUM.|