Pruning Index for Semi-Structured Data via Lazy Reassembly
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, making it desirable to execute queries without scanning the entire table.
Innovation Solution
A pruning index is created to identify a reduced scan set by generating fingerprints for searchable values in a source table, allowing the database system to quickly disqualify non-matching micro-partitions and only scan a subset of the table, supporting both structured and semi-structured data types through indexing transformations and lazy reassembly techniques.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a full table scan is performed to execute queries on very large tables, then complete data coverage is achieved, but query execution time and computing resource consumption increase significantly
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 evaluate indexes at the micro-partition level rather than scanning the entire table, significantly reducing the scan set while ensuring that all relevant data is still examined through the union of selected micro-partitions.
Solution Approach 2:
The patent pre-computes pruning indexes that store metadata about the contents of each micro-partition before queries are executed. These indexes are built in advance and can be used to quickly determine which micro-partitions contain data satisfying query conditions, eliminating the need to scan micro-partitions that definitely do not contain matching data.
2Productivity
If traditional indexing methods are used on semi-structured data, then query performance improves, but manual indexing selection and additional storage requirements increase complexity
Solution Approach 1:
The patent creates a universal pruning index structure that works with semi-structured data types without requiring manual configuration. The index automatically handles various data types and query patterns, providing multi-functional support for different query operations on semi-structured data while eliminating the need for separate indexing strategies for different data types.
Solution Approach 2:
The system automatically generates and maintains pruning indexes for semi-structured data without requiring manual intervention. The index build process self-adapts to the data structure, automatically identifying relevant fields and creating appropriate index entries, thereby eliminating manual indexing selection and reducing operational complexity.
Data Source
AI summary
A pruning index is generated for a source table organized into a set of batch units. The source table comprises a column of semi-structured data. The pruning index comprises a set of filters that index distinct values in each column of the source table. Rather than reassembling an entire tree structure of the semi-structured data prior to indexing, the generating of the pruning index comprises traversing a reassembly hook object that represents a first portion of the semi-structured data that is subcolumnarized and traversing a residual object that represents a second portion of the semi-structured data that is not subcolumnarized. The reassembly hook object is traversed to identify values corresponding to the first portion of the semi-structured data and the residual object is traversed to identify values corresponding to the second portion. The pruning index is stored with an association with the source table.