What is a chained row? Is a row that is too large to fit into a single database data 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:
1- Tables whose row size exceeds the blocksize
2- Tables with long and long raw columns are prone to having chained rows
3- Tables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces.
u can take a look in a report a metric called “Table Fetch Continued Row”, this metric give a general
indication of database migrated/chained rows. When chaining becomes excessive, additional
I/O is required to service a query, and the DBA should increase PCTFREE for the table and
reorganize the table to remove the chains
How to indentify?
One way to identify chained rown in a specific table is using script
utlchain.sql ($ORACLE_HOME\RDBMS\ADMIN), this script create a table called
CHAINED_ROWS where store the ROWID, table name, scheme of all chained rows.
the procedure is:
1. Run script utlchain.sql (creation of table CHAINED_ROWS)
2. Identify chained riows executing command:
ANALYZE TABLE DWH.DT_IND_OPERARIOS_DIA LIST CHAINED ROWS; <– USE DBMS_STAT
3. List results with next query:
WHERE table_name = ‘[TABLE_NAME]’;
Once identify the rows you can proceed to fix problem:
1. Create a table for insert chained rows, is easy cause you have ROWID.
Example of Query:
SQL> CREATE TABLE temp_table AS SELECT * FROM [table_name]
WHERE rowid in (SELECT head_rowid FROM chained_rows);
2. Delete row from [table_name] you can use next command:
DELETE FROM [table_name] WHERE rowid in (SELECT head_rowid FROM chained_rows);
3. Inser again the rows:
INSERT INTO [table_name] SELECT * FROM temp_tablel;