When you are sure about what the index fields mean (that is, their semantic meaning), check how many distinct values there are for each index field. Distinct values are the number different values per field in a particular database table.
The relationship between the total number of data records in a table and the distinct values per field indicates how selective the index field is.
When the table and index statistics are updated, the distinct values are re-calculated. For index fields, the number of distinct values can be determined using Explain SQL (DB SQL cache or SQL trace).
For an overview of the distinct values for all fields of a database table, from the SAP standard menu choose Tools >> Administration >> Monitor >> Performance >> Database >> Activity >> Detail analysis menu >> State on disk, or use Transaction DB02. Then choose Detailed analysis. In the dialog box that appears, enter the table name under Object name, and confirm. In the next screen, choose Table columns.
The number of data records returned for each combination of index fields shows how selective an index is. This means that, if only a few data records are returned for a large number of index field combinations, an index is selective.
For an overview of how selective an index is for all index field combinations, a histogram can be calculated. Calculating a histogram is very expensive, because the number of data records returned must be checked for all index field combinations over the whole table.
To perform a selectivity analysis, use Transaction DB05. In the initial screen of DB05, enter a table name, select an analysis for primary key or for specified fields, specify the index fields if applicable, and submit the analysis in background or dialog mode.
If quotients are calculated from the total number of table records (635,336) and the number of distinct values for the index fields (16,607), the results show that an average of 30 data records are returned when the index is used.
If the number of distinct values does not increase from one index field to the next index field, this index field is not selective. It should therefore be omitted.
Remember that a selectivity analysis is an expensive operation, which should only be performed at times of low system activity.
46
RELATED POST
CHANGING THE INDEX DESIGN IN ABAP DICTIONARY PART ONE
series.
abap communication interface 1
abap communication interface 2
abap communication interface 3
abap communication interface 4
abap communication interface 5
abap communication interface 6
No comments:
Post a Comment