What is the difference between rowid and rownum in oracle

ROWID and ROWNUM are used in Oracle to identify rows in a table: ROWID: A unique physical identifier that identifies the location of the row on disk and does not change even if the row is updated. ROWNUM: Pseudo column, indicating the row number of the row in the query result set, starting from 1, changing with the order of the rows, and will be reset when the row is added or deleted.

ROWID and ROWNUM in Oracle

Their difference

ROWID and ROWNUM are two different Oracle table keywords used to identify rows in the table:

• ROWID: A unique, internally managed identifier, used Identifies each row of a table in the database.
• ROWNUM: A pseudo column that returns the row number of the current row in the query, numbered starting from 1.

Detailed explanation

ROWID

• ROWID is a physical identifier, indicating that the row is on the disk position in the upper data block.
• ROWID is unique throughout the database, even for different rows in the same table.
• ROWID does not change as rows are inserted, deleted, or updated unless the table is reorganized.

ROWNUM

• ROWNUM is a logical identifier that represents the relative position of a row in the query result set.
• ROWNUM is unique within the same query result set, but different query result sets can return different ROWNUM values ​​for the same row.
• ROWNUM changes with the order of rows in the query result set, and can be reset when rows are added, deleted, or updated.

Usage

ROWID:

• For rows that need to be uniquely identified across different sessions or transactions operation.
• When rows need to be physically retrieved from the database.

ROWNUM:

• For operations that need to be performed based on the order of the rows.
• When the query result set needs to be sorted or paginated.

Example

<code>SELECT ROWID, ROWNUM FROM table_name;</code>

Result:

##ROWIDROWNUM##AAAA2JDABAAAL4Q4AAAEAAAQ##AAAA2JDABAAAL4Q5AAEAARQ2AAAA2JDABAAAL4Q6AAAEAA6Q3

1

The above is the detailed content of What is the difference between rowid and rownum in oracle. For more information, please follow other related articles on the PHP Chinese website!