row migration and row chaining in oracle

What is row migration

We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently).  A migration means that the entire row will move and we just leave behind the forwarding address. So, the original block just has the rowid of the new block and the entire row is moved.

Impact of row migration

1) There is no impact on Full table scan as forwarding address are ignored as we know that we will read all the blocks and will eventually read the row

2) Index Read on a migrated row will cause additional IO’s as index will say to go to a particular rowid which will forward you to exact row resulting in extra I/O

What is Row chaining
Row chaining happens when a row is too large to fit into a single database block. For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces. Some conditions that will cause row chaining are: Tables whose rowsize exceeds the blocksize. Tables with LONG and LONG RAW columns are prone to having chained rows. Tables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces. So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks

Impact of row chaining
1) It impact both the full table scan and index scan but it depends on the data required.


Both the row migration and row chaining impact performance as extra I/O need to be performed.

The statistics for row migration and row chaining is counted through “table fetch continued row”

SELECT, b.value
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
AND lower( = ‘table fetch continued row’;

How many Rows in a Table are chained?
The USER_TABLES tells you immediately after an ANALYZE (will be null otherwise) how many rows in the table are chained.

SELECT chain_cnt,
round(chain_cnt/num_rows*100,2) pct_chained,
avg_row_len, pct_free , pct_used
FROM user_tables
WHERE table_name = ‘TEST_CHAIN’;

———- ———– ———– ———- ———-
3         100        3691         10         40

PCT_CHAINED shows 100% which means all rows are chained or migrated.

List Oracle Chained Rows
You can look at the chained and migrated rows of a table using the ANALYZE
statement with the LIST CHAINED ROWS clause. The results of this
statement are stored in a specified table created explicitly to accept
the information returned by the LIST CHAINED ROWS clause. These results
are useful in determining whether you have enough room for updates to
Creating a CHAINED_ROWS Table
To create the table to accept
data returned by an ANALYZE … LIST CHAINED ROWS statement, execute the
UTLCHAIN.SQL or UTLCHN1.SQL script in $ORACLE_HOME/rdbms/admin. These
scripts are provided by the database. They create a table named
CHAINED_ROWS in the schema of the user submitting the script.
create table CHAINED_ROWS (
owner_name         varchar2(30),
table_name         varchar2(30),
cluster_name       varchar2(30),
partition_name     varchar2(30),
subpartition_name  varchar2(30),
head_rowid         rowid,
analyze_timestamp  date
After a CHAINED_ROWS table is created, you specify it in the INTO clause of the ANALYZE statement.
SELECT owner_name,
FROM chained_rows
OWNER_NAME                     TABLE_NAME                     HEAD_ROWID
—————————— —————————— ——————
SCOTT                          TEST_CHAIN             AAAPVIAAFAAAAkiAAA
SCOTT                          TEST_CHAIN             AAAPVIAAFAAAAkiAAB

How to avoid Chained and Migrated Rows?
PCTFREE can help to avoid migrated rows. If you leave more free space
available in the block, then the row has room to grow. You can also
reorganize or re-create tables and indexes that have high deletion
rates. If tables frequently have rows deleted, then data blocks can have
partially free space in them. If rows are inserted and later expanded,
then the inserted rows might land in blocks with deleted rows but still
not have enough room to expand. Reorganizing the table ensures that the
main free space is totally empty blocks.