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: row migaration

Re: row migaration

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 27 Dec 2005 18:25:56 -0800
Message-ID: <1135736756.619350.252290@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 Tue Dec 27 2005 - 20:25:56 CST

Original text of this message

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