Database Query Evaluation Using Selective Micro-Partition Decompression
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems inefficiently handle queries on compressed data, as they decompress all data before applying filters, leading to excessive computing and memory resource usage.
Innovation Solution
Implementing a method where only the portion of the data relevant to the query filter is decompressed, with dictionary compression and micro-partitions allowing for efficient query evaluation by decompressing only responsive data, reducing unnecessary data processing and storage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If all compressed data is decompressed before applying filters, then query accuracy is improved, but computing resources and memory usage increase significantly
Solution Approach 1:
The patent divides the compressed data into micro-partitions and further segments them into columnar blocks. This segmentation allows the system to decompress only specific column blocks that are relevant to the query filter, rather than decompressing entire micro-partitions or all data. The columnar organization enables selective decomposition of data segments based on query requirements, reducing unnecessary decompression operations.
Solution Approach 2:
The patent extracts and identifies relevant column blocks from compressed micro-partitions based on query filters before decompression. By extracting metadata about column block relevance and using it to guide decompression, the system takes out only the necessary data portions for processing, avoiding the decomposition of irrelevant data and thereby reducing computing resource consumption.
2Quantity of substance
If all compressed data is decompressed before applying filters, then complete data availability is improved, but memory resources increase significantly
Solution Approach 1:
The patent segments compressed data into micro-partitions and columnar blocks, allowing selective decomposition of only those blocks relevant to the query. This segmentation strategy ensures that memory resources are allocated only to necessary data portions rather than loading entire datasets, optimizing memory utilization while maintaining data availability for query processing.
Solution Approach 2:
The patent applies partial decompression by decompressing only the portion of data needed to satisfy the query filter conditions. Instead of performing excessive decompression of all compressed data, the system decompresses exactly what is necessary, thereby reducing memory resource requirements while ensuring sufficient data availability for accurate query evaluation.
3Reliability
If operations are performed on each entry in decompressed data, then query completeness is improved, but processing time increases
Solution Approach 1:
The patent segments data into micro-partitions and columnar blocks, enabling the system to operate only on relevant segments identified by query filters. This segmentation allows parallel processing of independent blocks and avoids unnecessary operations on irrelevant data, reducing overall processing time while maintaining query completeness through systematic coverage of all necessary data portions.
Solution Approach 2:
The patent performs preliminary identification of relevant column blocks using metadata before actual decompression and processing. This preliminary action filters out irrelevant data blocks in advance, so that subsequent processing operations are applied only to necessary data portions, thereby reducing processing time without compromising query completeness.
Data Source
AI summary
Data in a micro-partition of a table is stored in a compressed form. In response to a database query on the table comprising a filter, the portion of the data on which the filter operates is decompressed, without decompressing other portions of the data. Using the filter on the decompressed portion of the data, the portions of the data that are responsive to the filter are determined and decompressed. The responsive data is returned in response to the database query. When a query is run on a table that is compressed using dictionary compression, the uncompressed data may be returned along with the dictionary look-up values. The recipient of the data may use the dictionary look-up values for memoization, reducing the amount of computation required to process the returned data.