Partial Join Estimates for Database Query Optimization

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing database management systems face inaccuracies and inconsistencies when estimating the number of records returned after a join operation, particularly with fuzzy logic-based queries, leading to suboptimal access plan selection.

Innovation Solution

The method generates partial join estimates by scaling densities from histograms of the tables to be joined and combines these estimates to produce consistent join size estimates, incorporating fuzzy logic principles to improve the accuracy of join size calculations.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Measurement precision

If conventional join size estimation methods are used, then the query optimization process is simple and fast, but the accuracy and consistency of join size estimates deteriorate, leading to suboptimal access plan selection

Engineering Contradiction:

Improvejoin size estimation accuracyVSAvoidestimation method complexity

Core Design Contradiction:

Measurement precisionVSDevice complexity

Solution Approach 1:

The patent segments the join estimation problem by dividing the join condition into multiple ranges based on histogram data. Each range is estimated separately using partial join estimates, which are then combined to produce the final join size estimate. This segmentation approach improves accuracy by handling different data distributions in different ranges rather than using a single coarse estimation.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent calculates partial join estimates for multiple overlapping ranges that may exceed the actual needed estimation. By computing estimates for ranges that are slightly broader than necessary and then combining them with appropriate weighting, the method achieves more accurate results while managing computational complexity through selective range processing.

Inventive Principle:

Principle #16Partial or excessive action

2Measurement precision

If fuzzy logic principles are incorporated into join size estimation, then the accuracy of estimates for fuzzy queries improves, but the computational resources and time required increase

Engineering Contradiction:

Improvefuzzy query estimation accuracyVSAvoidestimation computation time

Core Design Contradiction:

Measurement precisionVSLoss of time

Solution Approach 1:

The patent performs preliminary actions by pre-computing histogram data and range estimates that can be reused for multiple fuzzy logic queries. The histogram-based partial join estimates are prepared in advance, allowing fuzzy logic queries to leverage these pre-computed values rather than performing complete re-estimations, thereby reducing computation time while maintaining accuracy.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent changes parameters by using scaled density values derived from histogram data instead of raw data values for estimation. This parameter transformation allows the system to work with aggregated statistical information rather than individual records, significantly reducing computational time while preserving the ability to accurately estimate join sizes for fuzzy logic conditions.

Inventive Principle:

Principle #35Parameter changes

3Reliability

If multiple potential access plans are generated and evaluated, then the quality of the selected access plan improves, but the query optimization time increases

Engineering Contradiction:

Improveaccess plan selection qualityVSAvoidquery optimization time

Core Design Contradiction:

ReliabilityVSLoss of time

Solution Approach 1:

The patent replaces the mechanical process of executing and timing multiple access plans with a theoretical cost model based on join size estimates. Instead of physically testing each access plan, the system uses the improved join size estimation method to predict the cost of each plan and select the optimal one, thereby maintaining high-quality plan selection while significantly reducing optimization time.

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

Data Source

PatentUS7685098B2Estimating the size of a join by generating and combining partial join estimates

Publication Date: 2010.03.23 X CORP

AI summary

A program product, an apparatus, and a method of estimating a join size by generating partial join estimates and combining the partial join estimates. In particular, robust formulas are provided that may result in more consistent estimates and fewer inaccuracies. Inconsistencies and inaccuracies are generally present in conventional join size estimation techniques, however, consistent with the invention, consistent join size estimates may be generated resulting in improved access plan selection during optimization.