Row Migration

Hello all,

Let me first write down my understanding of Row Migration -- If PCTFREE is set to a very low value, there may be insufficient space in a block to accommodate a row that grows as a result of an update. When this happens, the oracle server will move the entire row to a new block and leave a pointer from the original block to the new location. The I/O performance associated with this row decreases because the Oracle server must scan two data blocks to retrieve the data.

My Query is -- When Oracle server is migrating the entire row to a new block, it can create a new row directory slot in the new block for the migrated row and delete the row directory slot in the previous block from where the row has been migrated. This can be done as Oracle server is not storing any information of that row in the previous block. In this manner Oracle server can be saved from accessing two data blocks to retrieve the data, thereby increasing/keeping the I/O performance at the same level.

One of the possible answers I think is, the indexes(if any) present on the data object should be rebuilt as the ROWID changes when we create a new row directory slot for the migrated row, which might take a substantial amount of time.

bye,
Rahul. Received on Wed Jul 18 2007 - 21:51:08 CDT