Synopsis Merging for Distinct Value Estimation in Large Databases
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face inefficiencies in accurately estimating the number of distinct values (NDV) in large tables, particularly due to inadequate sampling methods that result in either excessive data reading or reduced randomness, leading to inaccurate statistics.
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 discard less frequently occurring values, and merging synopses from different partitions to estimate the NDV, allowing for incremental maintenance of database statistics.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If row sampling is used to estimate NDV, then randomness of sample is improved, but data reading volume increases
Solution Approach 1:
The patent divides the table into multiple partitions and generates separate synopses for each partition. This segmentation allows the system to process smaller portions of data independently, reducing the total data reading volume while maintaining estimation accuracy through the merging of partition-level synopses into a global synopsis.
Solution Approach 2:
The patent applies block sampling that reads a random sample of blocks and uses all rows in those blocks, rather than reading entire rows for random sampling. This partial action approach reduces data reading volume while still providing sufficient randomness for accurate NDV estimation through the synopsis data structure.
2Quantity of substance
If block sampling is used to estimate NDV, then data reading volume is reduced, but sample randomness decreases
Solution Approach 1:
The patent introduces a synopsis data structure as an intermediary between the sampled data and the NDV estimation. The synopsis captures essential information about distinct values in a compact form, allowing block sampling to reduce data reading volume while the synopsis mechanism preserves estimation accuracy by tracking distinct values efficiently.
Solution Approach 2:
The patent creates a simplified copy of the data distribution through the synopsis structure, which stores representative information about distinct values without requiring the full original data. This copying approach enables accurate NDV estimation from block samples while minimizing data reading requirements.
3Measurement precision
If 100% sample is used for manual statistic collection, then NDV estimation accuracy is improved, but resource consumption increases
Solution Approach 1:
The patent segments the statistic collection process into partition-level synopses that can be generated independently and merged later. This allows accurate NDV estimation through synopsis merging without requiring resource-intensive 100% sampling of the entire table, significantly reducing resource consumption while maintaining accuracy.
Solution Approach 2:
The patent uses synopsis structures that create compact representations of data distributions, serving as efficient copies that enable accurate statistic estimation without processing the full dataset. This copying approach maintains NDV estimation accuracy while avoiding the high resource consumption of complete data scanning.
4Measurement precision
If synopsis capacity is increased to store more values, then NDV estimation accuracy is improved, but memory consumption increases
Solution Approach 1:
The patent segments the synopsis into partition-level components, each storing distinct values for a specific partition. This segmentation allows the global NDV to be estimated by merging smaller partition synopses, improving overall estimation accuracy while keeping individual synopsis memory consumption manageable through distributed storage across partitions.
Data Source
AI summary
A method and apparatus for merging synopses to determine a database statistic, e.g., a number of distinct values (NDV), is disclosed. The merging can be used to determine an initial database statistic or to perform incremental statistics maintenance. For example, each synopsis can pertain to a different partition, such that merging the synopses generates a global statistic. When performing incremental maintenance, only those synopses whose partitions have changed need to be updated. Each synopsis contains domain values that summarize the statistic. However, the synopses may initially contain domain values that are not compatible with each other. Prior to merging the synopses the domain values in each synopsis is made compatible with the domain values in the other synopses. The adjustment is made such that each synopsis represents the same range of domain values, in one embodiment. After “compatible synopses” are formed, the synopses are merged by taking the union of the compatible synopses.