Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: ROWID and UPDATE statement

Re: ROWID and UPDATE statement

From: Georg Scholz <>
Date: Fri, 30 Apr 2004 12:50:13 +0200
Message-ID: <40922f5f$0$19120$>

Howard, thanks for such a comprehensive answer!

In our case, we only have "normal" tables (no IOT, not partitioned).

Regarding Row migration: If a row grows over a block's borders, and it needs to be split: does the ROWID then change or not?

Best regards


Howard J. Rogers wrote:

> Georg Scholz wrote:

>> Dear all,
>> if I update a row using the UPDATE statement, will the ROWID always be 
>> kept the same?

> Yes, except when it isn't. :-)
> In other words, normal tables always preserve the rowid. That's why,
> indeed, we can suffer the problem of row migration -where the index
> points to the start of the row in one block, but the bulk of the row has
> been moved to another block because of an update. If only we could
> assign a brand new rowid to the row as it grows too big for a block, row
> migration would never be a problem.
> But the rule is, and has always been, that once a row acquires a rowid,
> it is stuck with it for life.
> Except that Oracle started breaking that rule in 8i. It invented the
> 'alter table X move' command, which causes every row to be shifted to
> new blocks -and since the block number is a component part of the rowid,
> every row acquires a new rowid after a move. But moving a table is not
> the same as updating rows within a table, so the exception is probably
> not relevant to your specific question.
> Also in 8i, Oracle introduced a new feature for partitioning. If you
> partition a table by, say, Month, and then update a January record to be
> a February one, that implies the row should be moved from one partition
> to another... and that would require row movement, and hence new rowids
> as a result of a simple update. In 8.0, that simply wasn't allowed
> because of the 'permanent rowid' rule. But in 8i, Oracle broke that rule
> by allowing you to specify the ENABLE ROW MOVEMENT clause when
> specifying your partitions. If that's specified, then rowids can
> change... but for regular non-partitioned heap tables, the old rule of
> permanent rowids still applies.
> Another area where updates do cause row movement is Index Organised
> Tables: because an IOT is really just a b*tree index, then updates do
> cause rows to shift their physical location. That would imply a new
> rowid if IOTs actually had rowids in the first place... but they don't
> (they have UROWIDs instead, which aren't quite the same thing).
> Finally, in 10g, there's the ability to shrink a table, and that implies
> row movement, too... which has to be explicitly permitted (this even for
> regular tables) by first issuing the 'alter table X enable row movement'
> command.
> So, as newer versions are released, more and more exceptions to the
> general rule appear to be creeping in. But unless you are working with
> 10g and are doing shrinks, or 8i and above and are doing moves, or have
> 8i and above and fairly exotic tables (partitioned, IOT etc), then no,
> updates do not cause rowids to change.
> Regards
Received on Fri Apr 30 2004 - 05:50:13 CDT

Original text of this message