Estimation Algorithm for Partitioned Table Uniqueness
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In partitioned database tables, determining the exact count of distinct values across multiple partitions is expensive and generates substantial runtime traffic, making it costly in terms of performance, especially for operations like joins.
Innovation Solution
An estimation algorithm is applied to generate a uniqueness metric for a column split across multiple partitions, classifying it as categorical or non-categorical, and assigning appropriate estimation factors to calculate a cost estimate for system resources required for database operations, thereby optimizing the processing demands.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If the exact count of distinct values is determined across multiple partitions, then measurement precision is improved, but productivity deteriorates due to expensive operations and substantial runtime traffic
Solution Approach 1:
The patent uses inexpensive estimation algorithms (such as sampling-based methods or heuristic calculations) to approximate distinct value counts instead of performing expensive exact calculations. These estimation operations are computationally cheap and can be executed quickly, sacrificing some precision for significant performance gains in database operations like joins.
Solution Approach 2:
The patent performs distinct value count estimations in advance (prior to actual database operations) and caches these results for reuse. By pre-calculating and storing estimation results, the system avoids repeating expensive calculation operations during runtime, thereby improving overall database operation performance.
2Measurement precision
If partition reading is performed to obtain exact distinct value counts, then measurement precision is improved, but loss of time increases due to excess reading operations
Solution Approach 1:
The patent applies partial action by reading only a subset of partition data (sampling) rather than reading all partition data to compute exact distinct values. This partial reading approach provides sufficient estimation accuracy for database optimization purposes while dramatically reducing the time and I/O operations required compared to complete partition scans.
Data Source
AI summary
An estimation algorithm can generate a uniqueness metric representative of data in a database table column that is split across a plurality of data partitions. The column can be classified as categorical if the uniqueness metric is below a threshold and as non-categorical if the uniqueness metric is above the threshold. A first estimation factor can be assigned to the column if the column is classified as categorical or a larger second estimation factor can be assigned if the column is non-categorical. A cost estimate for system resources required to perform a database operation on the database table can be calculated. The cost estimate can include an estimated total number of distinct values in the column across all of the plurality of data partitions determined using the assigned first estimation factor or second estimation factor and a number of rows in the table as inputs to an estimation function.