Database Optimizer Statistics via Check Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems face inefficiencies in determining optimizer statistics, especially for large tables, as current methods are resource- and time-consuming, leading to potential selection of inefficient search strategies due to inaccurate extrapolations.
Innovation Solution
The system efficiently determines optimizer statistics by utilizing check tables and cardinality information to update statistics for fields associated with check tables, allowing for accurate and rapid calculation without impacting system performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If optimizer statistics are determined by reading each row of a large table, then measurement precision is improved, but loss of time and use of energy worsen
Solution Approach 1:
The system performs preliminary actions by maintaining check tables that store pre-computed statistics about the distribution of values in foreign key fields. When statistics are needed for query optimization, the system can directly query these pre-prepared check tables rather than scanning the entire base table, thus obtaining accurate statistics efficiently.
Solution Approach 2:
The patent introduces check tables as intermediary structures between the base table and the optimizer. These check tables act as mediators that store summarized statistical information, allowing the optimizer to obtain accurate statistics without directly accessing and scanning the entire base table, thus reducing time and energy consumption.
2Loss of time
If optimizer statistics are determined by sampling rows from large tables, then loss of time is reduced, but measurement precision worsens
Solution Approach 1:
The system pre-computes and stores complete statistical information in check tables during data insertion or update operations. This preliminary action ensures that when the optimizer needs statistics, accurate data is already available without requiring sampling or extrapolation, thus maintaining high measurement precision while minimizing query time.
Solution Approach 2:
Check tables serve as intermediary structures that maintain accurate statistical summaries of the base table data. Rather than sampling the base table directly, the optimizer queries these intermediary check tables which contain pre-computed, accurate statistics, thus eliminating the need for sampling while keeping time consumption low.
3Measurement precision
If check tables are maintained for all fields, then measurement precision is improved, but device complexity worsens
Solution Approach 1:
The system applies local quality by creating check tables selectively only for foreign key fields where statistical accuracy is most critical for query optimization. Not all fields require check tables - only those that are frequently used in joins or where value distribution significantly impacts query planning. This selective approach maintains measurement precision where needed while avoiding unnecessary complexity.
Solution Approach 2:
The system changes the parameter of check table creation from a universal approach (all fields) to a conditional approach based on field characteristics. Check tables are created only for fields meeting specific criteria (e.g., foreign key fields with high selectivity), thus reducing the total number of check tables and system complexity while maintaining statistical accuracy for the most important fields.
Data Source
AI summary
A database system may provide determination of statistics for a field of a database table. The determination may include determination of whether the field of the table is associated with a check table, and, if the field of the table is associated with a check table, determination of a number of distinct values of the field in the table based on a number of distinct values of the field in the check table.