What is Row Chaining?
A chained row is a row that is too large to fit into a single database block.
Inserting a row that is larger than the standard block size, (in most cases 8kb), will result in a chained row because the row is too large for the block. This means the row needs to be stored across two database blocks instead of one.
Examples of scenarios that can cause row chaining
- Tables whose rowsize exceeds the blocksize.
- Tables with LONG and LONG RAW data types
- Tables with lots of columns
Chained rows can result in extra IO when reading from a table as instead of having to read a single datablock Oracle needs to read two. Ideally, avoiding such large rows is preferably to reduce IO, but not always possible.
Checking for "table fetch continued row" in v$sesstat can help diagnose chained rows.
Note the comments from the Oracle documentation on "table fetch continued row"
Number of times a chained or migrated row is encountered during a fetch. Retrieving rows that span more than one block increases the logical I/O by a factor that corresponds to the number of blocks than need to be accessed. Exporting and re-importing may eliminate this problem. Evaluate the settings for the storage parameters PCTFREE and PCTUSED. This problem cannot be fixed if rows are larger than database blocks (for example, if the LONG data type is used and the rows are extremely large).
Published 15 August 2023
