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