HP 3000 Manuals HP 3000 Manuals
Avoiding Serial Scans Through Query Design
To design efficient ALLBASE/SQL queries, you should keep in mind the
conditions which force ALLBASE/SQL to perform serial scans rather than
faster index scans. Since certain classes of predicates cannot be
evaluated using an index scan, you should avoid using such predicates in
queries on large tables.
Arithmetic Expressions
An index scan cannot be used to evaluate a predicate that contains an
arithmetic expression. For example, ALLBASE/SQL performs a serial scan
for the following query:
SELECT W
FROM T1
WHERE X = Y + Z - W
The following approach avoids the problem by assigning the result of the
computation to a host variable which is then used in the predicate:
HostVar = Y + X - W
SELECT W
FROM T1
WHERE X = :HostVar
Columns from One Table on Both Sides of the Relational Operator
An index scan cannot be used to evaluate a predicate with columns from
the same table on either side of a relational operator. For example,
ALLBASE/SQL performs a serial scan for the following query:
SELECT W
FROM T1
WHERE T1.X = T1.Y
Data Conversions
Conversion takes place in the predicates of queries. For example, in the
predicate WHERE X = Y, a type conversion takes place whenever X and Y are
not of the same data type. In general, you should avoid data type
conversion, since it may mean that the optimizer will choose not to use
an index. Avoiding conversions is partly a matter of table design--in
defining columns with compatible data types--and partly a matter of query
design. Therefore, it is important to understand the queries that will
be used with the database before creating the tables.
For best results, columns constantly compared in the WHERE clause should
be of the same data type and size; thus DECIMAL types should have the
same precision and scale.
You can ensure that the optimizer is able to choose an available index by
making sure that data conversion in your predicates is of an acceptable
type, that is, that data types are compatible. In some cases,
conversions are required which may result in the loss of significant
information. In these cases, an index is not used. The following
conversions do not result in the loss of significant information, because
the data elements in them are compatible:
1. CHARACTER to CHARACTER or VARCHAR
2. VARCHAR to CHARACTER or VARCHAR
3. INTEGER to DECIMAL(p,s) where (p-s) >= 10
4. INTEGER to FLOAT
5. SMALLINT to INTEGER
6. SMALLINT to DECIMAL(p,s) where (p-s) >= 5
7. SMALLINT to FLOAT
8. DECIMAL to FLOAT
9. DECIMAL(p1,s1) to DECIMAL(p2,s2) where s2 >= s1 and (p2-s2) >=
(p1-s1)
10. DATE, TIME, DATETIME, or INTERVAL to CHARACTER or VARCHAR
11. CHARACTER or VARCHAR to DATE, TIME, DATETIME, or INTERVAL
In comparisons that result in conversions from INTEGER or SMALLINT
constants or host variables to DECIMAL, an index may be used if the
number of places to the left of the decimal point in the DECIMAL type
(i.e., p-s) can accomodate the largest value yielded by the INTEGER (or
SMALLINT). For example, a SMALLINT value is compatible with a DECIMAL
(10,2), but an INTEGER value is not compatible with a DECIMAL (10,2).
Comparisons that result in conversions between an INTEGER or SMALLINT
column and a DECIMAL expression can only use an index if the following
are true:
* The DECIMAL scale is 0 and
* The DECIMAL precision is
* less than 10 for an INTEGER or
* less than 5 for a SMALLINT
In the case of DECIMAL fields, the scale of both DECIMAL elements plays a
role in determining whether or not an index can be used. For example, in
converting between two DECIMAL elements, ALLBASE/SQL does use an index if
the scale of the comparison value is greater than the scale of the column
data type.
In the following query, an index can be used because both sides of the
conversion are decimal, and the right side (comparison value) is
convertible to the left (column data type) without loss of significant
information:
decimal
(7,2)
SELECT W ---------------------
FROM T1 | x | y | z | w |
WHERE X = 25.0 ---------------------
| | | | |
| | | | |
| | | | |
---------------------
Table T1
In this example, the left side (p2,s2) is DECIMAL (7,2) and the right
side (p1,s1) is (3,1). We see that s2 is greater than s1 and p2 - s2 (5)
is greater than p1 - s1 (2).
To retrieve data from an established database, you might need to compare
columns in the WHERE clause that contain different data types. But there
are several ways to help the optimizer by doing your own data conversion.
For example, you can express an INTEGER constant as a DECIMAL when it is
to be compared with a DECIMAL. In the following, assume that X is
decimal:
Instead of SELECT W FROM T1 use SELECT W FROM T1
WHERE X = 25 WHERE X = 25.0
Predicates with INTEGER = DECIMAL(n,0) Factors
Factors of the form Integer = Decimal (n,0) can be optimized by
ALLBASE/SQL provided n is less than or equal to 10. Factors of the form
Smallint = Decimal (n,0) can be optimized provided n is less than or
equal to 5. In the following, an index may be chosen, since the decimal
scale is zero and the precision is within the appropriate bounds. Assume
that X is an integer:
SELECT W FROM T1
WHERE X = 45.