Multi-Level Metadata Structure for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face challenges in efficiently managing and querying large amounts of metadata, leading to increased computing resources and time requirements, especially when dealing with vast datasets stored across multiple tables and micro-partitions.
Innovation Solution
The implementation of a multi-level metadata structure that utilizes immutable storage for metadata, allowing for efficient organization and pruning of metadata across micro-partitions, reducing the need to scan entire datasets by using cumulative, grouping, and column expression properties to quickly identify relevant data.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional database systems store and manage metadata in a single level structure, then the database can be accessed and managed, but the computing resources and time required to query and scan metadata increase significantly when dealing with vast datasets
Solution Approach 1:
The patent segments metadata into multiple hierarchical levels (table-level metadata, partition-level metadata, and column-level metadata). This segmentation allows the system to scan only relevant portions of metadata at each level rather than scanning all metadata, significantly reducing computing resources and query response time when dealing with vast datasets distributed across multiple tables and micro-partitions.
2Ease of operation
If database data is stored across multiple tables and micro-partitions to enable efficient data management, then data organization and accessibility improve, but the complexity of managing and querying metadata increases
Solution Approach 1:
The patent introduces a hierarchical dimension to metadata organization, creating multiple levels of metadata abstraction (table-level, partition-level, column-level). This dimensional approach simplifies metadata management by allowing queries to navigate through hierarchical levels rather than managing flat, monolithic metadata structures, making it easier to access and manage data across distributed tables and micro-partitions.
3Reliability
If the entire dataset is scanned to respond to database queries, then all possible data can be examined, but the time and computing resources required increase significantly
Solution Approach 1:
The patent implements preliminary organization of metadata into a hierarchical structure with summary information at each level before queries are executed. This preliminary action enables the query optimizer to quickly identify relevant micro-partitions and columns by scanning only necessary metadata levels, ensuring complete data retrieval while minimizing query processing time and avoiding unnecessary scanning of irrelevant data portions.
Data Source
AI summary
The subject technology stores database data in a table, the table comprising a plurality of micro-partitions each having rows and columns. The subject technology stores metadata for the table in a data structure, the metadata comprising a first level of the data structure comprising a column expression property comprising information about data stored in a column of a micro-partition of the table, a micro-partition expression property comprising information about data stored in the micro-partition of the table, and a second level of the data structure comprising a grouping expression property comprising information about data stored in the micro-partition and one or more additional micro-partitions of the table.