Database Join Processing Using Dynamic Predicate Selection
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Analytic queries in star schema databases face inefficiencies when joining large fact tables with dimension tables due to resource-intensive processes, such as hash joins, which are slow and consume significant resources, especially when the fact table contains a large number of rows.
Innovation Solution
The method dynamically selects actions at runtime based on the number of distinct join key values and the presence of indexes or payloads, using IN-list predicates and Bloom filters to optimize join processing by applying predicates to dimension tables and converting results into IN-list predicates for application to fact tables, thereby reducing unnecessary probing and resource usage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If hash join is used to join large fact tables with dimension tables, then join completeness is ensured, but processing time and resource consumption increase significantly
Solution Approach 1:
The patent applies partial action by using IN-list predicates to filter dimension tables first, processing only the necessary subset of data rather than performing complete hash joins on entire large fact tables. This reduces processing time while maintaining join completeness for the relevant data subset.
Solution Approach 2:
The patent segments the join operation into multiple stages: first applying IN-list predicates to filter dimension tables, then performing hash joins only on the filtered subsets. This segmentation avoids the need to process entire large fact tables, reducing time consumption while ensuring join completeness for matching records.
2Reliability
If hash join is used to join large fact tables with dimension tables, then join completeness is ensured, but resource consumption increases significantly
Solution Approach 1:
The patent uses IN-list predicates to identify and process only the necessary subset of dimension table records that will actually join with the fact table. This partial action approach reduces memory usage, CPU cycles, and I/O operations compared to complete hash joins, while maintaining join completeness for all matching records.
Solution Approach 2:
The patent extracts the essential filtering information from IN-list predicates before performing hash joins. By pre-filtering dimension tables using these predicates, the system removes unnecessary data from processing, reducing resource consumption while preserving join completeness for the extracted relevant subsets.
3Productivity
If dynamic selection of join actions is implemented, then processing efficiency improves, but query execution complexity increases
Solution Approach 1:
The patent implements dynamic selection of join actions by evaluating the number of distinct values in IN-list predicates at runtime. The query execution plan adapts dynamically: using IN-list filtering when distinct values are few, switching to hash joins when distinct values exceed a threshold. This dynamic approach improves efficiency while managing complexity through clear decision rules.
Solution Approach 2:
The patent changes the execution parameter based on the distinct value count of IN-list predicates. When the distinct value count is below a threshold, the system applies IN-list filtering; when above the threshold, it performs hash joins. This parameter-based decision mechanism improves processing efficiency while maintaining manageable complexity through well-defined transition criteria.
Data Source
AI summary
Performing a join of first and second database tables for a query includes applying a predicate of the query to the first table as a first predicate and determining how many distinct join key values the first table has that survive the applying of the first predicate, wherein a join key value of the first table that survives the applying of the first predicate is a surviving join key value for a second predicate. A selection includes selecting among applying the second predicate to the second table, probing the second table with the second predicate, and neither applying the second predicate to the second table nor probing the second table with the second predicate, wherein the selecting is responsive to the number of distinct, surviving join key values.