Careful query design is of primary importance for performance.
In most queries, the most efficient retrieval of data occurs
when an index is used.
By default, the
ALLBASE/SQL query optimizer decides whether to use an index or
not, and if so, it decides which one to use. However,
you can override the query optimizer's choice with the SETOPT statement.
Also, through careful
query design, you can ensure that the optimizer is able to
choose an available index.
Topics included in this chapter are as follows:
Avoiding Serial Scans Through Query Design.
Using MIN/MAX Functions in Predicates.
Using Predicates with LIKE.
Using Predicates with BETWEEN.
Using Fetch Unique Scans.
Avoiding User Propagation of Filters.
Using Parallel Serial Scans.
Analyzing Queries with GENPLAN.
Modifying the Access Optimization Plan with SETOPT