Secondary index - Considerations

[Former Member]

Hi,

I have to create a composite secondary index ( multiple fields ). What are the considerations in deciding the order of the fields in this index.

The where clause of the query will always use single value select for both these fields

eg : select F1 F2 from TAB where I1 = <X> and I2 = <Y>.

I1 and I2 are the fields that i want to index on.

Regards

Rajesh

[lbreddemann]

Hi Rajesh,

if this query really always looks the same, you should consider to include the selected columns into the index as well so that a index only strategy can be chosen.

The index then should look like this:

(I1, I2, F1, F2)

regards,

Lars

[stefan_koehler]

Hello Rajesh,

> eg : select F1 F2 from TAB where I1 = <X> and I2 = <Y>.

If the query looks always like that, the order of the index columns doesn't matter.

The order of the keys is important, if your columns have many values and you don't specify it with a "=" in the WHERE clausel.

For example, if your query looks like this "select F1 F2 from TAB where I1 > <X> and I2 = <Y>" and I1 has many values ... then the following index order would be better: I2, I1

Sapnotes #825653 and #176754 are having some examples and suggestions

Regards

Stefan