Multi-level Metadata Clustering for Database Query Pruning
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Large databases face inefficiencies in query processing due to the extensive resources required to scan metadata and data, especially when dealing with vast amounts of data, leading to prolonged computation times and resource utilization issues.
Innovation Solution
Implementing a multi-level metadata structure that organizes metadata into groups based on data organization metrics, such as clustering keys, to reduce the amount of metadata loaded during query pruning and other database management tasks, thereby optimizing memory usage and query compilation efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of information
If a traditional flat metadata structure is used to store all metadata, then complete metadata information is available for queries, but the resources required to scan and load metadata increase significantly
Solution Approach 1:
The patent segments metadata into multiple hierarchical levels (first-level metadata at column level, second-level metadata at row level, third-level metadata at cell level). This segmentation allows the system to load only relevant portions of metadata based on query requirements, reducing memory consumption and I/O operations while maintaining access to complete metadata information when needed.
Solution Approach 2:
The patent introduces a hierarchical dimension to the traditional flat metadata structure by organizing metadata into multiple levels with parent-child relationships. This dimensional transformation enables selective loading of metadata at different granularities, allowing the system to balance between information completeness and resource efficiency based on query specificities.
2Measurement precision
If all metadata is loaded into memory during query processing, then query accuracy is improved, but memory usage and processing time increase
Solution Approach 1:
The patent pre-organizes metadata into hierarchical groups and clusters during data loading and maintenance operations. This preliminary structuring enables the query optimizer to quickly identify and load only the necessary metadata subsets required for specific query types, avoiding the need to load all metadata while maintaining query accuracy through selective precision.
Solution Approach 2:
The system implements partial metadata loading based on query characteristics. For simple queries, only essential metadata is loaded; for complex queries requiring higher accuracy, additional metadata levels are loaded as needed. This partial action approach optimizes the balance between query accuracy and processing time by loading exactly what is necessary.
3Ease of manufacture
If metadata is organized without clustering to simplify structure, then ease of implementation is improved, but query pruning efficiency deteriorates
Solution Approach 1:
The patent segments metadata organization into hierarchical levels with clear separation of concerns. Each level (column, row, cell) has its own metadata structures and clustering strategies, allowing independent optimization. This segmentation maintains implementation simplicity through modular design while enabling efficient query pruning at each level through targeted clustering based on query patterns.
Data Source
AI summary
Embodiments of the present disclosure describe systems, methods, and computer program products for improving query processing of a database. An example method can include: storing table data for a table in a plurality of micro-partitions, each micro-partition comprising a portion of the table data for the table; for each micro-partition of the plurality of micro-partitions, storing metadata for the micro-partition in at least one of a plurality of expression properties; and selecting, by a processing device, a subset of the plurality of expression properties to be grouped into a grouping expression property based at least partially on the metadata of the subset of the plurality of the expression properties. The grouping expression property may include cumulative metadata associated with the metadata of the subset of the plurality of expression properties.