尝试找出Oracle中Rowid的最大长度 [英] Trying to figure out max length of Rowid in Oracle

问题描述

根据我的设计，我想像中获取rowid

As per my design I want to fetch rowid as in

select rowid r from table_name;

转换为C变量.我想知道rowid的最大大小/长度(以字符为单位).

into a C variable. I was wondering what is the max size / length in characters of the rowid.

当前，在我的数据库中最大的表之一中，我们的最大长度为18，而在整个表中，rowid的最大长度为18.

Currently in one of the biggest tables in my DB we have the max length as 18 and its 18 throughout the table for rowid.

谢谢.

当前，下面的代码块已迭代并用于多个表，因此为了使代码灵活，而无需在我们使用ROWID的查询中引入定义每个表的PK的需要.

Currently the below block of code is iterated and used for multiple tables hence in-order to make the code flexible without introducing the need of defining every table's PK in the query we use ROWID.

select rowid from table_name ... where ....;

delete from table_name where rowid = selectedrowid;

我认为，由于在选择并使用了rowid之后，不再存储该rowid以备将来使用，因此可以在这种特定情况下使用它.

I think as the rowid is picked and used then and there without storing it for future, it is safe to use in this particular scenario.

请参考以下答案: 使用ROWID是否安全?在Oracle中定位行/记录?

我会拒绝.如果例如应用程序临时存储ROWID(例如，生成一个可选择项的列表，每个项都用ROWID标识，但是该列表会定期重新生成而不存储)，则这可能是安全的.但是，如果以任何持久方式使用ROWID，都是不安全的.

推荐答案

在给定的Oracle版本中，物理ROWID具有固定大小，它不依赖于表中的行数.它由数据文件的编号，此文件内的块的编号以及此块内的行的编号组成.因此，它在整个数据库中都是唯一的，并且可以直接访问块和行，而无需任何进一步的查找.

A physical ROWID has a fixed size in a given Oracle version, it does not depend on the number of rows in a table. It consists of the number of the datafile, the number of the block within this file, and the number of the row within this block. Therefore it is unique in the whole database and allows direct access to the block and row without any further lookup.

随着IT领域的发展持续增长，可以肯定的是格式会在未来发生变化.

As things in the IT world continue to grow, it is safe to assume that the format will change in future.

除了体积之外，还发生了结构上的变化，例如可移动表空间的出现，这使得有必要将对象号(=表/分区/子分区的内部号)存储在ROWID内.

Besides volume there are also structural changes, like the advent of transportable tablespaces, which made it necessary to store the object number (= internal number of the table/partition/subpartion) inside the ROWID.

或者是索引组织表的出现(@ ibre5041提到)，该表看起来像一个表，但实际上只是一个没有这种物理地址的索引(因为索引中的事物在不断移动).因此，有必要引入可存储物理和基于索引的ROWID的UROWID.

Or the advent of Index organized tables (mentioned by @ibre5041), which look like a table, but are in reality just an index without such a physical address (because things are moving constantly in an index). This made it necessary to introduce UROWIDs which can store physical and index-based ROWIDs.

请注意，ROWID可能会发生变化，例如，如果该行从一个表分区移至另一表分区，或者对该表进行了碎片整理以填补许多DELETE留下的空缺.

Please be aware that a ROWID can change, for instance if the row moves from one table partition to another one, or if the table is defragmented to fill the holes left by many DELETEs.

这篇关于尝试找出Oracle中Rowid的最大长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！