What is ROWID in SQL
- ROWID is a pseudo column in a table in Oracle that stores and returns row addresses in HEXADECIMAL format with database tables.
- ROWID is the permanent unique identifier for each row in the database
- ROWID consists of an 18-character string with the format. BBBBBBBBB.RRRR.FFFF Where B is Block, R is Row, F is FIle
ROWID in Oracle Example
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 duplicate records from a tame.
What is ROWNUM in SQL
- ROWNUM is representative of the sequence allocated to any data retrieval bunch.
- For each row returned by a query, the ROWNUM pseudo column returns a number that indicates the order in which a row was selected from a table. For example, the 1st row gets the number 1, the 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.
RowNum and RowId in Oracle
| 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, the 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. |
Conclusion
ROWID and ROWNUM are both valuable tools in Oracle for working with table data. However, they serve different purposes.
- ROWID provides a permanent, physical address for each row, allowing for very fast access. It's ideal for situations where you need to directly retrieve specific data based on its location.
- ROWNUM assigns a temporary sequence number to each row within a query's result set. It's helpful for tasks like limiting the number of returned rows, adding sequential numbering to reports, or assigning temporary unique values.