When a Row Migrates and When Chain???

Hi,

I want to know under what Circumstances a Row Chains and Migrates. I know what is Row Migration and Chaining. But want to know the Reason, when Migrates and when Chains. Is there a way to distinguish between Row Migration and Row Chaining. I Know that in DBA_TABLES, You can see the Column Chain_Cnt after Analyzing but what about Row Migration.

Please Elaborate... Thanks in Advance.

Row Migration and Row chaining represents the same problem.

When a new row is inserted and the block scheduled does not have enough space , the row is migrated to a new block(or set of contiguous) .Size db_block_size properly.

When a row is updated and there is not enough space to expand in a block , the row is migrated to a new block .Size pctfree properly for this kind of a problem.

You can solve chaining problem and that is why u see chained_cnt but row migration can only be solved by resizing db_block_size.

Take Care
GP

Hi

Migration:-

sometimes parts of a single table row inhabit more than one block. If a modification increases a row's size beyond the capacity of its original data block, oracle8 moves the row to another block and maintain a pointer to row's new location in its original data block.

Chaining:-

Row chaining, in contrast occurs when a table row its too large to fit in any database block.

In either case , this "one row , many block" situation forces oracle8 to perform extra physical I/O to access to chained or migrated rows.

HTH.

VR.Murugappan

Hi,
Row chaining and Row migration always digrades ur performance.Basically row chaining happens when ur entire row canot fit into 1 data block.So the row gets chained into multiple blocks.Row chaining happens when u insert a row and pctused is not set properly.Row Migration happens when u try to update a row and that row cannot fit into the same data block so the row gets transferred to a new datablock with sufficent space.Row Migration happens because of low PCTFREE.
After analysing the table,the chain_cnt column in dba_tables contains both migrated and chained rows.In case of any help write to me at [email protected]


Regards,
Rohit Nirkhe,Oracle DBA,OCP
[email protected]

Thanks a Lot Fellows.

Thanks again, It clears the Concept of Row Migration and Row Chaining.