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-20
Improving Query Performance
But, DP2 might still perform the full scan if:

t1.c1 = 10 or t1.c1 = 20

t1.c1 IN (10,20) -- equals a)
However, if MDAM is forced by CONTROL QUERY TABLE * MDAM 'ON', MDAM is
forced on both T1 and T2. Based on correct statistics, the compiler may automatically
select MDAM.
Pushing this derived predicate to the inner table avoids a full scan or minimizes the
data flow (unwanted records are not fetched).
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.
Note. In general, pushing down predicates does not avoid full scans. Pushing down means
that the DP2 instead of the Master executor evaluates the predicate.
TP663851.fm Page 20 Wednesday, January 30, 2013 5:37 PM