ALLBASE/SQL Reference Manual (36216-90216)

Chapter 2 71
Using ALLBASE/SQL
Understanding Data Access Paths
more of the columns in the query. If an index is available and if the optimizer decides that
using the index is the fastest way to access the data, ALLBASE/SQL looks up the key
values in the index first, then goes directly to the pages containing table data.
For example, in the following query, assume that PurchDB.Parts contains a large number
of rows and that a unique index exists on the PartNumber column:
isql=> SELECT PartName, SalesPrice FROM PurchDB.Parts
> WHERE PartNumber = '1323-D-01';
The optimizer would probably choose this unique index for access to the single row because
the alternative choice--a serial scan--would require reading each page in the table until the
qualifying row is reached.
You define an index with the CREATE INDEX statement. The components of an index
definition are as follows:
1. Type of the index (optional)
2. Name of the index
3. Table on which the index operates
4. Key column(s)
The following example contains numbers that refer to the index components listed above:
1
|
CREATE UNIQUE INDEX
PartIndex --2
ON PurchDB.Parts --3
(Partno) --4
ALLBASE/SQL can choose to use an index when processing the SELECT, UPDATE, or
DELETE statements if the following criteria are satisfied:
• The statement contains a WHERE clause, which consists of one or more predicates. A
predicate is a comparison of expressions that evaluates to a value of True or False.
Refer to the “Search Conditions” chapter for more information on predicates.
• The statement contains explicit join syntax.
• Predicates are optimizable, which means that the use of an index is considered in
choosing an access path for the data. The following predicates are optimizable when all
the data types within them are the same; in the case of DECIMAL data, the precisions
and scales of the values must be the same:
— WHERE
Column1 ComparisonOperator Column2 , in which
ComparisonOperator is one of the following: =, >, >=, <, or <=. An index may be
used if
Column1 and Column2 are in different tables and an index exists on either
column. For example:
WHERE PurchDB.Parts.PartNumber = PurchDB.SupplyPrice.PartNumber
— WHERE Column1 ComparisonOperator (Constant or HostVariable) , in
which
ComparisonOperator is as defined above. An index may be used if one exists
on
Column1 ; however, an index may be used if a host variable appears in the