Different execution policies for given a given query may have different expenses, and constructive query execution plans for the minimum query execution price is the duties of the database system. Query Optimization is to query the process of selecting the most effective query policy. Search Optimization is to identify an expression with a given expression, but perform a higher expression, and determine the specific algorithm used in the execution of the operation and the specific indexes will be used.
In order to make a selection in many query strategies, the optimizer of the database system must estimate the cost of each query policy, and the number of disk access is often the main criterion for measurement. It is impossible to accurately calculate the price of the policy before performing a query according to a policy, so the optimizer uses statistics in the database system to estimate the price of the query policy. Informix database system These statistics are saved in the sysmaster database,
If you want to maintain an accurate statistical value, whenever the table data is modified, the corresponding statistically must be updated, which will bring a lot of cost, so the Informix system is not updated on the statistics at each modification. Therefore, statistics for selecting query policies are not necessarily true, sometimes inseparable, if the query does not use the index that should be used, that is, the statistics have not been updated. For Informix database systems, these statistics are saved in the Sysmaster database, you can use the update statistics command to update.
The following is information for estimating the price:
Ø
Records
Ø
Number of pages of table space
Ø
Recording length
Ø
Different values of fields
Ø
Distribution of field values
Ø
Index number
Ø
Number of index leaf nodes
Ø
Index B tree depth
Ø
Index is ascending or descending or cluster index
Ø
Number of pages occupied by the index
The optimizer in the Informix database server provides the most effective strategy for the query of the SQL statement, which makes you don't have to consider the table first to search when you connect the query, and you need to use that index.
The system's statistics can be updated by executing the Update Statistics command, making the optimizer get the current latest statistics. When modifying or deleting a table related data, the system's statistics are not updated automatically. For example, if you use the delete command to delete a record in a database table, after deleting the completion of the completion of the record information about the table, the number of NROWS (the number of record lines of the database table) will be discovered. By executing the Update statistics command, you can update the information in the system table Systables, sysdistrib, syscolumns, sysindexes. After running Update Statistics, you will find that the NROWS field in SYSTABLES has been updated. If Update Statistics Medium (HIGH) is executed, the updated data distribution information can also be obtained in the SysDistrib table. So, it is best to execute the Update Statistics operation when you modify the database table a lot. In addition, Update Statistics will force the stored procedure optimization (updated to sysprocpplan). The following is a system table related to Update Statistics:
1, Syscolumns
The database in the database is described, where colmin, colmax stores the secondary and secondary value of the database each table field, which only take effect after this field is indexed and runs Update Statistics. For example, for field values 1, 2, 3, 4, 5, 4 is the secondary value, 2 is a secondary value.
2, sysdistrib:
The data distribution information is stored. This table provides a detailed table field for information to provide an optimizer to optimize the execution of the SQL SELECT statement. This table will be stored in information after Update Statistics Medium (HIGH) is executed. Perform "dbschema-hd" to get the distribution information of the specified table or field
3, SYSINDEXES:
Describes index information in the database. For each index in the database, a record. After modifying the index, only UPDATE STATISTICS can be reflected in the table. At the same time, the value of the Clust is also updated, and between the number of data pages of the table and the number of database records.
4, SYSTABLES:
Update NROWS data by performing Update Statistics
Update Statistics has the following three levels:
1, low:
The default is LOW, and the minimum amount of information about Column is collected. Only content changes in Systables, Syscolumns, SysIndexes do not affect sysdistrib. In order to improve efficiency, the LOW operation is performed on the non-index field.
2, high:
The distribution information constructed at this time is accurate instead of a statistical sense.
Because the time and occupation of the CPU resource, you can only perform the HIGH operation on the table or field. For very large tables, the database server will scan all the data for each field. You can configure the dbupspace environment variable to determine the largest system disk space available.
3, Medium:
Sampling data distribution information, so the required time is less than high
When should I perform Update StStistics?
It is recommended to perform the Update Statistics action in the following:
A large number of changes have been made to the data. For data distribution, if the data distribution does not have a significant change, you can do not change the database table with the relevant stored procedures, avoid re-optimizing the database upgrade after the runtime stored procedure. Method for completing the transformation of the index Update StStistics
Considering the speed performance factor, implement Update Statistics recommended methods:
Table execution: Update statistics Medium for Table #### Distributions ONLY
Execute the first field of each index: Update Statistics HIGH
Execute the composite index: Update Statistics Low
Execute the fields used in the non-index field but in the conditions when necessary
Update Statistics HIGH
operating
