Metadata Index Subtable for Columnar Storage Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current relational database management systems face inefficiencies in managing partitions, particularly in accessing column-partitioned tables, leading to excessive use of CPU and I/O resources due to the need to read and decompress unqualified data blocks and containers during query processing.
Innovation Solution
The implementation of a metadata index subtable that stores summarized information such as partition identifiers, row identifiers, minimum, and maximum column values allows for pre-filtering of qualified and unqualified containers and data blocks, reducing the need to access and decompress unqualified data, thereby optimizing CPU and I/O resource usage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If all data blocks and containers are read during query processing, then complete data access is ensured, but CPU and I/O resources are excessively consumed
Solution Approach 1:
The patent applies preliminary action by creating and maintaining a metadata index subtable that stores summarized information (min/max values, row identifiers, partition identifiers) about column values in containers before query execution. During query processing, this pre-computed metadata is used to identify and eliminate unqualified data blocks and containers before actual data reading occurs, thus ensuring complete data access is maintained while reducing CPU and I/O resource consumption by avoiding unnecessary data block reads.
2Productivity
If metadata index subtable is implemented, then query performance is improved by eliminating unqualified data blocks, but system complexity increases due to additional metadata management
Solution Approach 1:
The patent applies the extraction principle by separating metadata about column values (min/max values, row identifiers, partition identifiers) from the actual data storage structure and placing it in a dedicated metadata index subtable. This extraction allows the metadata to be independently managed and queried, improving query performance through efficient filtering while containing the added complexity in a specialized, manageable component rather than distributing it throughout the entire storage system.
3Productivity
If column-partitioned tables are used, then query processing for column subsets is optimized, but access to unqualified data blocks cannot be avoided without additional structures
Solution Approach 1:
The patent applies the intermediary principle by introducing a metadata index subtable as a mediator between the query processing system and the column-partitioned table storage. This intermediary structure stores summarized information about column values in containers, allowing the system to quickly identify qualified and unqualified data blocks before accessing the actual columnar data. The metadata index subtable acts as a filtering layer that prevents unnecessary access and decompression of unqualified data blocks, thus maintaining columnar query efficiency while eliminating wasted time.
Data Source
AI summary
A method, apparatus, and computer program product for executing a relational database management system (RDBMS) in a computer system, wherein the RDBMS manages a relational database comprised of at least one column-partitioned base table storing data. Column values from at least one column of the column-partitioned base table are stored in one or more containers spread across one or more data blocks. Metadata comprising summarized information about the column values in the containers is stored in a metadata index subtable. A query with a filtering condition on the column is applied to the metadata index subtable before the column-partitioned base table is accessed, so that only qualified containers and data blocks are accessed, and unqualified containers and data blocks are eliminated, when responding to the query.