We hear it all the time that chained or migrated rows are not good. But if someone would challenge you to explain it why, could you do it?

Many times in our career, we take certain things for granted. A DBA once told you, or you read it somewhere that something is or is not good for the database, and therefore from now on, you take on that belief, many times without challenging the idea.

Today’s post is here to explain the chained and migrated rows, so next time, if someone is challenging you to explain it, you can!

1. How Is Space In The Data Block Managed?
2. What Is Row Chaining?
3. Causes And Types Of Row Chaining
4. Impact Of Row Chaining
5. Checking For Row Chaining

1. How Is Space In The Data Block Managed?

Before getting into the subject of row chaining, we need to understand how the space is managed within a data block.
If you are using ASSM (Automatic Segment Space Management), then each block is divided into four sections called FS1, FS2, FS3, FS4.
Each section corresponds to the following:

FS1 – 0 – 25% free space
FS2 – 25 – 50% free space
FS3 – 50 – 75% free space
FS4 – 75 -100% free space

It might be easier to understand a block and the naming convention, if you look at the full% instead of free%

FS4 – 0 – 25% full space
FS3 – 25 – 50% full space
FS2 – 50 – 75% full space
FS1 – 75 -100% full space

Oracle automatically updates the status of the block based on the % free space. If a block is 55% full, or 45% free, that becomes an FS2 block. This way we can determine if a row could fit into a certain block, (if you know the length of the row).

When a block is full, it will no longer allow inserts to happen. As soon as the block has some free space, it becomes candidate for new inserts. To find out what type of blocks you have in your segments, you can use DBMS_SPACE.SPACE_USAGE.

2. What Is Row Chaining?

Row chaining occurs when a row of data (for a table) no longer fits into a single data block.

3. Causes And Types Of Row Chaining

There are two main causes for row chaining:

1) the row of data is too large to start with, in order to fit into a single data block. By example, the length of the row is 10k and the data block size is 8k. There is no way your row will fit into a single data block, when initially inserted. The row will be stored in a chain of data blocks, and this is called row chaining. Usually this happens with rows of data that have columns of type LONG or LONG RAW. In this case row chaining is unavoidable.

2) the row of data that originally fitted in one data block is updated. The new length of the row doesn’t fit into the same data block, as there is not enough free space in the block. In this case the entire row is moved or migrated to a new data block.

The ROWID of the row will not change. The row leaves behind in the original block a “forwarding address” (the address where it will be, after it is migrated). A migrated row is considered also a chained row.

4. Impact Of Row Chaining

Migrated rows do not cause extra work for full table scans, because the whole table is read anyways.
Migrated rows affect the queries that use index reads on that table, because instead of reading one block to get to the row, the database has to read two blocks (the original block that has the ROWID, and the new block where the row got migrated).

5. Checking For Row Chaining

If you are curious to see if any of your tables is affected by migrated rows, you can check the chain_cnt column in dba_tables.

This is a statistic that will have a value of 0 by default. When you gather statistics on the table, using dbms_stats, the statistics are not gathered for chain_cnt, the chain_cnt column will not be populated. This is expected behavior.

In order to populate chain_cnt column of dba_tables for a specific table, you will have to run the ANALYZE command on that table, as in the example below:

ANALYZE TABLE TABLE_A COMPUTE STATISTICS;

SELECT chain_cnt,table_name
FROM dba_tables
WHERE table_name = 'TABLE_A';

Another way of checking if you have row chaining, is to look for the following statistic: table fetch continued row, after you run a select on the last column of the table.

SELECT name,value FROM v$sysstat
WHERE name = 'table fetch continued row';