HOW TO RESOLVE ROW-CHAINING

We know the row-chaining happen,when a row that is too large cannot fit on a single block.

Eg: If we have a 2k blocksize but our row size is 4k, we will use 3 blocks to store that row in pieces.

Row-chaining can occur because of below reasons:

1)A table have a long/long raw Data types in some cases
2) A table's rowsize exceeds the blocksize and
3)Any table with more then 255 columns

We can resolve ROW-CHAINING by following below 2 steps:

Step:1.FIND OUT IF ROW-CHAINING IS OCCURING.
$sqlplus scott/tiger
SQL>exec dbms_stats.gather_schema_stat(‘scott’)
OR
SQL>exec dbms_stat.gather_table_stat('scott','emp')
SQL>select chaint_cnt from user_tables where TABLE_NAME='EMP';


NOTE: Any non-zero value means there is row-chaining.

Step 2:RESOLVE THE CHAINING

 RUN THE FOLLOWING COMMAND TO CREATE chained_rows table.

$sqlplus "/as sysdba"

SQL>@$ORACLE_HOME/rdbms/admin/utlchain.sql

SQL>ANALYZE TABLE EMP LIST CHAINED ROWS INTO CHAINED_ROWS;

SQL>create table temp_emp as
Select * from emp
Where rowid in (select HEAD_ROWID from chained_rows
where TABLE_NAME= ‘EMP’;

SQL>DELETE FROM EMP WHERE ROWID IN(
select HEAD_ROWID from chained_rows
where TABLE_NAME= ‘EMP’);

SQL>INSERT INTO EMP SELECT * FROM TEMP_EMP;

SQL>DROP TABLE TEM-_EMP;
SQL>DROP TABLE CHAINED_ROWS;

Note:Remember,ROW CHAINING occurs because of UPDATE operations and not because of INSERT operations.

Hope it helps...

Happy Oracle DBA learning

Best regards,

Rafi.

.