Dimension Table Compression Dictionaries for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Small dimension tables in denormalized database schemas require join operations to be effective, but existing solutions like semi-joins, Materialized Query Tables, and schema denormalization increase disk space usage and maintenance overhead.
Innovation Solution
Implementing compression dictionaries that translate foreign keys from one table into column values of another, allowing direct predicate application without physical join operations, thus reducing overhead and space requirements.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If join operations are used to filter dimension tables against fact tables, then query accuracy is improved, but processing time and system complexity increase
Solution Approach 1:
The patent pre-materializes dimension table data into the fact table during data loading, so that when queries are executed, the dimension data is already available and no join operations are needed. This preliminary action eliminates the need for time-consuming join operations during query processing while maintaining query accuracy.
Solution Approach 2:
The patent creates a copy of the dimension table data and stores it within the fact table structure. This copying approach allows the dimension data to be directly accessed without requiring joins to the original dimension tables, thereby reducing processing time while preserving query accuracy.
2Productivity
If dimension tables are materialized or denormalized to eliminate joins, then processing speed is improved, but disk space usage increases
Solution Approach 1:
The patent applies local quality by materializing only the necessary dimension columns into the fact table rather than duplicating entire dimension tables. This selective approach reduces disk space requirements while still enabling fast query processing for the specific attributes needed in the analysis.
Solution Approach 2:
The patent implements partial materialization by incorporating only the essential dimension data into the fact table structure, avoiding the excessive action of fully denormalizing the entire schema. This partial approach balances processing speed improvements with reasonable storage overhead.
3Productivity
If semi-joins or Materialized Query Tables are used to optimize filtering, then query performance is improved, but maintenance complexity increases
Solution Approach 1:
The patent merges the dimension table and fact table into a unified structure where dimension data is stored within the fact table. This merging eliminates the need for separate semi-join operations or Materialized Query Tables, reducing maintenance complexity while preserving query performance benefits.
Solution Approach 2:
The patent implements a self-service approach where the fact table structure itself provides the dimension filtering capability through embedded dimension data. This eliminates the need for separate MQT maintenance operations and simplifies the overall system while maintaining optimized query performance.
Data Source
AI summary
According to one embodiment of the present invention, a system may execute database queries and may comprise a computer system including at least one processor. The system may select a column of a first table as a candidate column for creating a dictionary, create tokens for a foreign key in a second table that corresponds to a primary key in the first table, generate the dictionary for the selected column using the created tokens as indexes in the dictionary and modify a query accessing the column of the first table to retrieve column values based on the dictionary. Embodiments of the present invention further include a method and computer program product executing database queries in substantially the same manner described above.