database ROWID (oracle) - 有什么用吗？

ROWID (oracle) - any use for it?

提问by user319280

My understanding is that the ROWID is a unique value for each row in the result returned by a query.

我的理解是 ROWID 是查询返回的结果中每一行的唯一值。

Why do we need this ROWID? There is already the ROWNUM in ORACLE.

为什么我们需要这个 ROWID？ORACLE 中已经有 ROWNUM。

Have any one used ROWID in a SQL query?

有没有人在 SQL 查询中使用过 ROWID？

回答by APC

ROWID is the physical location of a row. Consequently it is the fastest way of locating a row, faster even than a primary key lookup. So it can be useful in certain types of transaction where we select some rows, store their ROWIDs and then later on use the ROWIDs in whereclauses for DML against those same rows.

ROWID 是一行的物理位置。因此，它是定位行的最快方式，甚至比主键查找还要快。因此，它在某些类型的事务中很有用，我们选择一些行，存储它们的 ROWID，然后在whereDML 子句中使用 ROWID来针对这些相同的行。

The Oracle SELECT ... FOR UPDATE syntax implicitly uses ROWID, when we update the locked row using WHERE CURRENT OF. Also the EXCEPTIONS table (referenced when applying constraints with the EXCEPTIONS INTO clause) has a column ROW_ID. This allows us to quickly identify the rows which are breaking our constraint.

当我们使用 WHERE CURRENT OF 更新锁定行时，Oracle SELECT ... FOR UPDATE 语法隐式使用 ROWID。此外，EXCEPTIONS 表（在使用 EXCEPTIONS INTO 子句应用约束时引用）有一个 ROW_ID 列。这使我们能够快速识别违反约束的行。

That latter example points to another general usage: when we are writing some generic piece of code and need a mechanism for storing UIDs without concerns regarding data type, composite keys, etc.

后一个例子指出了另一种通用用法：当我们编写一些通用代码并且需要一种机制来存储 UID 而不考虑数据类型、复合键等时。

ROWNUM on the other hand is a pseudo-column which tags a row in a given result set. It has no permanent significance.

另一方面，ROWNUM 是一个伪列，它标记给定结果集中的一行。它没有永久的意义。

edit

编辑

The ROWID for a given record can change over the lifetime of a system, for instance through a table rebuild. Also if one record is deleted a new record could be given that ROWID. Consequently ROWIDs are not suitable for use as UIDs in the long term. But they are good enough for use within a transaction.

给定记录的 ROWID 可以在系统的生命周期内更改，例如通过表重建。此外，如果删除了一条记录，则可以为该 ROWID 指定一条新记录。因此，从长远来看，ROWID 不适合用作 UID。但是它们足以在事务中使用。

回答by user319280

I now know an example for this.

我现在知道一个例子。

suppose you have table with no primary keys. so this table can have duplicate rows. How would you delete duplicate rows but keep exactly one of that kind?

假设您有没有主键的表。所以这个表可以有重复的行。你将如何删除重复的行，但保留那种类型的行？

Oracle provides ROWID as a kind of substitute for primary key. You can write a nested query which is of correlated type [(group by all columns in the row and take MIN(ROWID) in each group in inner query, for each group delete the other rows in the group in outerquery)]

Oracle 提供 ROWID 作为主键的一种替代。您可以编写一个相关类型的嵌套查询[（按行中的所有列分组，并在内部查询中的每个组中取 MIN(ROWID)，对于每个组，在外部查询中删除组中的其他行）]

Example

例子

SQL> select * from employees;

       SSN NAME
---------- ----------
         1 helen
         1 helen
         2 helen
         2 peter
        10 sally
        11 null
        11 null
        12 null

8 rows selected.

SQL> delete from employees where ROWID NOT IN (select min(ROWID) from employees
group by ssn,name);

2 rows deleted.

SQL> select * from employees;

       SSN NAME
---------- ----------
         1 helen
         2 helen
         2 peter
        10 sally
        11 null
        12 null

6 rows selected.

回答by Randy

note that ROWID does not persist across a database EXPORT and IMPORT cycle. you should NEVER store a rowid in your tables as a key value.

请注意，ROWID 不会在数据库 EXPORT 和 IMPORT 循环中持续存在。你永远不应该在你的表中存储一个 rowid 作为键值。

回答by Rob Heusdens

A ROWID consists of (but not necessarily in that order, although the ROWNUM part is the last part of ROWID as far as I remember):

一个 ROWID 包括（但不一定按这个顺序，虽然 ROWNUM 部分是我记得的 ROWID 的最后一部分）：

• OBJID The unique indentifier of the object.
• FILENO The relative number of the datafile in the tablespace.
• the BLOCKNO The relative block number in the datafile after the fileheader.
• the ROWNUM The relative rownum within the block.

