# US 10,769,149 B2 · Transitive local predicated across multiple joins

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 10,769,149 B2
- Document ID: US10769149B2
- Status: Granted
- Kind: B2
- Application number: 15/033,519
- Filed: 2013-12-06
- Published: 2016-09-29
- Granted: 2020-09-08
- Earliest claimed: 2013-12-06
- Assignees: Micro Focus LLC
- Applicants: Hewlett Packard Enterprise Development LP
- Inventors: Sreenath Bodagala; James Laurence Finnerty
- Web page: https://patentgrep.com/patent/US10769149B2

## CPC

- G06F 16/24544: {Join order optimisation}
- G06F 16/2453: Query optimisation
- G06F 16/2454: {Optimisation of common expressions}
- G06F 16/2456: {Join operations}
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## Abstract

In an example, a transitive local predicates may be inferred across multiple joins, wherein the multiple outer joins include multiple outer joins. A query connecting tables by the multiple joins is analyzed. A local predicate may then be identified from the analyzed query and may be determined to be either a where-clause local predicate or an on-clause local predicate. Accordingly, a transitive local predicate may be inferred across a selection of the tables based on the determination.

## Claims

### Claim 1

A method to infer transitive local predicates across multiple joins, comprising:
analyzing a query to a database connecting tables by the multiple joins, wherein the multiple joins include multiple outer joins;
identifying, utilizing a processor, a local predicate from analyzing the query;
determining whether the local predicate is a where-clause local predicate or an on-clause local predicate, wherein the where-clause local predicate is a local predicate appearing in a where-clause of a Structured Query Language (SQL) statement and the on-clause local predicate is a local predicate appearing in an on-clause of a SQL statement;
determining whether the local predicate is a strong local predicate;
determining whether a plurality of join predicates are equality join predicates;
inferring a transitive local predicate across a selection of the tables based on the determining whether the local predicate is a where-clause local predicate or an on-clause local predicate, the determining whether the local predicate is a strong local predicate, and the determining whether the plurality of join predicates are equality join predicates; and
performing, utilizing the transitive local predicate, join operations for the query to the database.

### Claim 2 (depends on 1)

The method of claim 1, wherein the inferring of the transitive local predicate across the selection of the tables comprises:
in response to determining that the local predicate is a where-clause local predicate, inferring the transitive local predicate across all the tables if the where-clause local predicate is a strong local predicate and all join predicates are equality join predicates.

### Claim 3 (depends on 1)

The method of claim 1, wherein the inferring of the transitive local predicate across the selection of the tables comprises:
in response to determining that the local predicate is an on-clause local predicate,
identifying a local table associated with the on-clause local predicate;
determining a table from the tables that is connected to the local table by a left outer join or an inner join; and
inferring a transitive local predicate to the table if the on-clause local predicate is a strong local predicate and all the join predicates are equality join predicates.

### Claim 4 (depends on 3)

The method of claim 3, further comprising:
determining each table from the tables that is connected to the local table by a sequence of joins consisting of at least one of a left outer join and an inner join; and
inferring the transitive local predicate across each table that is connected to the local table by the sequence of joins if the on-clause local predicate is a strong local predicate and all the join predicates are equality join predicates.

### Claim 5 (depends on 1)

The method of claim 1, wherein the multiple joins comprise at least one of a left outer join, a right outer join, a full outer join, and an inner join.

### Claim 6 (depends on 1)

The method of claim 1, wherein the local predicate is a strong local predicate that does not evaluate to true on NULL values for a predicate column.

### Claim 7 (depends on 1)

The method of claim 1, wherein the local predicate includes at least one of a Boolean operator and a comparison operator.

### Claim 8

A system to infer transitive local predicates across multiple joins, comprising:
a query evaluator engine, executed by a processor, to:
analyze a query connecting tables by the multiple joins, wherein the multiple joins include multiple outer joins,
identify a local predicate from analyzing the query,
determine whether the local predicate is a where-clause local predicate or an on-clause local predicate, wherein the where-clause local predicate is a local predicate appearing in a where-clause of a Structured Query Language (SQL) statement and the on-clause local predicate is a local predicate appearing in an on-clause of a SQL statement;
determine whether the local predicate is a strong local predicate; and
determine whether a plurality of join predicates are equality join predicates; and
a predicate generator engine to infer a transitive local predicate across a selection of the tables based on the determination whether the local predicate is a where-clause local predicate or an on-clause local predicate, the determination whether the local predicate is a strong local predicate, and the determination whether the plurality of join predicates are equality join predicates.

