Join Costing Statistics Using Correlation Factors
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Determining join costing statistics is challenging when database tables are distributed across multiple data-storage facilities or partitions, as existing optimizers struggle to accurately assess the number of unique join column values and their correlations across different units of storage.
Innovation Solution
A method is developed to derive join cost demographics for binary joins by determining the number of unique join column values in each unit of storage, calculating correlation factors, and adjusting these values based on the number of qualified units participating in the join, ultimately selecting the smaller number of join column values that will match to determine the join cost demographics.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If tables are distributed across multiple data-storage facilities or partitions, then the database system can handle larger data volumes and improve scalability, but determining join costing statistics becomes more challenging and less accurate
Solution Approach 1:
The patent segments the distributed table data into multiple partitions or data-storage facilities, with each unit maintaining its own join column value statistics. This allows the system to scale data capacity while keeping statistical measurements manageable at each segment level. The optimizer can then aggregate these segmented statistics to make informed join decisions across the distributed system.
Solution Approach 2:
The patent introduces correlation factors as intermediary metrics that bridge the gap between local partition statistics and global join cost estimation. These correlation factors capture the relationship between join columns across partitions, enabling accurate join costing without requiring complete global visibility of all data values.
2Productivity
If the optimizer uses detailed join costing statistics to find optimal join strategies, then query execution efficiency improves, but the complexity of collecting and maintaining these statistics increases
Solution Approach 1:
The patent extracts only the essential statistics needed for join optimization—specifically, the number of unique join column values in each partition and correlation factors—rather than maintaining complete global statistics. This extraction approach provides sufficient information for the optimizer to make informed decisions while significantly reducing the complexity of statistics collection and maintenance in distributed systems.
3Measurement precision
If the system calculates precise unique value counts across all partitions, then join cost estimation accuracy improves, but the computational resources and time required increase
Solution Approach 1:
The patent performs preliminary calculation of unique join column values at each partition level before the actual query execution. By pre-computing these statistics and storing them in the catalog, the system avoids the need for time-consuming global aggregation during query optimization, thus reducing the time loss while maintaining measurement precision.
Data Source
AI summary
Deriving join cost demographics for a binary join, i.e., a join between two relations each having a join column, includes using a correlation factor related to the ratio of unique join column values stored in each of a plurality of units of storage to the total number of unique values in the join column.