Hash Join Execution with Dynamic Partition Sizing and Cache Spillover
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database servers face inefficiencies in join operations due to inaccurate table statistics, leading to partition size skew and cache misses during hash join operations, which can result in additional rounds of partitioning and increased memory access latency.
Innovation Solution
A hash table structure is generated using a build key array and a link array, with spillover arrays in lower latency memory for partitions that exceed cache limits, allowing for efficient probing and minimizing memory storage requirements, thereby maintaining cache containment and reducing cache spillover.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If partition size is determined based on inaccurate table statistics, then partitioning can be performed, but partition size skew occurs and partitions cannot fit in cache
Solution Approach 1:
The patent performs a preliminary analysis of actual data distribution during the build phase to detect partition size skew before the probe phase begins. This early detection allows the system to identify problematic partitions and apply corrective measures (such as repartitioning or spill-to-disk) before cache misses occur during probing, thereby maintaining join operation efficiency despite inaccurate initial statistics
Solution Approach 2:
The patent dynamically adjusts partitioning parameters based on observed data characteristics. When partition size skew is detected, the system modifies the partitioning strategy (e.g., changing hash functions, adjusting partition counts, or implementing uneven partitioning) to ensure partitions fit within cache boundaries, thus resolving the contradiction between productivity and reliability
2Speed
If partition size is increased to reduce number of partitions, then processing speed improves, but cache containment is violated and cache misses increase
Solution Approach 1:
The patent implements dynamic partition sizing where partition boundaries are adjusted based on actual data distribution and cache capacity. During the build phase, the system monitors partition sizes and dynamically modifies them to ensure they fit within cache while maximizing the number of rows processed per partition. This dynamic adjustment maintains cache containment (preventing cache misses) while optimizing processing speed by minimizing the number of partitions
3Reliability
If additional partitioning rounds are performed to fix size skew, then partition fit in cache is improved, but execution time increases
Solution Approach 1:
The patent extracts the partition size skew detection and correction logic from a separate partitioning round and integrates it into the build phase itself. By performing skew detection during row insertion into hash tables and applying corrections immediately (such as redirecting rows to appropriate partitions or spilling to disk), the system eliminates the need for additional partitioning rounds, thereby maintaining partition cache fit while avoiding execution time penalties
Data Source
AI summary
A processor receives a payload array and generates a hash table in a cache that includes a hash bucket array. Each hash bucket element contains an identifier that defines a location of a build key array element in the payload array. For a particular build key array element, the processor determines a hash bucket element that corresponds to the payload array. The processor copies the identifier for particular build key array element into the hash bucket element. If the cache is unable to insert additional build key array elements into the hash table in the cache, then the processor generates a second hash table for the remaining build key array elements in local volatile memory. When probing, the processor probes both hash tables in the cache and local volatile memory for identifiers in hash bucket elements that are used to locate matching build key array elements.