Skip to content

How to Reduce Chained Row

  • by
The chained row is a row which is too large to fit into one single data block when it is first inserted. In row chaining, Oracle Database stores the data for the row in a chain of one or more data blocks reserved for the segment.

Row chaining most often occurs with large rows. Examples include rows that contain a column of data type LONG or LONG RAW, or a row with a huge number of columns. Row chaining in these cases is unavoidable.

Row chaining is affected by the following factors:
  1. Data block size may be too small to accommodate most of the row length.
  2. PCTFREE may be too small to allow current rows to grow.
  3. Average row length is larger than it was used to be.

Table Reorganization

Tables with many chained rows may degrade the performance due to data fragmentation, you can perform a table reorganization to reduce it.

Some tables cannot significantly reduce chained rows because of their nature of containing LONG, LONG RAW or with thounsands of columns, etc.

Increase PCTFREE

For a long term, you should raise PCTFREE on specific table, which may improve update performance, because Oracle does not need to chain row pieces as frequently, if ever. (Managing Space for Schema Objects) But be sure that more space is available for later growing before increasing the parameter.

You can use the following statement to move table and change PCTFREE in the same command:
SQL> ALTER TABLE <NAME> MOVE PCTFREE 40;
(See Oracle 12c documentation also : Logical Storage Structures)

For more information about the difference between PCTFREE and PCTUSED, you may refer to my post below:
What is Difference Between PCT_FREE and PCT_USED

Leave a Reply

Your email address will not be published. Required fields are marked *