Chaining

Oracle stores table rows in blocks. After a row is inserted into a table, Oracle checks how much space is left in the block. If the amount of space remaining is less than a specified percentage (PCTFREE), then the row is removed from the free-list and new rows will be inserted into the next available block. The free space left in each block is used for UPDATEs.

Updates can increase the size of a row in three ways

If you don't leave enough space at the end of the block, then Oracle must move the row in order to update it - this is called chaining. Oracle migrates the row to another block, and leaves a pointer to the new block in the old block. When you want to read that row, Oracle must first read the old block, and then the new block. This effectively doubles the amount of IO to read that row.

After you analyze a table, check the CHAIN_CNT column in the USER_TABLES or USER_TAB_PARTITIONS view. This is the number of chained rows in the table. If this number is high (more than a 1 or 2% of the rows in the table) then it is a concern. The only way to unchain rows is to rebuild the table or partition.

To prevent chaining from re-occuring, you need to analyze how you are populating the table.

Note that if PCTFREE is set too high, then the table will contain a lot of empty space and Full Table Scans will be slower.


©Copyright 2003