ROWID（oracle） - 任何使用它？ [英] ROWID (oracle) - any use for it?

问题描述

我的理解是，ROWID是查询返回的结果中的每一行的唯一值。为什么我们需要这个ROWID？（已经有ROWNUM是ORACLE）。有任何一个在sql查询中使用ROWID？

My understanding is that ROWID is a unique value for each row in the result returned by a query. why we need this ROWID ?( there is already ROWNUM is ORACLE). Have any one used ROWID in sql query?

推荐答案

ROWID是行的物理位置。因此，它是定位行的最快方式，即使比主键查找更快。所以它可以在某些类型的事务中使用，其中我们选择一些行，存储它们的ROWID，然后在中使用ROWID，其中 DML针对这些行。

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 where clauses for DML against those same rows.

当使用WHERE CURRENT OF更新锁定行时，Oracle SELECT ... FOR UPDATE语法会隐式使用ROWID。此外，EXCEPTIONS表（在使用EXCEPTIONS INTO子句应用约束时引用）具有列ROW_ID。这允许我们快速识别正在破坏我们的约束的行。

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.

后一个例子指向另一个通用用法：当我们编写一些通用代码并需要一个机制来存储UID而不关心数据类型，组合键等

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.

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

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

编辑

给定记录的ROWID可以在系统，例如通过表重建。此外，如果一个记录被删除，则可以给予新记录ROWID。因此，ROWID长期不适合用作UID。但它们足以在一个事务中使用。

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（oracle） - 任何使用它？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！