Chapter 3 Guidelines on Query Design

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 Subqueries.
Using UNION.
Using MIN/MAX Functions in Predicates.
Using OR Predicates.
Using Predicates with LIKE.
Using Predicates with BETWEEN.
Using Fetch Unique Scans.
Updating Key Columns.
Avoiding User Propagation of Filters.
Using TID Scans.
Using Parallel Serial Scans.
Using the BULK Option.
Analyzing Queries with GENPLAN.
Modifying the Access Optimization Plan with SETOPT

Chapter 3 Guidelines on Query Design

Technical documentation

» Table of Contents

» Index