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-20
Improving Query Performance
Example 2
The following tables are used to illustrate different scenarios for which predicate is
pushed on the inner table:
create table tt1(i int not null not droppable, j int, primary
key(i));
create table tt2(i int, j int not null not droppable, primary
key(j));
create table tt3(i int not null not droppable, j int, primary
key(i));
create table tt4(i int, j int not null not droppable, primary
key(j));
Query 1
select tt1.i, tt2.j, tt3.j
from tt1 join tt2 on tt1.j = tt2.j
left join tt3 on tt1.i = tt3.i and tt2.j = tt3.i
where tt1.i >=?;
The derived predicate(tt3.i >=?)is pushed down to scan of tt3.
Query 2
select tt1.i, tt2.j, tt3.j, tt4.j
from tt1 left join tt2 on tt1.i = tt2.j
left join tt3 on tt1.i = tt3.i and tt2.j = tt3.i
left join tt4 on tt1.i = tt4.i
where tt1.i <=?;
The derived predicates (tt2.i <=? and tt3.i <=?) are pushed down to the scan of
respective tables.
TP663851.fm Page 20 Thursday, August 2, 2012 4:47 PM