Follow the conversion guidelines in the Informix Migration Guide. The term many modifications is relative to the resolution of the distributions. The statement does not update the syscolumns and sysindexes tables. Any information about indexes, the syscolumns, and the sysindexes tables in the following pages does not apply to Enterprise Decision Server. Use the ONLY keyword to collect data for one table in a hierarchy of typed tables. If you do not specify the ONLY keyword and the table that you specify has subtables, the database server creates distributions for that table and every table under it in the hierarchy.
Figure Because neither of the previous examples mentioned the level at which to update the statistical data, the database server uses the low mode by default. If pages are found with the delete flag marked as 1 , the corresponding keys are removed from the B-tree cleaner list. This operation is particularly useful if a system crash causes the B-tree cleaner list which exists in shared memory to be lost. For information on the B-tree cleaner list, see your Administrator's Guide.
Use the LOW mode option to generate and update statistical data regarding table, row, and page count statistics in the systables system catalog table.
In Dynamic Server, the LOW mode option updates index and column statistics for specified columns also. The database server generates and updates this statistical data in the syscolumns , and sysindexes tables. When you use the low mode, the database server generates the least amount of information about the column. If you do not specify any columns, the database server removes all the distribution data for that table. You must have the DBA privilege or be the owner of the table to use this option.
As the example shows, you drop the distribution data at the same time you update the statistical data that the low mode option generates. Use the MEDIUM 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 database server places distribution information in the sysdistrib system catalog table.
The constructed distribution is statistically significant. When you use the MEDIUM mode option, the data for the distributions is obtained by sampling a percentage of data rows. Because the data obtained by sampling is usually much smaller than the actual number of rows, this mode executes more quickly than the HIGH mode. Because the data is obtained by sampling, the results might vary that is, different sample rows might produce different distribution results. If the results vary significantly, you can adjust the resolution percent or confidence level to obtain more consistent results.
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.
Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. How to run update statistics manually for informix table which is having composite indexes? Ask Question.
Asked 2 years, 1 month ago. Active 2 years, 1 month ago. Viewed 1k times. Improve this question. Jonathan Leffler k gold badges silver badges bronze badges. Nwn Nwn 2 2 gold badges 7 7 silver badges 27 27 bronze badges. Add a comment. Active Oldest Votes. The Informix Test it — YMMV. Improve this answer. Jonathan Leffler Jonathan Leffler k gold badges silver badges bronze badges. Any deference with update stat or same? The documentation shown does not distinguish between unique and duplicate non-unique indexes.
On the face of it, the same rules apply. However, I should warn you that I've not got field experience with these rules and I may be missing some nuances. Frank R. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password.
0コメント