SQL/MX 3.2 Query Guide (H06.25+, J06.14+)

Compiling and Executing a Query
HP NonStop SQL/MX Release 3.2 Query Guide—663851-002
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. However, derived predicates on the inner table are not pushed down to the
inner table. This results in a full scan of the table unless there are explicit selection
predicates present on the table. This full scan reduces the query performance.
In SQL/MX release 3.2, the derived predicates are pushed down to the inner table and
full scans are avoided if the following conditions are fulfilled:

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 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 without 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;
Figure 1-2. Query Plan
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. Pushing this derived predicate to the inner table avoids a full
scan and minimizes the data flow (unwanted records are not fetched). Hence, avoiding
a full scan improves the performance of the left join query.
TP663851.fm Page 19 Thursday, August 2, 2012 4:47 PM