Shared Dictionary Encoding for Faster Column Store Joins
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Dictionary encoding in column store databases complicates join operations when two related columns have incompatible dictionaries, leading to inefficient data processing and increased computational cost.
Innovation Solution
Implement a shared dictionary based on a key constraint to ensure compatibility between columns, using a join manager to perform efficient hash and index join operations by encoding data values with a unified identifier system.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If dictionary encoding is used to represent data values in table columns, then data storage efficiency is improved, but join operations become more complex when two related columns have incompatible dictionaries
Solution Approach 1:
The patent merges two separate dictionary encoding systems into a unified dictionary system. When a key constraint exists between two tables, the patent creates a single shared dictionary that both tables reference, eliminating the need for separate incompatible dictionaries. This merging approach maintains the storage efficiency benefits of dictionary encoding while resolving the complexity issue during join operations, as both tables now use the same value identifier system for their related columns.
Solution Approach 2:
The patent implements a universal dictionary system that serves multiple functions: it provides compact storage representation, enables efficient join operations, and maintains data integrity across related tables. The unified dictionary acts as a common reference that both tables in a relationship can use, making the system multi-functional and eliminating the need for separate encoding schemes.
2Adaptability or versatility
If separate incompatible dictionaries are used for related columns in two tables, then each table can independently encode its data, but additional data processing steps are required during join operations
Solution Approach 1:
The patent combines the encoding systems of two tables into a single unified dictionary when a key constraint exists. This merging eliminates the need for separate incompatible dictionaries while preserving the ability to represent all unique data values from both tables, thus maintaining adaptability while improving join operation efficiency.
3Productivity
If a unified shared dictionary is implemented for related columns, then join operations become more efficient with direct value identifier comparison, but the system requires coordination between tables to maintain the shared dictionary
Solution Approach 1:
The patent performs preliminary action by creating the unified shared dictionary during the table design or data loading phase, before join operations are executed. This advance preparation eliminates the need for complex runtime coordination during joins, as the dictionary mapping is already established and both tables can directly use the pre-coordinated value identifiers.
Data Source
AI summary
A computer implemented method can receive a first data table having a first column and a second data table having a second column, and obtain a dictionary shared by the first and second columns. The dictionary maps a plurality of unique data values to corresponding unique value identifiers. The method can generate a first data vector for the first column and a second data vector for the second column. The first data vector includes first value identifiers corresponding to data values stored in the first column, and the second data vector includes second value identifiers corresponding to data values stored in the second column. The method can join the first and second data tables based on the first and second data vectors. The joining generates one or more matching records between the first and second data tables. Related systems and software for implementing the method are also disclosed.