SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Accessing SQL/MX Data
HP NonStop SQL/MX Query Guide—523728-003
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.