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

Accessing SQL/MX Data
HP NonStop SQL/MX Query Guide—523728-003
2-7
Full Table Scan
•
Among the set of all plans considered, the optimizer chooses the set of plans with
the minimum number of full table or index scans.
•
Among the set of plans with a minimum number of full table or index scans, the
optimizer chooses the plan with the lowest estimated cost.
These examples indicate the optimizer strategy when INTERACTIVE_ACCESS is set
to ON:
T1(a,b,c,d,e,f) with indexes clustering(a), T1_b(b), T1_c(c)
T2(a,b,c,d,e,f) with indexes clustering(a,b,c), T2_b(b)
T3(a,b,c,d,e,f) with indexes clustering(a), T3_b(b)
T4(a,b,c,d,e,f) with indexes clustering(a,b), T4_c(c)
Example 1
select * from T1 where c=3;
The INTERACTIVE_ACCESS plan uses the index T1_c for efficient key lookup on
column T1.c.
Example 2
select * from T1 where a=3;
The INTERACTIVE_ACCESS plan uses the T1 clustering index for efficient key lookup
on column T1.a.
Example 3
select * from T1 where a=3 and b=6;
The INTERACTIVE_ACCESS plan could use either T1 clustering index (for key lookup
on column a) or index T1_b (for key lookup on column T1.b).
Example 4
select * from T2 where b=3;
When INTERACTIVE_ACCESS is ON, the optimizer considers the index T2_b. In
addition, if there is cost-efficient MDAM access to the clustering key, the optimizer also
considers using the clustering index for the MDAM search. This strategy depends on
the table size and UEC of column T2.a.
Example 5
select T1.c from T1, T3 where T3.b=5 and T1.c=T3.c;
The plan with the maximum number of index usages is:
NJ
/ \
T3_b T1_c