Table Statistics
Use this page to view information about table statistics and to change the options for automatically updating statistics.
The database server updates statistics automatically according to a predefined schedule and a set of expiration policies. The Auto Update Statistics (AUS) maintenance system runs the appropriate UPDATE STATISTICS statements to optimize query performance.
- Auto Update Statistics Options
- Prerequisite: To display the options that control automatically updating statistics, the OpenAdmin Tool (OAT) for Informix® requires IBM® Informix 11.70.To change the options for the table or index, change the values for these fields and click Save.
- Statistics level
- Specify the level of granularity of the column distribution statistics: table, fragment, or auto. Auto specifies that the database server determines whether fragment-level statistics are created.
- Minimum change threshold
- Specify the minimum percentage of change
that is required for the statistics for the table or index to be considered
stale. Only the statistics that are considered stale are updated.
For example, when the minimum change threshold is 10% and 10% of the
table changes, the statistics that are considered stale are updated.
- Use the system setting
- Select this option to use the threshold that is set for the database server with the STATCHANGE parameter in the onconfig file. The default value is 10%. The current system setting from the onconfig file is displayed.
- Set the threshold
- Select this option to set a specific threshold for this table or index only. The system setting in the onconfig file is not changed.
- Table Statistics
- Name
- The name of the index or column.
- Type
- The type of object in the list: index or column.
- Mode
- The update statistics mode: high, medium, or low. For indexes, the mode is low. For columns, the mode is medium or high. The number of rows that the database server scans depends on the mode and the confidence level. In high mode, the database server scans all rows in the table. In medium mode, the database server determines the number of rows to scan based on the confidence level. The higher the confidence level, the higher the number of rows that the database server scans.
- % Change
- The percent of changed rows in the index or column since the previous calculation of the distribution statistics. The percent change is the UDI counter divided by the number of rows in the table the last time the distribution statistics were calculated. If the percent change exceeds the minimum change threshold for the table, a warning icon is displayed and the column is highlighted in red. This information is displayed for Informix 11.70.
- UDI Change
- The number of UPDATE, DELETE, and INSERT operations on the rows in the index or column since the previous calculation of the distribution statistics. This information is displayed for Informix 11.70.
- Build Date
- The date and time when the statistics for the index or column were last recorded.
- Build Duration
- The time required to calculate the statistics for the index or column. This information is displayed for Informix 11.70.
- Sample Size
- For columns, in medium mode, the minimum number of rows to sample for calculating the column distribution statistics.
- Resolution
- For columns, in medium or high mode, the percent of values in each of the distribution bins.