Semijoin Query Plan Pruning Using Dynamic In-List Evaluation

Solution Overview

Problem

Database management systems face challenges in optimizing query performance due to complex queries and large volumes of transactions, leading to inefficient resource usage and prolonged response times.

Innovation Solution

Implementing SemiJoin Reduction optimization techniques that utilize a dynamic in-list to prune operators from the query execution plan, particularly the TableScanSemiJoin, when the in-list is empty, thereby reducing unnecessary evaluations and cascading prunings of subsequent operators.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If the query execution plan includes all operators for complete query processing, then query completeness is ensured, but resource consumption and response time increase

Engineering Contradiction:

Improvequery completenessVSAvoidresponse time

Core Design Contradiction:

ReliabilityVSProductivity

Solution Approach 1:

The system performs preliminary evaluation of the dynamic in-list before executing the full query execution plan. By checking whether the in-list is empty in advance, the system can prune unnecessary operators (TableScanSemiJoin and subsequent operators) before they consume resources, thus resolving the contradiction between ensuring query completeness and improving response time.

Inventive Principle:

Principle #10Preliminary action

2Reliability

If the query execution plan includes all operators for complete query processing, then query completeness is ensured, but resource consumption increases

Engineering Contradiction:

Improvequery completenessVSAvoidresource consumption

Core Design Contradiction:

ReliabilityVSUse of energy by moving object

Solution Approach 1:

The system performs preliminary evaluation of the dynamic in-list before executing the full query execution plan. By checking whether the in-list is empty in advance, the system can prune unnecessary operators (TableScanSemiJoin and subsequent operators) before they consume resources, thus resolving the contradiction between ensuring query completeness and reducing resource consumption.

Inventive Principle:

Principle #10Preliminary action

3Productivity

If dynamic in-list evaluation is performed to enable operator pruning, then query optimization is improved, but evaluation overhead is added

Engineering Contradiction:

Improvequery optimizationVSAvoidevaluation overhead

Core Design Contradiction:

ProductivityVSLoss of time

Solution Approach 1:

The system converts the potential harm of empty in-lists (which would cause unnecessary operator execution) into a benefit by evaluating the in-list dynamically. The evaluation overhead is justified because pruning operators based on empty in-lists results in significant performance gains that far exceed the minimal cost of the evaluation itself.

Inventive Principle:

Principle #22Blessing in disguise (Convert harm into benefit)

Data Source

PatentUS20260017262A1Table semijoin dynamic pruning

Publication Date: 2026.01.15 SAP SE

AI summary

A query execution plan is generated for a received query. The query execution plan may include a first operator with a predicate tree. The predicate tree may be expanded into one or more predicates and a value list, where the value list being empty satisfies a pruning condition. In response to determining that the pruning condition is satisfied, the first operator is pruned from the query execution plan. The value list may include a list of values specified by an expression associated with a first table and a column index of a second table. After pruning the first operator from the query execution plan, a pruned version of the query execution plan that does not include the first operator is executed.