728x90
Question
What is the recommended way of running UPDATE STATISTICS in a database so that the optimizer will have the information it needs to run the most efficiently? This article includes a utility to produce an SQL command file that runs all of the UPDATE STATISTICS command recommended for a particular database.
Answer
You can make sure that the optimizer has all of the data it needs to make your queries more efficient by running the SQL command UPDATE STATISTICS on your database.
1. Run UPDATE STATISTICS LOW on all tables in the database.
2. Run UPDATE STATISTICS MEDIUM on all columns which are in an index, but are not the first column of any index.
3. Run UPDATE STATISTICS HIGH on all columns which are the first column in an index.
4. Run UPDATE STATISTICS on all stored procedures.
This should be done any time that there are significant changes made to the distribution of the data.
To make the task of running all of these UPDATE STATISTICS statements easier you could put them into a single SQL command file.
makeupdate.sql
Here is a command file named makeupdate.sql that can be used to generate an SQL command file that runs the recommended UPDATE STATISTICS commands for your particular database.
To use the makeupdate.sql script follow these steps:
1. Copy the file makeupdate.sql.txt to a working directory and change the name to makeupdate.sql
2. Run the makeupdate.sql command file using dbaccess.
Example:
If your database is named mydb and the makeupdate.sql command file is in your current directory then you can run it by entering this at the command line:
dbaccess mydb makeupdate.sql
The SQL commands in makeupdate.sql produce an SQL command file named update_stats.sql in the current directory.
To complete the UPDATE STATISTICS process you must run this SQL command file using dbaccess.
Example:
If your database is named mydb and the update_stats.sql command file is in your current directory then you can run it by entering this at the command line:
dbaccess mydb update_stats.sql
You may also consider the Auto Update Statistics (AUS) maintenance system available in IBM™ Informix Dynamic Server version 11.50 and later. Refer to the topic "Automatic statistics updating" in the System Administration section of the Informix 11.50 information center (see Related URL section)
You can make sure that the optimizer has all of the data it needs to make your queries more efficient by running the SQL command UPDATE STATISTICS on your database.
- 1. Run UPDATE STATISTICS LOW on all tables in the database.
2. Run UPDATE STATISTICS MEDIUM on all columns which are in an index, but are not the first column of any index.
3. Run UPDATE STATISTICS HIGH on all columns which are the first column in an index.
4. Run UPDATE STATISTICS on all stored procedures.
This should be done any time that there are significant changes made to the distribution of the data.
To make the task of running all of these UPDATE STATISTICS statements easier you could put them into a single SQL command file.
makeupdate.sql
Here is a command file named makeupdate.sql that can be used to generate an SQL command file that runs the recommended UPDATE STATISTICS commands for your particular database.
To use the makeupdate.sql script follow these steps:
- 1. Copy the file makeupdate.sql.txt to a working directory and change the name to makeupdate.sql
2. Run the makeupdate.sql command file using dbaccess.
Example:
If your database is named mydb and the makeupdate.sql command file is in your current directory then you can run it by entering this at the command line:
dbaccess mydb makeupdate.sql
The SQL commands in makeupdate.sql produce an SQL command file named update_stats.sql in the current directory.
To complete the UPDATE STATISTICS process you must run this SQL command file using dbaccess.
- Example:
If your database is named mydb and the update_stats.sql command file is in your current directory then you can run it by entering this at the command line:
- dbaccess mydb update_stats.sql
You may also consider the Auto Update Statistics (AUS) maintenance system available in IBM™ Informix Dynamic Server version 11.50 and later. Refer to the topic "Automatic statistics updating" in the System Administration section of the Informix 11.50 information center (see Related URL section)
출처:
728x90