Pruning Index Micro-Partitioning for Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Processing queries on very large tables is challenging due to the significant time and computing resources required to scan the entire table to identify data that satisfies the query, necessitating a more efficient approach to reduce scanning efforts.
Innovation Solution
The creation of a pruning index that organizes data into micro-partitions and uses probabilistic data structures like blocked bloom filters to quickly identify pertinent micro-partitions for scanning, thereby reducing the scan set and optimizing query processing.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If a full table scan is performed to identify data satisfying the query, then all data can be examined to ensure query accuracy, but significant time and computing resources are consumed
Solution Approach 1:
The patent segments the large table into multiple micro-partitions based on prefix ranges of the indexed column. Each micro-partition contains data with similar prefix values, allowing the query processor to scan only relevant micro-partitions that could contain matching data, rather than scanning the entire table. This segmentation dramatically reduces the scan set while maintaining query accuracy.
Solution Approach 2:
The patent performs preliminary action by pre-computing and storing prefix information for each micro-partition in a pruning index before query execution. This pre-processing creates a metadata structure that enables rapid identification of relevant micro-partitions during query processing, avoiding the need to examine all data during the actual query execution.
2Reliability
If the entire table is scanned to ensure complete data retrieval, then query completeness is maintained, but computing resources are significantly consumed
Solution Approach 1:
The table is divided into micro-partitions with associated prefix ranges stored in the pruning index. The query processor uses these prefix ranges to identify and scan only the minimal subset of micro-partitions that could contain data satisfying the query predicates, ensuring completeness while minimizing resource consumption.
Solution Approach 2:
The pruning index acts as an intermediary structure between the query and the underlying table data. It contains pre-computed prefix information that mediates the query processing by quickly identifying relevant data regions, thereby reducing the need to examine unrelated data and lowering computing resource consumption.
3Loss of time
If traditional indexing methods are used to speed up query processing, then query execution time is reduced, but the index structure becomes complex and difficult to maintain
Solution Approach 1:
The patent segments the indexing task by creating micro-partitions based on prefix ranges rather than using complex traditional index structures like B-trees or hash indexes. Each micro-partition is associated with a simple prefix range in the pruning index, making the overall structure easier to understand, maintain, and optimize compared to traditional complex indexing methods.
Data Source
AI summary
A table organized into a set of batch units is accessed. A set of N-grams are generated for a data value in the source table. The set of N-grams include a first N-gram of a first length and a second N-gram of a second length where the first N-gram corresponds to a prefix of the second N-gram. A set of fingerprints are generated for the data value based on the set of N-grams. The set of fingerprints include a first fingerprint generated based on the first N-gram and a second fingerprint generated based on the second N-gram and the first fingerprint. A pruning index that indexes distinct values in each column of the source table is generated based on the set of fingerprints and stored in a database with an association with the source table.