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
Georg
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
> HJR
>
>
Received on Fri Apr 30 2004 - 05:50:13 CDT
