Database Reference
In-Depth Information
Chapter
12
DBMS-Specific Indexing
Restrictions
ž
Restrictions applied to indexes by relational database management
systems
ž
Maximum number and length of the index columns
ž
Variable-length columns
ž
Maximum number of indexes per table
ž
Maximum size of an index
ž
Index locking
Index row suppression with null values
ž
INTRODUCTION
Index design is, to a large extent, independent of the DBMS; by far the most
important decision is which table columns should be copied into the index. There
are, however, a few restrictions that are DBMS and version specific.
NUMBER OF INDEX COLUMNS
The maximum number of columns that may be copied to an index varies between
16 and 64. Not everybody sees this as a problem. Gulutzan and Pelzer (1, p. 231)
presents a surprising recommendation:
The general recommendations that apply to all DBMSs, then, are:
ž
Use up to 5 columns in a compound index. You can be sure that the
DBMS will allow at least 16, but 5 is regarded as the reasonable
maximum by some experts.
Nobody should drink more than five cups of coffee a day because some doctors
consider this a reasonable maximum!
Search WWH ::
Custom Search