Single Table Join Index Cardinality Estimation

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Traditional database systems face challenges in accurately estimating the selectivity of base table predicates and cardinality of joins due to limited demographic information, especially when histograms do not provide sufficient data, leading to sub-optimal query plans.

Innovation Solution

The method involves identifying and utilizing single-table join indexes to calculate the selectivity of base table predicates and cardinality of joins by leveraging their row counts and statistics, even when demographic information on base tables is scarce, and applying these calculations to improve estimation accuracy.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Measurement precision

If traditional demographic information and histograms are used for cardinality estimation, then the estimation process is simple, but the accuracy of selectivity and cardinality estimation deteriorates when demographic information is unavailable or histograms are insufficient

Engineering Contradiction:

Improveselectivity estimation accuracyVSAvoiddemographic information availability

Core Design Contradiction:

Measurement precisionVSLoss of information

Solution Approach 1:

The patent uses join indexes as intermediary structures to bridge the gap when direct demographic information on base tables is unavailable. The join indexes serve as mediators that contain alternative statistical information (row counts, histograms on join columns) that can be used to infer the selectivity and cardinality of base table predicates and joins, thus resolving the information loss problem.

Inventive Principle:

Principle #24Intermediary (Mediator)

Solution Approach 2:

The patent performs preliminary actions by collecting and maintaining statistics on join indexes (such as row counts and histograms on join columns) in advance. These pre-collected statistics are then reused during query optimization to estimate cardinality and selectivity, eliminating the need to collect demographic information on base tables at query time and improving both accuracy and efficiency.

Inventive Principle:

Principle #10Preliminary action

2Loss of information

If histograms are used to capture demographic information, then some statistical data is available, but the ability to estimate selectivity of complex predicates accurately deteriorates

Engineering Contradiction:

Improvedemographic information captureVSAvoidcomplex predicate selectivity estimation

Core Design Contradiction:

Loss of informationVSMeasurement precision

Solution Approach 1:

The patent segments the demographic information into multiple components: base table row counts, join index row counts, and histograms specifically on join columns. This segmentation allows the optimizer to selectively use the most appropriate statistics for different types of predicates, particularly improving complex predicate estimation by using histograms on join columns that are specifically designed for that purpose.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent changes the parameters being tracked in histograms from general base table columns to specific join columns in join indexes. This parameter change enables more accurate estimation of complex predicates that involve join conditions, as the histograms are tailored to the distribution of values in the join columns rather than arbitrary base table columns.

Inventive Principle:

Principle #35Parameter changes

3Measurement precision

If statistics are collected on base tables, then demographic information is available, but the cost of collecting and maintaining statistics increases

Engineering Contradiction:

Improvecardinality estimation accuracyVSAvoidstatistics collection cost

Core Design Contradiction:

Measurement precisionVSUse of energy by moving object

Solution Approach 1:

The patent creates copies of statistical information by maintaining join indexes with their own row counts and histograms. Instead of collecting detailed demographic information on expensive base tables, the system collects statistics on the join indexes, which are cheaper to maintain. These copied statistics are then used to estimate cardinality for queries that can be satisfied using the join index information.

Inventive Principle:

Principle #26Copying

Solution Approach 2:

The patent uses join indexes as cheaper alternatives to base tables for statistics collection. Join indexes have lower maintenance costs and can be updated less frequently. The system accepts that join index statistics may be slightly less accurate than base table statistics but gains significant benefits in terms of reduced collection and maintenance costs, especially for queries that can be answered using join index information.

Inventive Principle:

Principle #27Cheap short-living objects (Disposable)

Data Source

PatentUS8914354B2Cardinality and selectivity estimation using a single table join index

Publication Date: 2014.12.16 TERADATA CORP

AI summary

A method for estimating the selectivity of a database base table predicate, the cardinality of a join, and the cardinality of an aggregation. The method includes receiving a database query, the query comprising one or more query predicates and referencing one or more database tables. One or more join indexes are identified, the join index(es) defined on respective database tables referenced by the database query. The join index(es) comprises one or more join index predicates, and includes one or more join columns in its select list. The row count selected by the query predicates is calculated at least partly using the row count or statistics of the one or more join indexes. The selectivity of the base table predicate is calculated at least partly from the calculated row count. The cardinality of the join is estimated at least partly from the row count and statistics of the identified join index(es).