ROWID 在 SQL DBMS 内部索引是否唯一？
据我了解，访问特定行的最快方法是通过其 ROWID。在 INFORMIX-SE 7.3 中，当我执行 SELECT ROWID FROM table 时，我注意到它的值是 SERIAL[INT] 类型。在 Oracle 中，它们是 SERIAL[HEX]。有人曾经将 ROWID 用于实际用途吗？如果我想找到添加到表中的最新行，SELECT MAX(ROWID) FROM table 会比 SELECT MAX(pk_id) FROM table 更快、更可靠吗，其中 pk_id 是用户创建的 SERIAL 列？您还让 ROWID 为您带来过哪些其他实际用途？
It's my understanding that the quickest way to access a particular row is by its ROWID. In INFORMIX-SE 7.3, when I do a SELECT ROWID FROM table I notice that its values are type SERIAL[INT]. In Oracle, they are SERIAL[HEX]. Has anyone ever used ROWID for any practical use? If I wanted to locate the most recent row added to a table, would SELECT MAX(ROWID) FROM table be quicker and more reliable than say SELECT MAX(pk_id) FROM table, where pk_id is a user-created SERIAL column? What other practical use have you ever put ROWID to work for you?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
你的理解不一定正确。 SQL Server 中的 ROWID 属性主要用于复制，以保证表具有单字段唯一索引值。这样，复制系统不必考虑您的设计可能采用的任何特定主键语义，同时仍然能够通过单列识别每一行。任何表都不需要具有 ROWID 列，除非它是合并复制发布的一部分，因此与 Oracle 不同，并非每个表都具有 ROWID 列。它也没有达到相同的目的（它们是 SQL Server 上的 Guid（或 T-SQL 术语中的
uniqueidentifier），并且是随机的，而不是像 Oracle 上那样的连续整数）。从表中检索行的最快方法是通过聚集索引访问该行。一张表只能有一个聚集索引，因为它决定了磁盘上行的物理顺序。此外，如果表有主键，则主键就是聚集索引。虽然可以声明一个没有主键的表并将聚集索引分配给其他东西，但我无法（从我的头脑中）理解为什么你想要这样做（或者，出于实际目的，如何证明没有主键的表是合理的）。
简而言之，这意味着停用行的最快方法是使用表的主键。除非
ROWID列是主键（这当然是可能的），那么这不是最快的方法。Your understanding is not necessarily correct. The
ROWIDproperty in SQL Server is primarily intended for replication as a way to guarantee that the table has a single-field unique index value. This way the replication system does not have to account for any specific primary key semantics that your design might employ, while still being able to identify every row by a single column. No table is required to have aROWIDcolumn unless it is part of a merge replication publication, so it's not something that every table has, unlike Oracle. It also doesn't serve the same purpose (they're Guid's--oruniqueidentifierin T-SQL parlance--on SQL Server and are random, not sequential integers like they are on Oracle).The quickest way to retrieve a row from a table is by accessing the row via the clustered index. A table can only have one clustered index, as it's what determines the physical ordering of the rows on the disk. Furthermore, if the table has a primary key, the primary key is the clustered index. While it's possible to declare a table without a primary key and assign the clustered index to something else, I can't (off the top of my head) fathom a reason why you'd want to do this (or, for practical purposes, how you can justify having a table without a primary key).
In short, that means that the quickest way to retireve a row is by using the primary key of the table. Unless the
ROWIDcolumn is the primary key (which is certainly possible to do), then it isn't the fastest way.好吧，我只能真正讲述它在 Oracle 中的工作原理，使用它已经超过 19 年了:-)
简而言之，ROWID 是一个内部标识，其作用类似于物理地址。它可以分为数据库文件号、块号和行号。因此，获取 ROWID 使数据库引擎能够在单个直接 IO 中查找数据。
在索引中，B* 树的叶节点上有 ROWID，直接指向数据的位置，例如在主索引中。
作为物理地址，它在磁盘上重定位时提交更改，这可能在恢复备份、重建表或导出/导入数据后发生。
数据库引擎可以执行一些技巧，例如，将可插入表空间从一个实例移动到另一个实例时以避免重建索引，但是这是严格的数据库引擎内部结构。
因此，为了避免出现麻烦，请将 ROWID 留给数据库引擎内部使用。存储 ROWID 供您自己使用最终会导致不一致。
Well, I can only really tell how it works in Oracle, using it for 19+ years :-)
Put simply, ROWID is an internel identification, that acts like an physical address. It can be split into database file no, block no, and row no. So obtaining the ROWID makes the db engine able to look the data up in a single direct IO.
In an index the B* tree will have ROWIDs on the leaf nodes pointing directly the location of the data, e.g. in a primary index.
Being an physical address it is submit to change on relocation on disk, which can happen after restoring a backup, rebuilding a table, or export/import of data.
The db engine can do some tricks, e.g. when moving a pluggable tablespace from one instance to another to avoid rebuilding indexes, however this is strickly db engine internals.
So to keep out of trouble leave the ROWID for internal use for the db engine. Storing the ROWID for your own usage will eventually lead to inconsistency.
在 Informix-SE 中，ROWID 基本上是用于保存表的 C-ISAM 文件中的记录号。当然，SE 只处理固定大小的记录（没有 VARCHAR 数据）。
在 Informix Dynamic Server 中，ROWID (a) 更复杂（页号加槽号），并且 (b) 并不总是存在（碎片表不会公开 ROWID，除非表是使用 ROWIDS 创建的，在这种情况下，ROWID 为毕竟是一个被索引的物理列） - 请注意！
如果没有删除任何数据并且您正在使用 SE，则选择具有最大 ROWID 的行将是最近添加的行。如果删除一行，那么该空间最终将被重用，然后最近添加的行将不再是具有最大 ROWID 的行。 （由于各种复杂的原因，IDS 没有做出这样的承诺。）
ROWID 的 SE 实现不会将 ROWID 存储在表中，也不会在其上创建索引，但它不需要索引，因为它知道公式对于在哪里查找数据（数据文件中的偏移量 = ROWID * RowSize），请在 RowSize 上加一或减一 ROWID 或两者。
至于 ROWID 的实际用途，在将碎片添加到 IDS 之前使用的样式是为表中感兴趣的记录选择 ROWID 值列表，并在内存中维护该列表：
然后，程序可以将这些行呈现为一个有时，通过存储的 ROWID 获取当前数据。当程序运行时，记录的 ROWID 不会改变。这确保了在显示记录时显示当前数据（无论是当前用户还是其他人的编辑）。
有一个您熟悉的程序，ISQL Perform，其行为如下。它不适用于碎片表（在 IDS 中是必需的；SE 不支持碎片表），除非它们是使用带有 WITH ROWIDS 子句的物理 ROWID 列创建的。
In Informix-SE, the ROWID is basically the record number within the C-ISAM file that is used to hold the table. SE only deals in fixed size records, of course (no VARCHAR data).
In Informix Dynamic Server, the ROWID is (a) more complex (page number plus slot number) and (b) not always present (fragmented tables do not expose the ROWID, unless the table was created WITH ROWIDS, in which case the ROWID is a physical column that is indexed after all) - be aware!
If no data is ever deleted and you are using SE, then selecting the row with the maximum ROWID will be the most recently added row. If a row is deleted, then that space will eventually be reused, and then the most recently added row ceases to be the one with the maximum ROWID. (IDS does not make that promise for a variety of complex reasons.)
The SE implementation of ROWID does not store the ROWID in the table, and does not create an index on it, but it does not need an index because it knows the formula for where to go to find the data (offset in data file = ROWID * RowSize), give or take a plus one on RowSize or a minus one ROWID or both.
As to practical use for ROWID, the style that was used before fragmentation was added to IDS was to select a list of ROWID values for the records of interest in the table, maintaining that list in memory:
Then, the program could present these rows one at time, fetching the current data via the stored ROWID. The ROWID for a record would not change while a program was running. This ensured that the current data - whether edits from the current user or someone else - was shown when the record was displayed.
There's a program you're familiar with, ISQL Perform, that behaves like this. And it does not work with fragmented tables (necessarily in IDS; SE does not support fragmented tables) unless they are created with a physical ROWID column with the WITH ROWIDS clause.
也许术语“RDBMS”而不是“SQL 服务器”？
将任何目的附加到 ROWID 上都是一个坏主意。特别是如果您有删除和重新创建表的习惯。如果您的桌子需要 SERIAL PK，那么它就应该具备。在应用程序中使用 ROWID 不会带来任何好处。
Perhaps the term "RDBMS" rather than "an SQL server"?
Attaching any purpose to a ROWID is a bad idea. Particularly if you're in the habit of dropping and recreating tables. If your table needs a SERIAL PK, then that's what it should have. No good can come of using ROWIDs within your application.