Join Predicate Derivation from Materialized Results

Solution Overview

Problem

In multiple table queries, existing methods are inefficient as they join more rows than necessary before applying filters, leading to excessive data processing due to lack of early filtering across joined tables.

Innovation Solution

Deriving and applying join predicates from materialized results to earlier tables in the join sequence, using IN or BETWEEN predicates based on filtering outcomes, to reduce the number of rows joined before reaching the final result.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If traditional multiple table join queries are executed without derived predicates, then the query can be processed using standard join operations, but the number of rows joined between tables becomes excessively large, leading to inefficient data processing

Engineering Contradiction:

Improvequery processing efficiencyVSAvoidnumber of rows joined

Core Design Contradiction:

ProductivityVSQuantity of substance

Solution Approach 1:

The patent applies preliminary action by materializing intermediate query results and deriving predicates from these materialized results before executing the actual join operations. Specifically, the system executes a preliminary query to obtain materialized results, derives predicates from these results, and then applies these predicates to filter tables before joining. This preliminary filtering action significantly reduces the number of rows that need to be joined between tables, thereby improving query processing efficiency while reducing the quantity of data processed.

Inventive Principle:

Principle #10Preliminary action

2Loss of energy

If filtering is applied only after joining multiple tables, then the join operations can proceed with all available data, but unnecessary rows are processed and joined before the filter is applied, wasting computational resources

Engineering Contradiction:

Improvecomputational resource wasteVSAvoidfiltering operation timing

Core Design Contradiction:

Loss of energyVSEase of operation

Solution Approach 1:

The patent implements preliminary filtering by deriving predicates from materialized intermediate results and applying these predicates to tables before the join operations. This allows filtering to occur earlier in the query execution process rather than after all joins are completed. The system materializes intermediate results, derives predicates from them, and applies these predicates to filter tables prior to joining, thereby reducing computational resource waste on unnecessary row processing while maintaining operational simplicity.

Inventive Principle:

Principle #10Preliminary action

3Device complexity

If all rows from multiple tables are joined before filtering, then the query maintains simplicity in execution flow, but the intermediate result contains far more rows than necessary, increasing memory and I/O requirements

Engineering Contradiction:

Improvequery execution complexityVSAvoidintermediate result size

Core Design Contradiction:

Device complexityVSQuantity of substance

Solution Approach 1:

The patent applies preliminary action by materializing intermediate query results and deriving predicates from these materialized results before executing the actual join operations. Specifically, the system executes a preliminary query to obtain materialized results, derives predicates from these results, and then applies these predicates to filter tables before joining. This preliminary filtering action significantly reduces the number of rows that need to be joined between tables, thereby improving query processing efficiency while reducing the quantity of data processed.

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS7953726B2Generated predicates from materialized result

Publication Date: 2011.05.31 WRP IP MANAGEMENT LLC

AI summary

Processing a multiple table query includes: determining if any tables in the query require materialization; for each table in the query that requires materialization, deriving at least one join predicate on a join column; determining if any tables earlier in a join sequence for the query has same join predicates; and applying the at least one derived join predicate to an earlier table in the join sequence, if there is at least one table earlier in the join sequence that has the same join predicate. This significantly reduces the number of rows that are joined before arriving at the final result.