Dictionary Encoding Join Acceleration via Global Code Transcoding
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational joins in database management systems are inefficient due to the need for decoding and comparing unencoded join key values, which leads to slow performance, especially when using compressed or encoded keys, and hashing methods suffer from collisions and sparsity issues.
Innovation Solution
The use of dictionary encoding for join key columns, where a global dictionary is shared between build and probe tables, allowing direct processing of dictionary codes, reducing the need for decoding and memory comparisons, and leveraging SIMD for data parallelism to accelerate the join operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If decoding is performed on encoded join key columns before joining, then the join can be executed, but the performance becomes slow due to decoding overhead
Solution Approach 1:
The patent applies preliminary action by pre-computing hash values from encoded join key columns during the build phase, before the actual join execution. The build table's encoded join keys are hashed and stored in a hash table during preprocessing, allowing the probe phase to directly compare encoded values without decoding, thus maintaining correctness while improving performance
Solution Approach 2:
The patent introduces an intermediary mechanism by using a hash table as a mediation structure between the build table and probe table. The hash table stores hash values computed from encoded join keys, serving as an intermediary that enables efficient matching without requiring decoding of the original encoded values, thus resolving the contradiction between correctness and performance
2Productivity
If hashing is used for join operations, then the join speed improves, but hash collisions occur reducing accuracy
Solution Approach 1:
The patent uses the hash table as an intermediary structure that separates the hashing operation from the final matching decision. The hash table quickly identifies candidate matches using hash values, but the actual join correctness is verified by comparing the original encoded join key values, thus maintaining both speed and accuracy
Solution Approach 2:
The patent segments the join operation into two distinct phases: build phase where hash values are computed and stored, and probe phase where encoding values are compared for final matching. This segmentation allows hashing to provide speed while encoded value comparison ensures accuracy, resolving the contradiction between join speed and join accuracy
3Reliability
If raw join key values are used instead of hashed values, then no collisions occur, but the values are sparse and expensive to compare
Solution Approach 1:
The patent segments the comparison process into two stages: first, hash values are used for quick initial filtering and candidate identification; second, the original encoded join key values are used for final precise matching. This segmentation leverages the speed of hashing for filtering while using the accuracy of encoded value comparison only when necessary, thus improving overall comparison efficiency while maintaining accuracy
4Quantity of substance
If in-memory columnar storage with compression is used, then storage efficiency improves, but decoding is required before joining increasing time cost
Solution Approach 1:
The patent extracts and utilizes the encoded values directly from the compressed columnar storage without full decoding. By computing hash values from the encoded representations and performing comparisons on encoded values rather than decoded values, the patent takes out only the necessary information for join operations, avoiding the time cost of complete decoding while maintaining storage efficiency
Solution Approach 2:
The patent performs preliminary hashing on encoded join key values during the build phase, before any decoding occurs. This preliminary action on encoded data allows the system to leverage the compressed storage format directly, extracting and processing only the join key information needed, thus avoiding unnecessary decoding time while maintaining storage efficiency
Data Source
AI summary
For join acceleration, a computer stores local encoding dictionaries (ED), including a build ED that contains a plurality of distinct build dictionary codes (DC) and a probe ED that contains a plurality of distinct probe DCs that is not identical to the plurality of distinct build DCs (BDC). Build data rows (DR) that contain a build key that contains BDCs from the plurality of distinct BDCs is stored. Probe DRs that contain a probe key that contains probe DCs from the plurality of distinct probe DCs is stored. A request for a relational join of the build DRs with the probe DRs is received. The BDCs from the build key and the probe DCs from the probe key are transcoded to global DCs (GDC) of a global ED. Based on GDCs for the build key, a build array whose offsets are respective GDCs of the global ED is populated. Based on GDCs for the probe key, offsets of the build array are accessed. A response to the request for the relational join that is based on accessing offsets of the build array is sent.