Notes from: All about chained rows @ Ask Tom

Before jumping into the topic, we need to first clarify some concepts:

• A row that doesn’t fit into a block A needs to be “chained” with another block B.
• Now, if there was another row in the block A then it needs to be “migrated” to another block when block A fills up.
• a Full table scan example is SELECT * FROM t
• a query using an index is for example SELECT * FROM t WHERE t.col1 = 1 (it uses the index on col1 to find the row where t.col1 = 1)

Migrated rows and their impact on:

• Full table scan: are not affected by migrated rows since it picks up data while reading the disks
• Querying migrated rows with index (instead of full table scan): go through the ROWID pointer (that was left behind when a row was migrated) to find the data it needs so it adds 1 to Table Fetch Continue Row count.

Chained rows and their impact on:

• Full table scan: since we can’t find the data in one place only, we will need an additional read to find all the data
• Querying chained rows with index (instead of full table scan): idem.

Conclusion
Chained rows are inevitable so there’s not much we can do unless the data model could be changed so that fewer data is stored – but that’s a functional problem. On the other hand, if we have lots of migrated rows that each of them would perfectly fit in one datablock, then we could potentially reorganize the table to reduce the additional disk lookup.