To design efficient ALLBASE/SQL queries, you should keep in mind
the conditions which force the optimizer
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.
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:
CHARACTER to CHARACTER or VARCHAR
VARCHAR to CHARACTER or VARCHAR
INTEGER to DECIMAL(p,s) where (p-s) >= 10
INTEGER to FLOAT
SMALLINT to INTEGER
SMALLINT to DECIMAL(p,s) where (p-s) >= 5
SMALLINT to FLOAT
DECIMAL to FLOAT
DECIMAL(p1,s1) to DECIMAL(p2,s2) where s2 >= s1 and
(p2-s2) >= (p1-s1)
DATE, TIME, DATETIME, or INTERVAL to CHARACTER or VARCHAR
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
|
