Re: row migaration

Mark,
Thanks for your brilliant answer, many many thanks.

Tony

"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message news:1135736756.619350.252290_at_z14g2000cwz.googlegroups.com...
> No, row migration is where on update the new row size exceeds the space
> available to store the row in the block so Oracle migrates the row to a
> new datablock. The rowid remains the same as Oracle keeps a pointer to
> the new physical row piece location at the original location. This
> means an extra IO is required to fetch the row when the fetch is via
> randon IO since the indexes point to this original slot. When the
> fetches are done via a full table scan Oracle does not bother to fetch
> the row till it hits the physical block that holds the row.
>
> Row movement resutls in the row having a new physical rowid. With a
> normal heap table changing the PK value has no effect on the physical
> location of the row in the table. The PK index entry would have to be
> deleted and re-inserted. But with a indexed organized table the entire
> row would have to be moved to the proper location for the new PK column
> value. Like wise changing of a column that is part of the partion key
> could require that the row be physically moved from its current
> partition to the correct partition for its partition key.
> See the SQL Manual entries for CREATE TABLE, ALTER TABLE, FLASHBACK
> TABLE, and UPDATE for information on row movement.
>
> HTH -- Mark D Powell --
>
Received on Wed Dec 28 2005 - 01:15:12 CST