Pruning Index for Pattern Matching Query Scan Set Reduction
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Processing queries on very large tables in databases is challenging due to the significant time and computing resources required to scan the entire table, necessitating a method to reduce the scan set for efficient data retrieval.
Innovation Solution
The creation and use of a pruning index, which organizes data into micro-partitions and employs probabilistic data structures like blocked bloom filters to identify a reduced scan set of micro-partitions that contain relevant data, thereby accelerating query execution by pruning non-pertinent partitions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If the entire table is scanned to identify data satisfying the query, then all relevant data can be found, 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 processor to work with smaller, manageable units rather than scanning the entire table at once, thereby reducing query execution time while maintaining result completeness.
Solution Approach 2:
The patent performs preliminary action by pre-computing pruning indexes that store metadata about the data in each micro-partition before queries are executed. These indexes are built in advance and can be quickly consulted during query processing to identify relevant micro-partitions, eliminating the need to scan irrelevant portions of the table during actual query execution.
2Reliability
If the entire table is scanned to ensure all matching data is found, then query accuracy is maintained, but computing resources are significantly consumed
Solution Approach 1:
The pruning indexes are pre-computed and stored, containing information about which micro-partitions contain which values. This preliminary computation allows the system to quickly determine relevant micro-partitions during query execution without re-scanning data, thereby maintaining query accuracy while significantly reducing computing resource consumption during actual queries.
Solution Approach 2:
The patent extracts only the necessary information from the full table data and stores it in pruning indexes. These indexes contain metadata about value locations in micro-partitions rather than the actual data itself, allowing the system to identify relevant data locations without processing the entire table, thus reducing computing resource usage while maintaining result accuracy.
3Productivity
If a pruning index is created to reduce the scan set, then query execution time is reduced, but additional data structures and processing overhead are introduced
Solution Approach 1:
The pruning index is segmented by micro-partition, with each index entry associated with a specific micro-partition. This segmentation allows the index to be organized in a hierarchical manner that mirrors the physical data organization, making the index easier to manage and process despite the large amount of data it covers.
Solution Approach 2:
The pruning index structure is designed to support multiple query types and predicate operations universally. The same index structure can be used for equality queries, range queries, and pattern matching queries, reducing the need for multiple specialized index structures and thereby managing complexity while maintaining versatility.
Data Source
AI summary
A query directed at a source table organized into a set of batch units is received. The query includes a pattern matching predicate that specifies a search pattern. A set of N-grams are generated based on the search pattern. A pruning index is used to identify a subset of batch units to scan for matching data based on the set of N-grams generated for the search pattern. The pruning index indexes distinct N-grams in the source table. The query is processed by scanning the subset of batch units.