Chapter 9 SQL Statements
449
♦
Automatically created indexes Adaptive Server Anywhere
automatically creates indexes for primary keys and for unique
constraints. These automatically created indexes are held in the same
database file as the table.
UNIQUE keyword The UNIQUE attribute ensures that there will not be
two rows in the table with identical values in all the columns in the index.
Each index key must be unique or contain a NULL in at least one column.
There is a difference between a unique constraint on a table and a unique
index. Columns of a unique index are allowed to be NULL, while columns in
a unique constraint are not. A foreign key can reference either a primary key
or a column with a unique constraint, but not a unique index, because it can
include multiple instances of NULL.
ASC | DESC option Columns are sorted in ascending (increasing) order
unless descending (DESC) is explicitly specified. An index will be used for
both an ascending and a descending ORDER BY, whether the index was
ascending or descending. However, if an ORDER BY is performed with
mixed ascending and descending attributes, an index will be used only if the
index was created with the same ascending and descending attributes.
IN | ON clause By default, the index is placed in the same database file as
its table. You can place the index in a separate database file by specifying a
dbspace name in which to put the index. This feature is useful mainly for
large databases to circumvent file size limitations.
$ For more information on limitations, see "Size and number limitations"
on page 958.
WITH HASH SIZE clause By default, at most ten bytes of each index
entry is actually hashed (stored in the index). If a query needs access to more
than the hashed data, it needs to fetch it from the underlying row, and this
may affect index performance. You can explicitly configure the amount of
data that is stored in the index by specifying WITH HASH SIZE. Increasing
the hash size can reduce the number of times the underlying row needs to be
accessed. However, this is at the expense of a decreased index fanout. For
optimal performance, you should not increase hash size for all indexes, only
for indexes for which the first ten bytes of data does not provide high
selectivity.
If the data in the index does not require the whole space set aside by the
WITH HASH clause, it is not used. For example, a single column index
holding an integer (four bytes) takes only five bytes for each index entry,
even if the WITH HASH clause specifies a larger value.
The maximum hash size is 64 bytes.
The MAX and SIZE keywords are optional.
Parameters
To Next Page
Loading...