Database Join Optimization Using Skew-Specific Compact Arrays

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing join and groupby operations in relational database systems fail to recognize and address data skew in join and aggregate columns, leading to inefficient query execution plans.

Innovation Solution

A method that identifies data ranges with skew in the join column, applying a skew-specific join scheme to these ranges and a non-skew specific scheme to others, using techniques such as compact array tables and compact hash tables to optimize join operations.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If a conventional join scheme (merge-sort, nested-loop, or hash join) is used, then the join operation can be performed, but it fails to recognize or consider skew in the join columns, leading to inefficient query execution

Engineering Contradiction:

Improvequery execution efficiencyVSAvoidability to handle skewed data

Core Design Contradiction:

ProductivityVSAdaptability or versatility

Solution Approach 1:

The patent applies different join schemes to different data ranges based on their skew characteristics. For data ranges identified as containing skew, a skew-specific join scheme is used, while for data ranges without skew, a conventional join scheme is applied. This local differentiation resolves the contradiction by making the system adaptable to local data characteristics while maintaining overall productivity.

Inventive Principle:

Principle #3Local quality

Solution Approach 2:

The patent dynamically selects the join scheme based on the detected data distribution characteristics. The system first identifies skew in the join columns, then adaptively chooses between skew-specific and conventional join schemes during query execution. This dynamic adaptation allows the system to optimize for skewed data when present while maintaining efficiency for non-skewed data.

Inventive Principle:

Principle #15Dynamics

2Productivity

If a skew-specific join scheme is applied to all data ranges, then efficiency for skewed data is improved, but unnecessary complexity is introduced for non-skewed data ranges

Engineering Contradiction:

Improvejoin operation efficiency for skewed dataVSAvoidjoin operation complexity

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The patent applies the skew-specific join scheme only to data ranges where skew is actually detected, rather than universally applying it to all data. This selective application reduces unnecessary complexity for non-skewed data while maintaining the efficiency benefits for skewed data ranges.

Inventive Principle:

Principle #3Local quality

Solution Approach 2:

The patent performs a cost-benefit analysis to determine whether applying the skew-specific join scheme is worthwhile for each dense range. By partially applying the skew-specific scheme only where beneficial (rather than excessively applying it everywhere), the system improves efficiency for skewed data without introducing unnecessary complexity for non-skewed data.

Inventive Principle:

Principle #16Partial or excessive action

3Ease of manufacture

If data skew is not recognized, then the conventional join schemes can be used, but memory consumption increases and hash collisions occur more frequently

Engineering Contradiction:

Improvesimplicity of join operationVSAvoidmemory consumption

Core Design Contradiction:

Ease of manufactureVSQuantity of substance

Solution Approach 1:

The patent performs preliminary analysis to identify skew in the join columns before executing the join operation. By detecting skew upfront and selecting appropriate join schemes in advance, the system avoids excessive memory consumption and hash collisions during execution, while still maintaining relative simplicity through automated detection and selection.

Inventive Principle:

Principle #10Preliminary action

4Ease of operation

If data skew is not recognized, then conventional techniques can be used, but query execution time increases due to inefficient handling of skewed data

Engineering Contradiction:

Improvesimplicity of join operationVSAvoidquery execution time

Core Design Contradiction:

Ease of operationVSLoss of time

Solution Approach 1:

The patent dynamically adapts the join scheme based on detected data skew characteristics, allowing the system to optimize query execution time for skewed data while maintaining simplicity for non-skewed data. The automatic detection and selection process maintains ease of operation while significantly reducing execution time for skewed datasets.

Inventive Principle:

Principle #15Dynamics

Data Source

PatentUS10489403B2Embracing and exploiting data skew during a join or groupby

Publication Date: 2019.11.26 INTERNATIONAL BUSINESS MACHINE CORPORATION

AI summary

A hybrid approach for performing a join in a database includes: obtaining a distribution of data values in a join column of an inner table; using the distribution, identifying one or more data ranges containing skew; for each data range identified as containing skew, performing, by the processor, the join operation using a skew specific join scheme; and for each data range not identified as containing skew, performing, by the processor, the join operation using a non-skew specific join scheme. One skew specific join scheme involves a compact array table, a highly populated array that represents the range of values that the inner table join column contains. One non-skew specific join scheme involves a compact hash table, an optimized hash table that allows high load factors with a small memory overhead. In combining multiple join techniques, joins may be performed more efficiently for skewed and non-skewed data.