### Claim 9 (depends on 8)

The system of claim 8, wherein the predicate generator engine is to, in response to a determination that the local predicate is a where-clause local predicate, infer the transitive local predicate across all the tables if the where-clause local predicate is a strong local predicate and all join predicates are equality join predicates.

### Claim 10 (depends on 8)

The system of claim 8, wherein the predicate generator engine is to, in response to a determination that the local predicate is an on-clause local predicate,
identify a local table associated with the on-clause local predicate;
determine a table from the tables that is connected to the local table by a left outer join or an inner join; and
infer a transitive local predicate to the table if the on-clause local predicate is a strong local predicate and all the join predicates are equality join predicates.

### Claim 11 (depends on 10)

The system of claim 10, wherein the predicate generator engine is to:
determine each table from the tables that is connected to the local table by a sequence of joins consisting of at least one of a left outer join and an inner join; and
infer the transitive local predicate across each table that is connected to the local table by the sequence of joins if the on-clause local predicate is a strong local predicate and all the join predicates are equality join predicates.

### Claim 12

A non-transitory computer readable medium including machine readable instructions executable by a processor to:
analyze a query connecting tables by multiple joins, wherein the multiple joins include multiple outer joins;
identify a local predicate from analyzing the query;
determine whether the local predicate is a where-clause local predicate or an on-clause local predicate, wherein the where-clause local predicate is a local predicate appearing in a where-clause of a Structured Query Language (SQL) statement and the on-clause local predicate is a local predicate appearing in an on-clause of the SQL statement;
determine whether the local predicate is a strong local predicate;
determine whether a plurality of join predicates are equality join predicates; and
infer a transitive local predicate across a selection of the tables based on the determination whether the local predicate is a where-clause local predicate or an on-clause local predicate, the determination whether the local predicate is a strong local predicate, and the determination whether the plurality of join predicates are equality join predicates; and
perform, utilizing the transitive local predicate, join operations relating to the query to the database.

### Claim 13 (depends on 12)

The non-transitory computer readable medium of claim 12, wherein the machine readable instructions are executable by the processor to, in response to a determination that the local predicate is a where-clause local predicate, infer the transitive local predicate across all the tables if the where-clause local predicate is a strong local predicate and all join predicates are equality join predicates.

### Claim 14 (depends on 12)

The non-transitory computer readable medium of claim 12, wherein the machine readable instructions are executable by the processor to, in response to a determination that the local predicate is an on-clause local predicate,
identify a local table associated with the on-clause local predicate;
determine a table from the tables that is connected to the local table by a left outer join or an inner join; and
infer a transitive local predicate to the table if the on-clause local predicate is a strong local predicate and all the join predicates are equality join predicates.

### Claim 15 (depends on 14)

The non-transitory computer readable medium of claim 14, wherein the machine readable instructions are executable by the processor to:
determine each table from the tables that is connected to the local table by a sequence of joins consisting of at least one of a left outer join and an inner join; and
infer the transitive local predicate across each table that is connected to the local table by the sequence of joins if the on-clause local predicate is a strong local predicate and all the join predicates are equality join predicates.

### Claim 16 (depends on 1)

The method of claim 1, wherein the identifying includes converting the query into an outputted parse tree.

### Claim 17 (depends on 16)

The method of claim 16, wherein the determining whether the local predicate is a where-clause local predicate or an on-clause local predicate includes searching the outputted parse tree.

### Claim 18 (depends on 8)

The system of claim 8, wherein to identify the local predicate, the query evaluator engine is to convert the query into an outputted parse tree.

### Claim 19 (depends on 18)

The system of claim 18, wherein to determine whether the local predicate is a where-clause local predicate or an on-clause local predicate, the query evaluator engine is to search the outputted parse tree.

### Claim 20 (depends on 12)

The non-transitory computer readable medium of claim 12, wherein to identify the local predicate, the machine readable instructions are executable by the processor to convert the query into an outputted parse tree.