Sybase 12.4.2 manual Choosing an index type, 140

Choosing an index type

•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.

140