Query to identify chained rows and how to fix it

Query to identify chained rows and how to fix it

The following query can be used to identify tables with chaining problems:

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

The above query is useful only for tables that have been analyzed. Note the NVL function to replace a NULL with a zero -- tables that have not been analyzed will appear to have been.

The following steps explain how to list all of the chained rows in any selected table:

1. Create a table named CHAINED_ROWS using the following script (taken from Oracle's utlchain.sql 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
   );

2. Issue the ANALYZE command to collect the necessary statistics:

   ANALYZE TABLE  LIST CHAINED ROWS;

3. Query the CHAINED_ROWS table to see a full listing of all chained rows, as shown below:

   SELECT *
     FROM chained_rows
    WHERE table_name = 'ACCOUNT';
   
   Sample Output:
   Owner_name   Table_Name    Cluster_Name    Head_Rowid   Timestamp
   -----------------------------------------------------------------
   QUEST        ACCOUNT       00000723.       0012.0004    30-SEP-93
   QUEST        ACCOUNT       00000723.       0007.0004    30-SEP-93

   The following is an example of how to eliminate the chained rows:

   CREATE TABLE chained_temp AS
       SELECT * FROM
        WHERE rowid IN (SELECT head_rowid
                          FROM chained_rowS
                         WHERE table_name = '');
   DELETE FROM
       WHERE rowid IN (SELECT head_rowid
                         FROM chained_rows
                        WHERE table_name = '');
   INSERT INTO
       SELECT * FROM chained_temp;

4. Drop the temporary table when you are convinced that everything has worked properly.

   DROP TABLE chained_temp;

5. Clean out the CHAINED_ROWS table:

   DELETE FROM chained_rows
         WHERE table_name = '';

Even when you analyze your tables without the LIST CHAINED ROWS option (i.e., ANALYZE COMPUTE STATISTICS;), a column of USER | ALL | DBA_TABLES called CHAIN_CNT stores the number of chained and migrated rows at the time the ANALYZE was run. Likewise, when you use DBMS_STATS to gather statistics in Oracle 9i (remember the ANALYZE command is deprecated for statistics collection in Oracle 9i) the CHAIN_CNT column is populated.

If you are using the rule-based optimizer and you have set OPTIMIZER_MODE to CHOOSE (the default), don't forget to remove the statistics from your tables and indexes using ANALYZE DELETE STATISTICS;. 

You can also obtain the overall number of chained and migrated rows read by your instance since startup time using the V$SYSSTAT table.

SELECT SUBSTR(name, 1, 30) "Parameter", value "Value"
  FROM v$sysstat
 WHERE name = 'table fetch continued row';