Dictionary Column Computation for Low-Cardinality Database Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database operations on columns with low cardinality values are computationally expensive due to inefficient processing methods, leading to slowed execution times.
Innovation Solution
Creating a dictionary column from a dictionary table corresponding to the column values, applying operations to the dictionary table instead of the original data table, thereby reducing the number of iterations required.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If database operations are performed on each row of a column individually, then the operations can be applied to all data records, but the computational expense increases and execution time slows
Solution Approach 1:
The patent segments the column data into two parts: a dictionary table containing unique values and their identifiers, and the original data table containing only identifiers. Operations are performed on the smaller dictionary table and then applied to all rows through identifier mapping, reducing computational overhead while maintaining completeness.
Solution Approach 2:
The patent creates a dictionary column in the dictionary table that replicates the operation results for each unique value. This copy contains pre-computed results that can be efficiently referenced during query execution, avoiding repeated computation on the full data table.
2Reliability
If operations are applied to the original data table with all rows, then all data records are processed, but the number of iterations is large for low cardinality columns
Solution Approach 1:
The patent divides the data processing task into two stages: first performing operations on the compact dictionary table with unique values, then mapping results back to the original data through identifier relationships. This ensures all data records are covered while minimizing iteration count to the number of unique values.
3Productivity
If a dictionary column is created from the dictionary table, then computational expense is reduced for low cardinality columns, but additional data structures must be maintained
Solution Approach 1:
The patent performs the computationally expensive operations in advance on the dictionary table during data loading or maintenance operations. The results are stored in the dictionary column, so that during query execution, only simple identifier lookups are required, significantly improving runtime performance.
Data Source
AI summary
In an example embodiment, rather than apply an operation to the values of an original data table, the operation is applied to the values in the dictionary table corresponding to the column of the original data table on which the operation is being performed. This produces what is termed a “dictionary column”, which is a column that contains cells having values indicating the results applying the operation to the rows of the corresponding dictionary table.