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

• NULL columns take up 0 bytes. Setting a NULL column to a not NULL value makes it take up space.
• VARCHAR2 columns use only as many bytes as there are characters in the string. Eg. Updating 'Fred' to 'Barney' uses an extra 2 bytes.
• NUMBER and DATE columns consume a variable amount of space. Dates can use 7 or 8 bytes depending on the time component, and large numbers or high precision numbers use more space. eg. The number 2 uses two bytes, whereas 2.8727862 uses six bytes.

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.

• If the chaining is a result of a once-only UPDATE, say adding and populating a new column, then the DBA can just rebuild the table and the problem will go away.
• If the table contains a lot of VARCHAR2 or one or two large VARCHAR2 columns that are often updated to larger strings, then you will need to increase the table's PCTFREE value. See your DBA.
• If you have a table where you insert a minimal row with mostly NULL columns and then populate them with UPDATE statements, then you may be able to fix this by updating the PCTFREE attribute. However this is a bad technique and should be avoided. Consider using a Global Temporary Table for a more efficient two-step insert. eg. Insert base details into a Global Temporary Table, then use that data plus data from another source to insert into the target 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.