Privacy policy. Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile.
We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries.
The specific tradeoffs depend on your application. In Object Explorer , click the plus sign to expand the database in which you want to update the statistic. Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases.
This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics with a linear threshold. Skip to main content. This browser is no longer supported. This value can be roughly interpreted to mean that 95 percent of the time, the estimate is equivalent to that obtained from high distributions. You must have the DBA privilege or be the owner of the table to create medium distributions.
Use the HIGH mode option to update the same statistics that you can perform with the low mode and also generate statistics about the distribution of data values for each specified column. The constructed distribution is exact. You must have the DBA privilege or be the owner of the table to create high distributions. If you use the HIGH mode option to update statistics, the database server can take considerable time to gather the information across the database, particularly a database with large tables.
The HIGH keyword might scan each table several times for each column. To minimize processing time, specify a table name and column names within that table. Use the Resolution clause to adjust the size of the distribution bin, designate whether or not to avoid calculating data on indexes, and with the Medium mode, to adjust the confidence level. This can be roughly interpreted as meaning that 95 percent of the time, the estimate produced by the MEDIUM keyword is equivalent to using high distributions.
The minimum confidence level is 0. The maximum confidence level is 0. Literal Number, p. For HIGH mode, the default value is 0. A distribution is a mapping of the data in a column into a set of column values. The contents of the column are divided into bins or ranges, each of which contains an equal portion of the column data. For example, if one bin holds 2 percent of the data, 50 of these 2-percent bins hold all the data.
A bin contains the particular range of data values that reflects the appropriate percentage of entries in the column. Distributions are constructed for other columns in the list, and the statement does not return an error. The amount of space that the DBUPSPACE environment variable specifies determines the number of times the database server scans the designated table to construct a distribution. This option does not affect existing index information. Use this option to avoid the examination of index information that can consume considerable processing time.
This option does not affect the recalculation of information on tables, such as the number of pages used, the number of rows, and fragment information. The SPL routine must reside in the current database. In an ANSI-compliant database, specify the owner as the prefix to the routine name.
The following table explains the keywords that you can use when you update routine statistics. The sysprocplan system catalog table stores execution plans for SPL routines. Two actions update the sysprocplan system catalog table:. To collect statistics for a column that holds a user-defined data type, you must specify either medium or high mode.
When you use this option, the database server removes the row in the sysdistrib system catalog table that corresponds to the tableid and column. In addition, the database server removes any large objects that might have been created for storing the statistics information. You must have usage permissions on these routines. You can choose to convert the indexes one table at a time or for the entire database at one time.
Follow the conversion guidelines that are outlined in the Informix Migration Guide. Limiting the number of columns distributed speeds the update. Similarly, precision affects the speed of the update.
Element Purpose Restrictions Syntax column. Name of a column in the specified table. The column must exist. Name of synonym for table for which statistics are updated. The synonym and the table to which the synonym points must reside in the current database. Name of table for which statistics are updated.
The table must reside in the current database. Usage You cannot update the statistics used by the optimizer for a table or UDR that is external to the current database. Updating Statistics for Tables Although a change to the database might obsolete the corresponding statistics in the systables , syscolumns , sysindexes , and sysdistrib system catalog tables, the database server does not automatically update them.
0コメント