Oracle ROWID作为函数/过程参数 [英] Oracle ROWID as function/procedure parameter

问题描述

我只是想听听有关将ROWID类型用作任何函数或过程的输入参数的不同意见.

I just would like to hear different opinions about ROWID type usage as input parameter of any function or procedure.

我通常使用主键作为输入参数，但是使用ROWID作为输入参数是否有某种缺点?我认为这很简单，并且如果在WHERE子句中使用，选择起来也很快.

I have normally used and seen primary keys used as input parameters but is there some kind of disadvantages to use ROWID as input parameter? I think it's kind a simple and selects are pretty quick if used in WHERE clause.

例如:

FUNCTION get_row(p_rowid IN ROWID) RETURN TABLE%ROWTYPE IS...

推荐答案

来自

物理行标识提供对给定表的一行的最快访问.它们包含一行的物理地址(向下到特定的块)，并允许您在单个块访问中检索该行. Oracle保证只要该行存在，它的rowid就不会改变.

Physical rowids provide the fastest possible access to a row of a given table. They contain the physical address of a row (down to the specific block) and allow you to retrieve the row in a single block access. Oracle guarantees that as long as the row exists, its rowid does not change.

ROWID的主要缺点是，尽管它通常是稳定的，但在某些情况下可以更改:

The main drawback of a ROWID is that while it is normally stable, it can change under some circumstances:

• 表已重建(ALTER TABLE MOVE ...)
• 明显地导入/导出
• 启用行移动的分区表

• The table is rebuilt (ALTER TABLE MOVE...)
• Export / Import obviously
• Partition table with row movement enable

主键逻辑上标识行，即使在执行删除+插入操作后，您也始终会找到正确的行. ROWID物理地标识行，而不像主键那样持久.

A primary key identifies a row logically, you will always find the correct row, even after a delete+insert. A ROWID identifies the row physically and is not as persistent as a primary key.

您可以在单个SQL语句中安全地使用ROWID，因为Oracle将保证结果是一致的，例如，删除表中的重复项.为了安全起见，我建议您仅在对行进行锁定(SELECT ... FOR UPDATE)时才使用ROWID accross语句.

You can safely use ROWID in a single SQL statement since Oracle will guarantee the result is coherent, for example to remove duplicates in a table. To be on the safe side, I would suggest you only use the ROWID accross statements when you have a lock on the row (SELECT ... FOR UPDATE).

从性能的角度来看，主键访问会稍微贵一些，但是通常只有在执行很多单行访问时，您才会注意到这一点.但是，如果性能至关重要，那么在这种情况下，与使用rowid进行单行处理相比，使用集处理通常会获得更大的收益.尤其是，如果数据库和应用程序之间有很多往返，那么与往返成本相比，行访问的成本将可以忽略不计.

From a performance point of view, the Primary key access is a bit more expensive but you will normally notice this only if you do a lot of single row access. If performance is critical though, you usually can get greater benefit in that case from using set processing than single row processing with rowid. In particular, if there are a lot of roundtrips between the DB and the application, the cost of the row access will probably be negligible compared to the roundtrips cost.

这篇关于Oracle ROWID作为函数/过程参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！