Pruning Index Micro-Partition Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Processing queries on very large tables in cloud data warehouses is challenging due to the significant time and computing resources required to scan entire tables, necessitating a method to reduce the scan set for efficient query execution.
Innovation Solution
The creation and use of pruning indexes, which organize data into micro-partitions and utilize filters like blocked bloom filters to identify a reduced scan set of micro-partitions relevant to the query, allowing for efficient pruning and reducing the need to scan non-pertinent data.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If the entire table is scanned to execute queries, then all data can be accessed, but significant time and computing resources are required
Solution Approach 1:
The patent divides the large table into multiple micro-partitions and creates pruning indexes for each micro-partition. This segmentation allows the query system to scan only relevant micro-partitions rather than the entire table, significantly reducing query execution time while maintaining complete data access capability.
Solution Approach 2:
The patent performs preliminary actions by pre-building pruning indexes that contain metadata about the data distribution in each micro-partition. These indexes are constructed in advance and stored, enabling the system to quickly identify relevant micro-partitions during query execution without scanning the entire table.
2Reliability
If the entire table is scanned to execute queries, then all data can be accessed, but significant computing resources are required
Solution Approach 1:
By segmenting the table into micro-partitions with associated pruning indexes, the system reduces the amount of data that needs to be processed during query execution. This segmentation enables selective scanning of only relevant micro-partitions, thereby reducing computing resource consumption and energy usage while maintaining complete data access.
Solution Approach 2:
The patent extracts and stores metadata about data distribution in pruning indexes separately from the main table data. This extraction allows the query system to use the compact indexes to identify relevant data regions without processing the entire table, significantly reducing computing resource requirements.
3Loss of time
If pruning indexes are created to reduce scan set, then query execution time is reduced, but additional data structure overhead is introduced
Solution Approach 1:
The patent creates pruning indexes with local quality by generating specific filter structures tailored to each micro-partition's data characteristics. Each pruning index is optimized for its corresponding micro-partition, containing only the relevant metadata needed for that specific segment, which reduces overall complexity compared to a single global index while still achieving query execution time reduction.
Data Source
AI summary
A source table organized into a set of micro-partitions is accessed by a network-based data warehouse. A pruning index is generated based on the source table. The pruning index comprises a set of filters that indicate locations of distinct values in each column of the source table. A query directed at the source table is received at the network-based data warehouse. The query is processed using the pruning index. The processing of the query comprises pruning the set of micro-partitions of the source table to scan for data matching the query, the pruning of the plurality of micro-partitions comprising identifying, using the pruning index, a sub-set of micro-partitions to scan for the data matching the query.