• OBJID 对象的唯一标识符。
• FILENO 表空间中数据文件的相对编号。
• BLOCKNO 数据文件中文件头之后的相对块号。
• ROWNUM 块内的相对行数。

You can easily break down the ROWID into it's composite fields (OBJID, FILENO, BLOCKNO, ROWNUM) by using the ROWIDTOCHAR() SQL-function, or use:

您可以使用 ROWIDTOCHAR() SQL 函数轻松地将 ROWID 分解为其复合字段（OBJID、FILENO、BLOCKNO、ROWNUM），或使用：

    SQL> select DBMS_ROWID.ROWID_OBJECT(rowid) "OBJECT",
    2         DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE",
    3         DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK",
    4         DBMS_ROWID.ROWID_ROW_NUMBER(rowid) "ROW"
    5  from dual
    6  /
        OBJECT       FILE      BLOCK        ROW
    ---------- ---------- ---------- ----------
           258          1       2082          0

Note that the field ROWNUM (or ROW in the above query) is notthe same ROWNUM as the SQL pseudo column ROWNUM you use in SELECT query, which is just the dynamically generated rownumber of the row in the result set.

请注意，字段 ROWNUM（或上述查询中的 ROW）与您在 SELECT 查询中使用的 SQL 伪列 ROWNUM不是同一个 ROWNUM，它只是结果集中动态生成的行的行号。

Note that because of this implementation, rows, blocks, extents and segments are not transportable without breaking the ROWID, which invalidates indexes.

请注意，由于此实现，行、块、范围和段在不破坏 ROWID 的情况下不可传输，这会使索引无效。

The ROWID is the most direct access path to the block in which the row resides and uniquely indentifies the row, because it encodes the unique file and unique block within that file and unique row within that block.

ROWID 是该行所在块的最直接访问路径并唯一标识该行，因为它对唯一文件和该文件中的唯一块以及该块中的唯一行进行编码。

More information:

更多信息：

See: DBMS notes on ROWID format

请参阅：关于 ROWID 格式的 DBMS 注释

Note:

笔记：

If you have a little understanding of the way oracle structures database files and blocks, and know some C programming, you could quite easily make a program that displays the contents of the block given by ROWID (an 8k, or whatever block size is used in the database, block that starts at fileheadersize + BLOCKNO * BLOCK_SIZE. The block contains the block header and thereafter (assuming the table is not clustered) the rowdir, which for each row gives the relative offset within the block for each row. So for example at position 0 within the rowdir is the relative offset of the 0-th row within the block, at position 1 within the rowdir the relative position of the 1-st row, etc. The number of rows itself is stored somewhere in the block header (See orale documentation on the block layout).

如果您对 oracle 构建数据库文件和块的方式有一点了解，并且知道一些 C 编程，您可以很容易地制作一个程序，显示由 ROWID 给出的块的内容（8k，或任何块大小用于数据库，从文件头大小 + BLOCKNO * BLOCK_SIZE 开始的块。该块包含块头，然后（假设表没有聚集）rowdir，它为每一行给出块内每一行的相对偏移量。例如在 rowdir 中的位置 0 是块中第 0 行的相对偏移量，在 rowdir 中的位置 1 是第 1 行的相对位置，等等。行数本身存储在块头中的某处（请参阅有关块布局的口头文档）。

With a little bit of programming knowledge and looking up the documentation on oracle database files an blocks for the exact layout of blocks, you can see how rows are stored on disk, and even reconstruct all the values the row stores for each column. Each row contains metadata for the length of the row and the number of columns, and for each column, an indication for the type of the column and the bytesize and therafter the value. Bytesize 0 means that the column data is empty (or: NULL).

借助一点编程知识并查找有关 oracle 数据库文件和块的文档以了解块的确切布局，您可以了解行如何存储在磁盘上，甚至可以重建行为每一列存储的所有值。每一行都包含行长和列数的元数据，对于每一列，包含列类型和字节大小的指示，以及随后的值。Bytesize 0 表示列数据为空（或：NULL）。

回答by Mark Byers

ROWID uniquely identifies a row within a table. ROWNUM gives you the row number of a result for a specific query. The two are very different and are not interchangeable.

ROWID 唯一标识表中的一行。ROWNUM 为您提供特定查询结果的行号。两者非常不同，不可互换。

Also there is ROW_NUMBER which is a more modern version of ROWNUM, and behaves slightly differently. Check out this articlewhich explains the difference.

还有 ROW_NUMBER，它是 ROWNUM 的更现代版本，其行为略有不同。查看这篇文章，它解释了差异。

回答by Robert Greiner

ROWID basically allows you to have two rows with the exact same data. While, you typically want your Primary Key to be a little more meaningful than a RowID, it is just a simple way of automatically ensuring uniqueness between rows.

ROWID 基本上允许您拥有两行具有完全相同的数据。虽然您通常希望主键比 RowID 更有意义，但这只是一种自动确保行之间唯一性的简单方法。