Maintain a Current UPDATE STATISTICS

The operations of the OnLine optimizer depend upon the data that is collected from runs of the SQL UPDATE STATISTICS command. This command looks at the distribution of data in the tables and indexes and stores it internally. Based upon this information, the optimizer decides the best way to execute your SQL statements. If the statistics information is stale or nonexistent, you could get SQL statements that perform poorly.

Get your users and developers into the habit of running an UPDATE STATISTICS FOR TABLE tablename every time they create a new table, create a new index, or significantly modify the distribution of data within a table. If you already have your users trained in the use of the SET EXPLAIN ON command in SQL and they are optimizing their own queries, be sure that they run the UPDATE STATISTICS before they do the SET EXPLAIN ON.

It is also a good procedure to have UPDATE STATISTICS in your UNIX cron file to be run occasionally. Once a night is not too much. Since UPDATE STATISTICS when invoked without a FOR TABLE clause works on a database level, you first need to feed the program the information about the names of your existing databases. This script is named update_statistics:

#!/bin/csh
echo "Please wait.........."
foreach db ('$INFORMIXLOCALBIN/find_db_names')
echo "TIME: 'date' UPDATING STATISTICS FOR DATABASE: $db"
$INFORMIXDIR/bin/isql $db << EOF
update statistics
EOF
end

This script includes an echo statement that can be removed if you want it to operate silently. I usually prefer to run the script with a redirection of the output to a logfile so that I can verify when the last updates were done. This information is not easily available unless you keep track of it yourself.