Dictionary-Based Join Aggregation Using Dense Grouping Keys
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database management systems face performance degradation due to inefficient processing of large fact tables and dimension tables during join and aggregation operations, particularly with hash join methods that require significant memory resources.
Innovation Solution
The use of dense grouping keys and column vectors with dictionaries to pre-process dimension table rows, allowing for efficient filtering and aggregation by creating a dense grouping key data structure that maps join key values, reducing the need for extensive memory usage and improving processing efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If hash join is used to perform join and aggregation operations on large fact tables and dimension tables, then the join operations can be completed with a standard algorithm, but the memory resources required increase significantly and processing efficiency deteriorates
Solution Approach 1:
The patent segments the dimension table into multiple blocks that can be processed independently. Each block is processed separately to generate partial aggregation results, which are then combined. This segmentation reduces the memory footprint by avoiding the need to load the entire dimension table into memory simultaneously, thus resolving the contradiction between completing join operations and reducing memory resource consumption.
Solution Approach 2:
The patent performs preliminary processing of the dimension table by pre-computing aggregation results for each block before the actual join operation. This preliminary action includes generating summary statistics and intermediate aggregation results that can be stored in a more space-efficient format, reducing the memory requirements for the subsequent join operation while ensuring accurate results.
2Reliability
If hash join is used to process large fact tables, then the join can be performed systematically, but the processing time increases and overall performance deteriorates
Solution Approach 1:
The patent divides the large fact table processing into multiple parallel segments that can be processed concurrently. By segmenting the work and distributing it across multiple processing units, the patent maintains systematic processing while reducing the time required for each individual processing step, thus resolving the contradiction between systematic processing and processing time.
Solution Approach 2:
The patent performs preliminary filtering and aggregation on the fact table before the main join operation. By pre-processing the fact table to identify and filter relevant rows based on join keys and aggregation requirements, the patent reduces the volume of data that needs to be processed in the main join operation, thereby reducing processing time while maintaining systematic processing integrity.
3Reliability
If conventional join methods are used on large databases, then the operations can be performed with standard algorithms, but the computational resources required increase significantly
Solution Approach 1:
The patent segments both the fact table and dimension table processing into manageable blocks that can be handled with reduced computational resources. Each block processing operation requires significantly less memory and computational power than processing the entire tables simultaneously, while the segmented results are combined to produce the final accurate output, thus resolving the contradiction between operation execution and computational resource requirements.
Solution Approach 2:
The patent performs preliminary aggregation and filtering operations on both tables before the main join. This preliminary action pre-computes intermediate results that reduce the complexity and resource requirements of the subsequent join operation, allowing the system to execute reliable join operations with significantly reduced computational resources.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
Techniques are described herein for performing join and aggregation operations for a received query using column dictionaries. In an embodiment, a query is received that requests to aggregate a measure column of a fact table, stored in storage data units, based on an aggregate function and join the fact table with a dimension table on a join key column. For a storage unit having column dictionaries and corresponding column vectors for each column, the DBMS may generate a dictionary-grouping key mapping based on the fact join key dictionary and dense grouping keys from the dimension table. Based on the generated dictionary-grouping key mapping and the fact join key vector, the DBMS aggregates a column vector in that data storage unit that corresponds to the measure column in the received query.