What is Row Chaining and row Migration in oracle Database ?

What is Row Chaining and row Migration in oracle Database ?
===========================================================

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 8KB block size for your database and you need to insert a row of 16KB into it, Oracle will use 2/3 blocks and store the row in chain of data blocks for that segment. And Row Chaining happens only when the row is being inserted.

you can show the block size of database by

SQL> conn / as sysdba
SQL> show parameter db_block_size


What is Row Migration
---------------------

Row Migration Occurs when a row that originally fitted into one data block is updated so
that the overall row length increases, and the block's free space is already
completely filled. In this case, Oracle migrates the data for the entire row
to a new data block, assuming the entire row can fit in a new block. Oracle
preserves the original row piece of a migrated row to point to the new block
containing the migrated row: the rowid of a migrated row does not change.



How can identified Row Chaining and row migration
------------------------------------------------

there are three ways :-

1) by Analyze command
2) USER_TABLES
3) V$SYSSTAT

You can show here from follows example :-




SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 8 18:06:54 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> set linesize 1000
SQL> set pagesize 1000
SQL>
SQL>
SQL>
SQL> conn stlbas@juncls
Enter password:
Connected.
SQL>
SQL>
SQL> @G:\oracle_new\DB10G2\APP1\BEFTN\RDBMS\ADMIN\utlchain.sql

Table created.

SQL>
SQL> desc CHAINED_ROWS ;
Name
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

OWNER_NAME
TABLE_NAME
CLUSTER_NAME
PARTITION_NAME
SUBPARTITION_NAME
HEAD_ROWID
ANALYZE_TIMESTAMP

SQL> select count(*) from CHAINED_ROWS ;

COUNT(*)
----------
0


SQL> SELECT owner, table_name, chain_cnt
2 FROM user_tables
3 WHERE chain_cnt > 0
4* and owner = 'STLBAS'
SQL>
SQL> /

no rows selected

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

no rows selected

SQL>


=====================================================================
=========From above we cannot find any row chaining====================
=========below we create a table of row chaining=======================
=====================================================================


SQL> drop table chain_row_test ;

Table dropped.

SQL>
SQL> create table chain_row_test (a varchar(4000),b varchar(4000), c varchar(4000));

Table created.

SQL>

SQL> insert into chain_row_test (a,b,c) values ( 1, rpad('*',40000000,'*'), rpad('*',2300000,'*') )
SQL>
SQL>
SQL> /

1 row created.

SQL> commit ;

Commit complete.

SQL>
SQL>
SQL>
SQL> SELECT count(*) FROM chained_rows;

COUNT(*)
----------
0

SQL>
SQL> analyze table chain_row_test list chained rows into chained_rows ;

Table analyzed.

SQL>
SQL> SELECT count(*) FROM chained_rows;

COUNT(*)
----------
1

SQL>
SQL> select dbms_rowid.rowid_block_number(rowid) from chain_row_test;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
508711

SQL>
SQL> insert into chain_row_test (a,b,c) values ( 1, rpad('*',5620000000,'*'), rpad('*',55500000,'*') )
SQL> /

1 row created.

SQL>
SQL>
SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select dbms_rowid.rowid_block_number(rowid) from chain_row_test;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
508708
508711

SQL>
SQL>
SQL> SELECT count(*) FROM chained_rows;

COUNT(*)
----------
1

SQL>
SQL> analyze table chain_row_test list chained rows into chained_rows ;

Table analyzed.

SQL>
SQL> SELECT count(*) FROM chained_rows;

COUNT(*)
----------
3

SQL> select dbms_rowid.rowid_block_number(rowid) from chain_row_test;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
508708
508711

SQL>
SQL>
SQL>
SQL>
SQL> ANALYZE TABLE chain_row_test LIST CHAINED ROWS;

Table analyzed.

SQL> SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';
NAME VALUE
---------------------------------------------------------------- ----------
table fetch continued row 3


SQL>
SQL>
SQL> SELECT table_name, initial_extent, num_rows, blocks, empty_blocks, avg_space,
2 chain_cnt, avg_row_len
3 FROM user_tables
4 WHERE table_name = 'CHAIN_ROW_TEST'
5 /

TABLE_NAME INITIAL_EXTENT NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
------------------------------ -------------- ---------- ---------- ------------ ---------- ---------- -----------
CHAIN_ROW_TEST 65536 5 16 3 4852 3 4826

SQL>


SQL>
SQL> EXEC dbms_stats.gather_table_stats('STLBAS','CHAIN_ROW_TEST');

PL/SQL procedure successfully completed.

SQL>



======================================================================
=================we can find which rows are chained by follows query==
======================================================================



SQL> SELECT a, SUBSTR (b, 1, 10), SUBSTR (c, 1, 10)
2 FROM chain_row_test
3 WHERE ROWID IN (SELECT head_rowid
4 FROM chained_rows);

A
-----------------------------------------------------------------------

SUBSTR(B,1 SUBSTR(C,1
---------- ----------
1
********** **********

1
********** **********

1
********** **********


SQL>
SQL>
SQL>






How to avoid and eliminate Chained/Migrated rows ?
====================================================

1-ALTER TABLE ... MOVE command.

You can MOVE the table by using ALTER TABLE ... MOVE statement that enables you to relocate data of a non partitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace for which you have quota. This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE. You can also use the ALTER TABLE ... MOVE statement with the COMPRESS keyword to store the new segment using table compression.

Note : Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE,and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must bedropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected aftermoving the table.


2-Increase PCTFREE.

The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block. For example, assume that you specify the following parameter within a CREATE TABLE statement.
If the PCTFREE has been set to a low value, that means there is not enough room in the block for updates. To avoid migration,all tables that are updated should have their PCTFREE set so that there is enough space within the block for updates.You need to increase PCTFREE to avoid migrated rows. If you leave more free space available in the block for updates, then the row will have more room to grow.

3- Import/Export can be taken as an approach to eliminate the migrated rows.

4- Avoid to create tables with more than 255 columns.