Partitioned Column-Major Database Joins for Cache Efficiency
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In database systems, traditional hash join methods face challenges in managing hash table sizes, especially in column-store databases where input records are not materialized, leading to inefficient data movement and increased width of the probe table as more tables are joined.
Innovation Solution
Partitioning only the join-columns, which are the columns containing equality predicates, improves cache and memory utilization by limiting the number and size of partitions, reducing data movement, and using scatter/gather operations for efficient data handling during join operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional hash join methods are used with full table partitioning, then join operations can be performed, but data movement increases significantly and memory/cache utilization deteriorates
Solution Approach 1:
The patent segments the partitioning operation to apply only to join-columns rather than entire tables. This selective segmentation reduces the volume of data that needs to be moved and processed during hash join operations, directly addressing the contradiction between join efficiency and data movement volume.
Solution Approach 2:
The patent extracts and isolates only the necessary join-columns from the full tables for partitioning. By taking out only the essential columns required for the join operation rather than partitioning entire tables, the system reduces memory usage and data movement while maintaining join functionality.
2Productivity
If partitioning is applied to all columns in column-store databases, then hash joins can be executed, but the width of the probe table increases as more tables are joined
Solution Approach 1:
The patent segments the data structure to partition only join-columns separately from other columns in column-store databases. This segmentation prevents the probe table width from increasing proportionally with the number of tables joined, as only the essential join keys are partitioned rather than all columns from all tables.
Solution Approach 2:
The patent applies different treatment to different parts of the data structure: join-columns are partitioned while other columns are handled differently. This local quality approach ensures that partitioning resources are allocated only where necessary for join operations, preventing unnecessary increase in probe table width.
3Adaptability or versatility
If hash tables are partitioned into memory-sized chunks in traditional disk-resident systems, then manageable hash tables are created, but the goal shifts to cache-sized chunks as memory grows
Solution Approach 1:
The patent implements dynamic partitioning where the hash table chunk size adapts based on available memory and cache resources. The system can adjust between memory-sized and cache-sized chunks depending on the execution context and hardware resources available, providing adaptability without requiring complex manual partition management.
Data Source
AI summary
A database table is partitioned with column-major layout by limiting partitioning one or more columns containing join-column values for a join operator. Join operations are executed for joining the partitioned columns.