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.
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.
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 firstname.lastname@example.org