Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: ROWID and UPDATE statement

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 30 Apr 2004 20:20:16 +1000
Message-ID: <40922859$0$439$afc38c87@news.optusnet.com.au>


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
HJR Received on Fri Apr 30 2004 - 05:20:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US