Filter
Top Products
Choosing an index type
140
•Each CREATE INDEX statement can create only one index.
• Each connection can create only one index at a time.
• If two connections issue
CREATE INDEX statements on the same table, the
first statement works; the other gets an error saying that only 1 writer is
allowed.
• If two connections issue
CREATE INDEX statements on different tables,
both proceed in parallel
• If two connections issue
CREATE INDEX statements on different tables but
both tables participate in the same join index, then only one
CREATE
INDEX
works; the other gets an error saying that only 1 writer is allowed.
Choosing an index type
The set of indexes you define for any given column can have dramatic impact
on the speed of query processing. There are four main criteria for choosing
indexes:
• Number of unique values
• Types of queries
• Disk space usage
• Data types
Use the recommendations for all criteria in combination, rather than
individually. Remember also that all columns are automatically stored in a way
that facilitates fast projections. To decide on additional indexes, look closely at
the data in each column. Try to anticipate the number of unique and total
values, the query results users will want from it, and whether the data will be
used in ad hoc joins or join indexes.
For details of index types, and criteria to use for choosing the correct types, see
the sections that follow.