SQL Query NDV Estimation for Hash Table Capacity Allocation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies in managing hash table capacity during aggregation queries due to inaccurate estimation of the number of distinct values (NDV), leading to frequent adjustments that cause performance degradation and resource wastage.
Innovation Solution
Implement a machine learning model for NDV estimation in the database system kernel, using sampled data and a cache mechanism to quickly and accurately determine the hash table capacity based on user queries, thereby reducing the need for frequent adjustments.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Stability of the object's composition
If the hash table capacity is set to be large to avoid frequent adjustments, then the stability of hash table capacity is improved, but memory overflow risk increases
Solution Approach 1:
The patent changes the parameter of hash table capacity from a fixed large value to a dynamically determined value based on NDV estimation. The system estimates the number of distinct values (NDV) for the query condition and sets the hash table capacity accordingly, allowing the capacity to adapt to different query scenarios rather than using a uniformly large capacity that causes memory overflow.
Solution Approach 2:
The patent performs preliminary NDV estimation before creating the hash table to determine the appropriate capacity in advance. By estimating the number of distinct values that will be processed during the aggregation query, the system can pre-allocate the optimal hash table capacity, avoiding both frequent adjustments and memory overflow.
2Quantity of substance
If the hash table capacity is set to be small to reduce memory usage, then memory consumption is reduced, but the frequency of capacity adjustment increases
Solution Approach 1:
The patent determines hash table capacity as a parameter based on NDV estimation rather than using a fixed small value. The estimated NDV provides an optimal capacity that balances memory consumption and query processing efficiency, allowing the system to allocate just enough memory for the expected number of distinct values without frequent adjustments.
Solution Approach 2:
The system performs preliminary NDV estimation to determine the optimal hash table capacity before processing the aggregation query. This preliminary action allows the system to allocate memory efficiently from the start, avoiding both waste and the need for frequent capacity adjustments during query execution.
3Device complexity
If traditional methods are used to determine hash table capacity, then system complexity is kept simple, but NDV estimation accuracy is insufficient
Solution Approach 1:
The patent introduces an intermediary NDV estimation mechanism that bridges the gap between simple system design and accurate capacity determination. The system uses sampling techniques and statistical estimation to calculate the number of distinct values, providing accurate NDV estimates without requiring complex full-data analysis or manual configuration.
Solution Approach 2:
The patent uses sampling methods to estimate NDV by analyzing a partial subset of data rather than processing the entire dataset. This partial action provides sufficient accuracy for capacity determination while keeping the system complexity manageable, avoiding the need to analyze all data rows in detail.
Data Source
AI summary
Embodiments of the present disclosure provide a method and apparatus for database query, an electronic device, and a medium. The method includes: determining, in response to receiving a structured query language (SQL) query for a database, a query condition and a target column of the SQL query that relates to a number of distinct values (NDV) estimation; selecting, from sampled data of the database, data of the target column that meets the query condition; generating feature data of the selected data of the target column; and determining an estimated NDV of the target column based on the feature data using a model for NDV estimation.