The difference between rownum and rowid in oracle

The difference between ROWNUM and ROWID in Oracle

In Oracle database, ROWNUM and ROWID are two different concepts, each with its own purpose and function:

ROWNUM

• Definition: ROWNUM is a pseudo column that returns the number of each row in the table, starting from 1.
• Usage: ROWNUM is usually used to limit the number of rows in the result set or in paging queries.
• Example:

<code class="sql">SELECT * FROM employees
WHERE ROWNUM <= 10;  -- 返回前 10 行</code>

ROWID

• Definition: ROWID is An internal identifier that uniquely identifies each row in the table.
• Use: ROWID is mainly used for internal purposes such as indexing and data block management. It can also be used to recover deleted rows or identify specific rows in a table.
• Example:

<code class="sql">SELECT ROWID FROM employees
WHERE employee_id = 10;  -- 返回员工 ID 为 10 的那一行的 ROWID</code>

Key Difference

• Feature: ROWNUM Returns the row number, while ROWID returns the internal identifier of the row.
• Granularity: ROWNUM is unique for each query, while ROWID is unique throughout the database.
• Usage: ROWNUM is used to limit results or pagination, while ROWID is used for internal data management.
• Performance: ROWNUM may cause slower queries because Oracle must calculate the row number for each row. ROWID usually has better performance because it is precomputed.

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