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

Accessing SQL/MX Data
HP NonStop SQL/MX Release 3.2 Query Guide—663851-002
2-8
Full Table Scan
This plan does not have any full table or index scans because both index T3_b and
T1_c are used for key lookup for the predicates T3.b=5 and T1.c=T3.c,
respectively. In comparison, this hash join plan has one index used for lookup and one
index fully scanned:
HJ
/ \
T3_b T1_c
The only index used for lookup in the previous hash join plan is T3_b. The index T1_c
might have been chosen because of its smaller size, but it cannot be used for
predicate evaluation on its key. As a result, the index T1_c will be fully scanned. In this
scenario, when INTERACTIVE_ACCESS is set to ON, the optimizer frequently gives
preference to nested join plans because they have a better chance of utilizing indexes
for lookups.
Example 6
select T1.* from T1, T3 where T3.b=5 and T1.c=T3.c;
The plan with the minimum number of full table or index scans is:
NJ
/ \
T3_b NJ
/ \
T1_c T1
No full table or index scans are performed in this plan.
Example 7
select T4.a, T1.f from T1, T4 where T1.c=T4.b and T4.c=5 and
T1.a between 12 and 20;
In this case, several plans have the minimum number of full table or index scans. For
example:
HJ
/ \
T4_c T1
This plan uses index T4_c for key lookup on T4.c and the clustering index on T1 for
key lookup on T1.a.
TP663851.fm Page 8 Thursday, August 2, 2012 4:47 PM