Cardinality Estimation via Dimension Table Segmentation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current cardinality estimation methods in data warehousing struggle with accuracy when dealing with join predicates between fact tables and overloaded dimension tables, particularly in scenarios where a majority of fact table records refer to a small subset of dimension table records, leading to inefficient query optimization and resource provisioning.
Innovation Solution
A method that involves receiving a dimension table and a fact table in a join predicate, identifying the majority of records in the fact table that refer to a subset of records in the dimension table, computing a filter factor, and creating a statistical view using relevant portions of the dimension table to improve cardinality estimation, which is automatic, fast, and lightweight, requiring no user intervention.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional cardinality estimation methods are used for join predicates between fact tables and overloaded dimension tables, then the estimation process is simple and fast, but the accuracy of cardinality estimation deteriorates when a majority of fact table records refer to a small subset of dimension table records
Solution Approach 1:
The dimension table is segmented into multiple partitions based on the join predicate conditions. Instead of treating the entire dimension table as a single unit, the method divides it into relevant and irrelevant partitions, estimating cardinality only for the relevant portions. This segmentation allows accurate cardinality estimation by focusing computational effort on the actual data subsets that contribute to the join result.
Solution Approach 2:
The method extracts and identifies the specific subset of dimension table records that are actually referenced by the fact table through the join predicate. By taking out only the relevant portion of the dimension table (the subset of records that form actual join matches), the system avoids the inaccuracy of treating the entire overloaded dimension table uniformly, thereby improving cardinality estimation precision.
2Measurement precision
If the entire dimension table is considered for cardinality estimation, then the estimation covers all possible records, but it includes irrelevant records that do not participate in the join, leading to inaccurate results
Solution Approach 1:
The method extracts and identifies the specific subset of dimension table records that are actually referenced by the fact table through the join predicate. By taking out only the relevant portion of the dimension table (the subset of records that form actual join matches), the system avoids the inaccuracy of treating the entire overloaded dimension table uniformly, thereby improving cardinality estimation precision.
Solution Approach 2:
The estimation process applies local quality by treating different portions of the dimension table differently based on their relevance to the join predicate. Relevant portions (those actually referenced by fact table records) receive detailed analysis and accurate estimation, while irrelevant portions are either excluded or given minimal consideration. This localized approach ensures computational resources focus on the critical subsets that determine actual query results.
Data Source
AI summary
In one embodiment, a method for improving cardinality estimation of a join predicate between a fact table and an overloaded dimension table is provided. The method includes receiving a dimension table and a fact table in a join predicate of one or more SQL statements. The method further includes identifying a majority of records in the fact table that refer to a subset of records in the dimension table. The method further includes computing a filter factor of the join predicate between the dimension table and the fact table. The method further includes creating a statistical view using one or more relevant portions of the dimension table that are referred to by the fact table.