Dictionary Encoding for Relational Database Join Columns
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing hash join methods in relational databases are inefficient due to the high cost of hash computations and bloom filter operations, especially when dealing with wide char/varchar columns or compressed/encrypted join attributes, which require repeated hashing and decoding, leading to slow performance and increased overhead.
Innovation Solution
The use of domain-level dictionaries to encode join columns, allowing for vector lookups instead of hash lookups, reducing the need for hash computations and minimizing bloom filter creation and evaluation costs, while leveraging SIMD operations for faster joins and projection of matching data.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If hash join methods are used to combine data from two sources, then join operations can be performed, but the performance is slow due to high cost of hash computations and bloom filter operations
Solution Approach 1:
The patent applies preliminary action by pre-computing hash values for join columns and storing them in a hash table during data loading, rather than computing hashes repeatedly during each join operation. This allows the join operation to simply look up pre-computed hash values, dramatically reducing computation time during actual joins.
Solution Approach 2:
The patent creates a copy of the hash table in memory that can be quickly accessed during join operations. Instead of repeatedly computing hashes or accessing the original data sources, the system uses this in-memory copy with pre-computed hash values to perform fast lookups and joins.
2Productivity
If hash computations are performed on wide char/varchar columns or compressed/encrypted join attributes, then join operations can be performed, but the overhead increases significantly
Solution Approach 1:
The patent performs preliminary hashing of join columns during data loading, storing the hash values in the hash table. This eliminates the need to repeatedly hash wide char/varchar columns or decompress/decrypt attributes during each join operation, significantly reducing computation overhead.
Solution Approach 2:
The patent replaces the mechanical process of repeated hashing and decoding during joins with a simpler lookup mechanism. By pre-computing and storing hash values, the system substitutes complex repeated computations with simple hash table lookups, reducing both time and computational resources required.
3Reliability
If bloom filters are created and evaluated during hash joins, then filtering can be performed, but the creation and evaluation costs are high
Solution Approach 1:
The patent extracts the filtering function from the bloom filter mechanism and implements it directly using hash table lookups. By removing the bloom filter layer and using precise hash-based lookups, the system maintains filtering accuracy while eliminating the overhead of bloom filter creation and evaluation.
Solution Approach 2:
The patent uses a copied hash table structure in memory that enables direct and accurate lookup of join candidates without requiring probabilistic bloom filter checks. This copy-based approach provides deterministic filtering results while reducing processing time compared to bloom filter evaluation.
Data Source
AI summary
Techniques are described for encoding join columns that belong to the same domain with a common dictionary. The tables are encoded with dictionary indexes that make the comparison operation of a join query a quick equality check of two integers and there is no need to compute any hashes during execution. Additionally, the techniques described herein minimize the bloom filter creation and evaluation cost as well because the dictionary indexes serve as hash values into the bloom filter. If the bloom filter is as large as the range of dictionary indexes, then the filter is no longer a probabilistic structure and can be used to filter rows in the probe phase with full certainty without any significant overhead.