Interpreting results of DB05 transaction

[Former Member]

Hi,

Does anybody has idea how to interpret the results of DB05 analysis of table with respect to index fields. Based on its output is it possible that we can find out the feasibility to create new index fields on the table or not ? ie

I have a table t1 and it has fields f1 f2 f3 f4. we have an sql in which where clause is used on fields f3 and f4. There are no indexes for f3 and f4 which is degrading the sql performance. now i want to create indexes for fields f3 and f4. So in this case can i use :

• db05 transaction to find out the feasibility of creating indexes on f3 and f4.

• how to interpret the results of db05 transaction after i do an analysis of fields f3 and f4.

• if i cannot use db05, what are the other options available.

Expecting helpful replies.

Regards,

Sandy

[Former Member]

In DB05, enter the table name and the names of the fields that you plan to create an index for (select the specific fields radio button).

The next screen shows the cumulative distinct values of proposed index fields. The number of distinct values should increase with addition of each field to the index i.e, one line to the next. Also, the numbers to the right under 101-1000...100,101-1,000,000 etc mean that a particular search string value occurs so many times. The higher the number under these columns, the likelihood of fetching a high number of records for a particular value.

In summary, you would like to see higher numbers as you go down the list and higher numbers to the left, smaller numbers, ideally zeros, to the right. Take a snap-shot of these figures.

Go back to the main screen and change the sequence of the fields and analyze again and compare with the earlier sequence of fields.

Should you decide to create the index, the above analysis will tell you the best possible sequence for the index fields. Select the sequence of fields which has the best distribution, higher values as you go down the list and higher values to the right as you go down the list.

2. Identify fields which are not increasing the selectivity as you go down the list as these may not add much value to the index. Consider excluding them from the index.

3. The optimizer may decide to use this index when partial fields are specified. So, if possible, choose the sequence of fields that provides the bext selectivity for partial as well as full key fields.

Regards,

Rao A