Lateral Join Query Plan With Shared Subplans for Correlated Subqueries

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Database management systems face challenges in optimizing performance when handling complex queries and large volumes of transactions, particularly with correlated subqueries, leading to inefficient resource usage and prolonged response times.

Innovation Solution

Implementing a query execution plan optimization technique that includes a distinct operator to eliminate duplicate tuples, utilizing a shared subplan and separate pipelines for correlated subqueries, thereby optimizing the query execution plan to enhance efficiency and reduce redundant evaluations.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Measurement precision

If a database management system handles complex queries with correlated subqueries using traditional execution plans, then the system can process queries with complete accuracy, but the query execution time and resource consumption increase significantly

Engineering Contradiction:

Improvequery execution accuracyVSAvoidquery response time

Core Design Contradiction:

Measurement precisionVSLoss of time

Solution Approach 1:

The query execution plan is segmented into multiple pipelines (first pipeline, second pipeline, third pipeline) that can process different portions of the query simultaneously. Each pipeline handles specific subqueries or operations independently, allowing parallel execution and reducing overall query response time while maintaining accuracy through coordinated results aggregation.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The system performs preliminary actions by pre-computing and caching results of subqueries that are referenced multiple times. The shared subplan computes results once and makes them available to multiple pipelines, eliminating redundant computations and reducing query execution time without sacrificing result accuracy.

Inventive Principle:

Principle #10Preliminary action

2Ease of operation

If the database system executes correlated subqueries without optimization, then the implementation remains simple and easy to maintain, but the resource usage and execution efficiency deteriorate

Engineering Contradiction:

Improvesystem maintenance easeVSAvoidquery execution efficiency

Core Design Contradiction:

Ease of operationVSProductivity

Solution Approach 1:

The shared subplan serves multiple functions by providing computed results to multiple pipelines. This multi-functional component eliminates redundant subquery executions across different pipelines, improving execution efficiency while maintaining a unified, manageable structure that does not significantly complicate system maintenance.

Inventive Principle:

Principle #6Universality (Multi-functionality)

Solution Approach 2:

Instead of copying and executing the entire subquery multiple times across different pipelines, the system creates a single shared subplan that computes results once. Other pipelines reference and reuse these results, reducing resource consumption and execution time while keeping the optimization logic centralized and maintainable.

Inventive Principle:

Principle #26Copying

3Device complexity

If the database management system uses traditional query execution plans without distinct operators, then the processing logic remains straightforward, but duplicate tuple evaluations increase resource consumption

Engineering Contradiction:

Improveprocessing logic complexityVSAvoidresource consumption

Core Design Contradiction:

Device complexityVSLoss of energy

Solution Approach 1:

The distinct operator extracts and eliminates duplicate tuples from query results at specific points in the execution plan. By placing distinct operators strategically in pipelines, the system removes redundant data early in the processing flow, reducing the amount of data that subsequent operations must process and thereby reducing overall resource consumption without significantly complicating the processing logic.

Inventive Principle:

Principle #2Taking out (Extraction)

Data Source

PatentUS12561326B1Lateral join

Publication Date: 2026.02.24 SAP SE

AI summary

A query is received from a software application, with the query including at least a first subquery. In response to receiving the query, a database management system (DBMS) generates a query execution plan for the received query. Next, the DBMS optimizes the query execution plan based on the first subquery to generate an optimized query execution plan, wherein the optimized query execution plan includes at least a first pipeline, a second pipeline, a shared subplan, and a distinct operator, wherein the distinct operator is configured to eliminate duplicate tuples. Then, the DBMS executes the optimized query execution plan to identify and retrieve first data stored in a database. Next, the DBMS provides the first data to the software application.