ALLBASE/SQL Reference Manual (36216-90216)

72 Chapter2
Using ALLBASE/SQL
Understanding Data Access Paths
predicate only if the comparison operator is =, >, >=, <, or <= . For example:
WHERE SupplyPrice = :SupplyPrice
— WHERE Column1 BETWEEN ( Column2 or Constant or HostVariable ) AND
(
Column2 or Constant or HostVariable ). For example:
WHERE OrderNumber BETWEEN '1123-P-01' AND '1243-MU-01'
• Some queries which use the MIN or MAX aggregate function on an indexed column as
follows are optimizable:
— MIN/MAX column is the first column of a nonhashed index.
— MIN/MAX indexed column on a single table with or without predicates.
— MIN/MAX indexed column on the outermost table of a nested loop join query.
— Single MIN/MAX within one query.
• ALLBASE/SQL does not use an index in the following types of queries:
— The query contains a WHERE clause using a not-equal (<>) arithmetic operator, such
as, WHERE
Column1 <> ( Column2 or Constant or Host Variable ). For example:
WHERE VendorState <> :VendorState
— The query contains a predicate using an arithmetic expression. For example:
WHERE Column1 > Column2 *: HostVariable
— MIN or MAX is used with the GROUP BY, ORDER BY, or HAVING clause.
—AMIN or MAX indexed column exists in the inner table of a nested-loop, join query.
—AMIN or MAX indexed column exists on all tables of a sort-merge, join query.
— MIN or MAX is used with an expression.
— One query contains multiple MINs or MAXs.
—ALIKE predicate contains a host variable.
If other predicates are used, then an index is considered in choosing an access path.
For more information about indexes, refer to the “Designing Indexes” section in the
“Logical Design” chapter of the ALLBASE/SQL Database Administration Guide.
Hashed Access
Hashed access requires you to specify hashing when you create the table, before loading
data. Because a hash structure is specified as part of the table definition, you do not assign
a name to it, as you do with an index. However, you must identify specific key columns and
a number of primary pages for data storage. ALLBASE/SQL determines the placement of
rows based on specific unique key values. You can define one hash structure per table at
table creation time; and if a hash is defined, you cannot define a clustering index on the
table. You can define a multiple-column key for a hash structure; up to 16 columns are
permitted in the key.
A hash structure is a group of designated pages in a DBEFile that are set aside for the
storage of tuples according to the values in a unique hash key. The key enforces