Various statistics are compiled for each database instance, such as the number of table entries, the size of the tables and indices, and the value distribution (various values) of indices and columns. This information is required by the Optimizer to define the best strategy for executing complex SQL statements. The statistics information is stored in the database catalog.
If the database size or the values it contains have changed considerably, you have to update the statistics. Do this about once a week.
The statistics values can be updated for certain tables, columns, or for all base tables.
The database instance is in the operational state ONLINE.
Choose Instance ® Tuning ® Optimizer Statistics.
The database system contains an internal list of the tables whose statistics need to be updated.
·
Updating
statistics entries for all tables
You can update the statistics entries for all tables in this internal list. To
do this, do not enter a search argument under
Search;
instead, just enter *.
Updating statistics entries for selected tables
You can update the statistics entries for selected tables in this internal
list. To do this, enter appropriate search arguments for the owner, table
name, and column name under
Search.
Owner |
Owner |
Table Name |
Table name |
Column Name |
Column name |
· You can determine the size of a sample. To do this, enter appropriate information under Estimate.
Use estimate value |
Select this option. |
Rows |
Number of rows to be checked |
Percent |
Percentage of rows to be checked |
The update is only performed across the number of rows or the specified percentage, based on the total number of database tables. This option, therefore, is faster.
The significance of the random sample depends on the size of the tables and the physical position of the data. Measurements have shown that for large tables (more than 1 million entries), samples of more than 20000 rows produce good results.
· Independently of the internal list, you can display all tables for which statistics can be updated. To do this, select Select from tables under Advanced.
1. Choose Actions ® Search.
2.
A list of the
tables is displayed that matches your criteria under Search or
Advanced. From the
Update Statistics Dateand
Update Statistics Time, you can tell when the statistics for this table
were last updated.
If you want to update the statistics for one of the tables in the list,
simply select the relevant entry. If you want to update the statistics for
all of the tables, choose Actions
® Mark All.
Select Include update columns for marked tables (under
Advanced) if you want to update column statistics as well as the table
statistics. You can display the columns of a table by double clicking. Select
the desired columns.
3.
Choose Actions
® Execute to update the statistics for the selected tables.
If you have not selected any tables, the statistics of those tables are
updated that are in the internal list of the database system.