Database Query Optimizer Using Hash-Based Unique Value Sampling
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Optimizing query execution plans in relational database systems for large volumes of data is challenging due to the lack of accurate resource utilization, especially in decision support and data mining applications where the penalty for a suboptimal plan is high, and existing methods rely heavily on statistical data that may not account for unique value distributions effectively.
Innovation Solution
The method involves calculating the number of rows per unique value for columns in a database table and using a hash function to record and manage unique values in a data structure, optimizing the execution plan based on these calculations to reduce resource utilization and improve performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If query optimization relies on known statistics and metadata, then execution plans can be generated, but the accuracy of resource utilization estimation is insufficient leading to suboptimal performance
Solution Approach 1:
The system performs preliminary sampling of data rows to calculate rows per unique value statistics before the main query execution. This advance preparation of statistical information enables more accurate cost estimation and better execution plan selection, resolving the contradiction between having execution plans and achieving accurate resource utilization estimation.
2Reliability
If the database system samples data to determine unique value distribution, then statistical confidence for optimization improves, but the time and resources required for sampling increase
Solution Approach 1:
The system samples a portion of the data rows rather than examining all rows to determine unique value distribution. This partial action provides sufficient statistical confidence for query optimization without incurring the time cost of complete data scanning, thus resolving the contradiction between reliability and time loss.
3Reliability
If the optimizer uses calculated rows per unique value to compare execution plans, then resource-conserving plans can be identified with greater confidence, but the complexity of the optimization process increases
Solution Approach 1:
The system introduces a new parameter (rows per unique value) that transforms the optimization process from qualitative comparison to quantitative analysis. By changing the parameter basis for comparison, the system achieves more reliable plan selection while the added complexity is offset by the automated calculation and straightforward integration into existing cost models.
Data Source
AI summary
A method, computer program, and database system are disclosed for optimizing a query of a table based on one or more values in one or more columns of the table. The method includes repeating the following steps for a series of rows in the table and optimizing an execution plan for the query based at least in part on a calculated number of rows per unique value. One or more values for the one or more columns are read. The read values are determined to be different from all previously read values in some cases. When the read values are different, the one or more values are recorded in a data structure corresponding to the result of applying a hash function to the one or more values.