Diffs between Toad and spotlight in chained rows. (4273389)

When analysing chained rows within an oracle database.

Toad reports 116 rows, and spotlight reports 17,245.

This is after running toads fix chained rows utility.

Toad 116 rows returned is correct, but considering the fixed chained rows spotlight would have picked this up the same result as Toad.

How is it possible to force spotlight to reanalyse the tables to get accurate chained rows counts.

Spotlight uses the chain_cnt column from dba_tables, which is only updated when statistics are gathered. If you have not gathered statistics on the table

after fixing the chained rows, you should try that by running the following command:

begin
dbms_stats.gather_table_stats(&OWNER,&TABLE_NAME);
end;

Oracle will then update the dba_tables with the number of chained rows, and Spotlight will reflect this.

This is believed that it is often implemented as a scheduled job to keep the statistics up-to-date.