| < Day Day Up > |
|
Chained rows in Oracle Database are as a result of row migration or row chaining. Row chaining occurs when a row is too large for a single block and thus the row is "chained" or spread across multiple blocks. Row migration occurs when updating increases the row size, prohibiting the whole row from fitting in a single block. Thus, the row is migrated or moved elsewhere leaving only a pointer in the original block. It is possible that a row migration can lead to a row chain as well if the row becomes large enough. A lot of row chaining in a database is a big problem. Row chains are removed by table reconstruction, recreating the table, or by using export and re-import. Row chaining and migration can be minimized by leaving more empty space in each block when first inserting rows: set PCTFREE to a higher value. PCTFREE leaves empty space in a block by reserving more space for future updates. The downside to increasing PCTFREE is that rows will be less compacted and there will be fewer rows per block on average. High row to block density leads to faster access time because there are more rows read per block. The approach is dependent on requirements and behavior required by applications.
SELECT 'Chained Rows ' "Ratio" , ROUND( (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table fetch continued row') / (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN ('table scan rows gotten', 'table fetch by rowid')) * 100, 3)||'%' "Percentage" FROM DUAL; Ratio Percentage -------------- ---------- Chained Rows 0%
The chain count in my Accounts schema is negligible.
| < Day Day Up > |
|