The DB2 optimizer uses the table and index statistics from the DB2 catalogs to determine the optimal access path. If the table statistics do not reflect the data in the table, the optimizer may decide to perform a table scan instead of using indexes.
This can be very expensive depending on the size of the table.
A quick and easy way to look at the statistics is to query the "sysibm.systables" catalog table for the specific table you are interested in and look for the values in columns CARD, NPAGES.
A value of -1 indicates that statistics have not been collected from the table/index and runstats needs to be run on the table.
If the values differ significantly from the number of rows in the table - once again a "runstats" may be necessary to update the catalog statistics.
Once the runstats process has been run, the bind should be performed again so that the optimizer takes advantage of the new statistics.
Thursday, January 15, 2009
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment