ROW CHAINING AND ROW MIGRATION

HOW TABLE DATA IS STORED

• When a table is created, a segment is created to hold its data
• A tablespace contains a collection of segments.
• A row is ultimately stored in a database block in the form of a row piece.

ROW CHAINING:

• Normally, we encounter row chaining when the size of the row is larger than the size of the database block used for storing it. 
• In this situation, the row is split across more than one database block.
• When this row needs to be accessed, more than one database blocks are traversed, resulting in greater I/O.
• To avoid row chaining we can create larger size data block table space.

Non-default data block tablespace creation:

Before we start we need to alter an initialization parameter. Assuming the default block size is 8096 KB.

SQL>ALTER SYSTEM SET DB_16K_CACHE_SIZE=16M SCOPE=BOTH;

We can understand row chaining by following steps:


STEP 1

Create a new table ROW_CHAIN

 SQL> CREATE TABLE SCOTT.ROWS_CHAIN
(
Id number not null primary key,
  Field1 char(2000) default 'A' not null,
  Field2 char(2000) default 'B' not null,
  Field3 char(2000) default 'C' not null,
  Field4 char(2000) default 'D' not null,

);

STEP 2

INSERT VALUES IN TABLE

INSERT INTO SCOTT.ROW_CHAIN (ID) SELECT ROWNUM FROM SYS.DBA_OBJECTS WHERE ROWNUM<101;

STEP 3

GATHER STATISTICS FOR THE TABLE


SQL>ANALYZE TABLE SCOTT.ROW_CHAIN COMPUTE STATISTICS;

STEP 4

CHECK IF ROW CHAINING IS THERE


SQL>SELECT CHAIN_CNT FROM ALL_TABLES WHERE OWNER='SCOTT' AND TABLE_NAME='ROW_CHAIN';

 CHAIN_CNT
----------

       100

STEP 5

CREATE A TABLESPACE WITH DIFFERENT BLOCK SIZE

SQL> CREATE TABLESPACE TS_16K BLOCKSIZE 16K DATAFILE 'TS_16K.DBF'  SIZE 30M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

STEP 6

MOVE TABLE ROW_CHAIN IN NEWLY CREATED TABLESPACE

SQL> ALTER TABLE SCOTT.ROW_CHAIN MOVE TABLESPACE TS_16K;

STEP 7

REBUILD INDEX OF TABLE ROW_CHAIN

SQL> ALTER INDEX SCOTT.PK_ROW_CHAIN REBUILD;

STEP 8

NOW REFRESH THE TABLE STATISTICS

SQL>ANALYZE TABLE SCOTT.ROW_CHAIN COMPUTE STATISTICS;

STEP 9

NOW CHECK FOR THE ROW CHAINING

SQL>SELECT CHAIN_CNT FROM ALL_TABLES WHERE OWNER='SCOTT' AND TABLE_NAME='ROW_CHAIN';

 CHAIN_CNT

----------

         0

TO CHECK ROW CHAINING IN ANY TABLE

SQL> SELECT owner, table_name, NVL(chain_cnt,0) "Chained Rows"  FROM all_tables WHERE owner NOT IN ('SYS','SYSTEM') AND NVL(chain_cnt,0) > ORDER BY owner, table_name;

ROW MIGRATION

In row migration, Oracle Database moves the entire row to a new data block, because in block there is not enough size to fit in the block after updation the entire row will migrated to another block and and the original row piece of a migrated row contains a pointer of forwarding address to the new block containing the migrated row. The rowid of migrated row does not change.

Row migration is depends on the pct_used and pct_free of oracle block.

When varchar2 datatype's value will be updated and if there is insufficient space is available in pct_free then row chaining will takes place.

Avoid Row Chaining

To avoid row migration we can move table in another tablespace.