SQL/MX 3.2.1 Query Guide (H06.26+, J06.15+)

Compiling and Executing a Query
HP NonStop SQL/MX Release 3.2.1 Query Guide—663851-003
1-19
Improving Query Performance
Improving Performance for the Left Join
In SQL/MX, the predicates on the outer table of the left join are pushed down to the
outer table.
Starting with SQL/MX release 3.2.1, the derived predicates are pushed down to the
inner table and therefore full scans may be avoided if the following conditions are met:

The query must have a selection predicate on the outer table of the left join.

The columns of the outer table, which are involved in the selection predicate, must
be a part of the primary key of the table.

The equality join predicates must contain all primary key columns of both the inner
and outer table.
Example 1
Consider the following tables:
Create table t1 (c1 int not null not droppable, c2 int not
null not droppable, primary key(c1,c2));
Create table t2 (c1 int not null not droppable, c2 int not
null not droppable, primary key(c1,c2));
The following query produces a plan with a predicate push down on the inner table:
select *
from t1 left join t2 on t1.c1 = t2.c1 and t1.c2 = t2.c2
where t1.c1 between 10 and 20;
In this query, the join predicate is t1.c1 = t2.c1, and the selection predicate is
t1.c1. t1.c1 is BETWEEN 10 AND 20, which implies that t2.c1 is also
BETWEEN 10 AND 20.
Figure 1-2. Query Plan
In the following cases, a full scan is avoided if:

t1.c1 BETWEEN 10 and 20

t1.c1 = 10
TP663851.fm Page 19 Wednesday, January 30, 2013 5:37 PM