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.
situations for chain & 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.
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.
I love dba job
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@example.com
Rohit Nirkhe,Oracle DBA,OCP
Thanks a Lot Fellows.
Thanks again, It clears the Concept of Row Migration and Row Chaining.
Click Here to Expand Forum to Full Width