Row Migration and Chaining

- Row Chaining : When a row is inserted into a table exceeds the size of the database block, the row will spill over into two or more blocks. When row spans into multiple blocks is called row chaining. It cause bad performance because multiple blocks must be read to return a single row
- Row Migration : When previously inserted row is updated and that row causes the to grow larger than the space available, causes row migration.
- dba_tables is used to identify row chaining and migration

Solution for row migration:
- For row migrations can be corrected by merely deleting and then reinserting the migrated rows.
- This can be done by
* export, drop or truncate and then reimport the table
* Use the ALTER TABLE ...MOVE command to rebuild the table
* Identify and then reinsert the migrated rows.

Solution for row Chaining:
Method 1 :
- Analyze the sales table

SQL> ANALYZE TABLE sales COMPUTE STATISTICS;

SQL> SELECT table_name, chain_cnt
     FROM dba_tables
     WHERE table_name = 'SALES';
  
    TABLE_NAME                    CHAIN_CNT
    ------------------------------------------
    SALES                                10

Method 2 :
- use the utlchain.sql in the $ORACLE_HOME/rdbms/admin directory
- ANALYZE TABLE sales LIST CHAINED ROWS;
- Copy chained rows into temporary table and copy it back to original table