Oracle ANALYZE

VALIDATE STRUCTURE

ANALYZE TABLE mytable VALIDATE STRUCTURE;

CHAINED ROWS

To identify chained rows in a table...

ANALYZE TABLE mytable LIST CHAINED ROWS INTO chained_rows;

SELECT *

FROM chained_rows;

(Create chained_rows using UTLCHAIN.SQL)@$ORACLE_HOME/rdbms/admin/UTLCHAIN.SQL

To count the number of chained rows accessed since database startup...

SELECT SUBSTR(st.name,1,40),

sy.value,

sy.statistic#

FROM v$statname st,

v$sysstat sy

WHERE st.statistic# = sy.statistic#

AND st.name = 'table fetch continued row';

Eliminate Chained Rows

NOTE: this is old documentation that I wrote last century and cut & paste here... there may be better ways of dealing with this now. TODO

If the number of chained rows in a table is small compared to the total number of rows in the table:

  1. Move the rows of data into an intermediate table with the same column structure as the original table by using the HEAD_ROWID in the CHAINED_ROWS table.

  2. DELETE the moved rows of data again by using the HEAD_ROWID in the CHAINED_ROWS table.

  3. INSERT the rows from the intermediate table back into the original table.

  4. DROP the intermediate table.

  5. DELETE the record in the CHAINED_ROWS table.

If the number of chained rows in a table is the majority of rows in the table then one of the two options below is true:

  • DB_BLOCK_SIZE is smaller than the row length (Database Chaining)

Database Chaining requires rebuilding the entire database with a larger block size if possible - or changing the design of the table(s) involved (and all programs, scripts etc that are dependant on them).

  • PCTFREE is incorrect for the table, in which case...

  1. Determine a better percent free factor for the table

  2. Export the entire table with all its dependencies (i.e. indexes, grants, constraints)

  3. DROP the original table

  4. Re-create it with the new specification.

  5. Import the data with its dependencies.