Database Synopsis for Distinct Value Approximation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face challenges in accurately estimating the number of distinct values (NDV) in large databases due to inefficient sampling methods, which can lead to inaccurate statistics and high resource consumption, especially when dealing with large sample sizes or non-randomly distributed data.
Innovation Solution
The approach involves generating a synopsis by mapping column values to a domain using a uniform hash function, splitting the synopsis when it reaches capacity to approximate the NDV, and merging synopses to derive global statistics, allowing for incremental maintenance and reduced resource usage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Use of energy by moving object
If sampling methods are used to estimate NDV, then resource consumption is reduced, but measurement precision deteriorates
Solution Approach 1:
The patent divides the database into multiple partitions and processes each partition separately to build synopses. This segmentation allows the system to work with smaller data subsets, reducing memory requirements and resource consumption while maintaining accurate NDV estimation through the combination of partition-level synopses.
Solution Approach 2:
The patent introduces synopses as intermediary data structures that summarize distinct values from database partitions. These synopses act as mediators between the raw database data and the final NDV estimation, enabling accurate measurements without processing the entire dataset, thus reducing resource consumption while preserving precision.
2Measurement precision
If large sample sizes are used to improve NDV estimation accuracy, then measurement precision improves, but resource consumption increases
Solution Approach 1:
Instead of processing a large sample from the entire database at once, the patent segments the database into partitions and processes each partition separately. This allows accurate NDV estimation to be achieved by combining results from multiple smaller partition samples, reducing the total quantity of data that needs to be processed while maintaining or improving precision.
Solution Approach 2:
The patent processes only the necessary portions of the database (individual partitions) rather than requiring a large comprehensive sample. By focusing on partial processing of partitions and combining results, the system achieves accurate NDV estimation without the resource cost of processing excessively large samples from the entire database.
3Measurement precision
If traditional aggregation operations are performed on the entire table, then measurement precision improves, but productivity deteriorates
Solution Approach 1:
The patent divides the database table into multiple partitions and performs aggregation operations on each partition separately rather than on the entire table. This segmentation dramatically improves productivity by reducing the data volume that needs to be processed for each aggregation operation, while the combination of partition-level results maintains measurement precision.
Solution Approach 2:
The patent performs preliminary aggregation operations on individual partitions before combining results. This preliminary action on smaller data subsets improves productivity by avoiding the need to process the entire table at once, while still achieving accurate statistics through the subsequent combination of partition-level aggregations.
4Productivity
If partition pruning is used to improve query performance, then productivity improves, but reliability deteriorates when statistics are inaccurate
Solution Approach 1:
The patent replaces traditional expensive aggregation operations with a more efficient synopsis-based mechanism. Synopses provide the necessary statistical information for partition pruning and query optimization without requiring full aggregation operations, thus improving productivity while maintaining the reliability needed for accurate execution plan generation.
Solution Approach 2:
The patent performs preliminary synopsis construction on database partitions before query execution. This preliminary action provides accurate statistical information that enables reliable partition pruning and query optimization, ensuring that both productivity and reliability are maintained by having statistics ready in advance rather than computing them during query execution.
Data Source
AI summary
A method and apparatus for approximating a database statistic, such as the number of distinct values (NDV) is provided. To approximate the NDV for a portion of a table, a synopsis of distinct values is constructed. Each value in the portion is mapped to a domain of values. The mapping function is implemented with a uniform hash function, in one embodiment. If the resultant domain value does not exist in the synopsis, the domain value is added to the synopsis. If the synopsis reaches its capacity, a portion of the domain values are discarded from the synopsis. The statistic is approximated based on the number (N) of domain values in the synopsis and the portion of the domain that is represented in the synopsis relative to the size of the domain.