An index only makes sense if SQL statements that use the index return less than 5% of the table records. The index fields must therefore significantly reduce the resulting sets. Otherwise, the database optimizer would perform a full table scan anyway.
Indexes must not be contained in other indexes (that is, they must be disjunct), because the cost-based database optimizer can also select the index with the upper quantity. In addition, do not create indexes that can be selected accidentally.
To keep additional work for the database to a minimum, create as few indexes as possible for each table (approximately 5 indexes per table).
As a general rule, an index should consist of a maximum of 4 fields. If too many fields are specified in an index, additional work is created every time a database operation is performed, and the memory space required grows accordingly. Consequently, the index becomes less effective and the probabilty of it being selected is reduced.
The selective fields in an index should be as near to the beginning of the index as possible (see index range scan access strategy). Selective fields are, for example, document number, material number, and customer number. Unselective fields are client, company code, header account, and plant.
Before you perform a technical selectivity analysis, you must be sure what the various index fields in question mean. You should therefore type index fields according to their meaning.
Identifiers: These are particularly selective table fields. They are usually characterized by consecutive numbers (document number, object number, and so on) since they are assigned using a number range object.
Organizational units: These are fields such as sales organization, company code, or distribution channel. They are often very unselective, and should only be used in secondary indexes in exceptional circumstances.
Status fields: These can be very selective if, in an SQL statement, values are selected where only a few corresponding data records exist in the table (for example, open orders, if most of them are complete). Conversely, they can also be very unselective.
Classifiers: These are fields where typing is performed (for example, sales order, planned order, and production order). In general, classifiers are not selective, as few different versions exist, and they are usually distributed relatively evenly.
Date and time: These are often selective, and can be used in a secondary index.
Text fields: These are generally selective, but they are also very long. They should therefore not be used in a secondary index, because it would become too wide.
No comments:
Post a Comment