Impact of Order of fields in Secondary index

[Former Member]

Hi all,

I have a doubt that wether the order of fields and number of fields cause any impact on performance.

Like if I am creating a secondary index Z01 for some table X with fields a,c,b,d,e,h,f and in my select query the where clause is like a,b,c only will it will redume my performance.

Thanks in Advance,

Anmol.

Moderator message - Moved to the correct forum

Edited by: Rob Burbank on Jan 5, 2010 4:14 PM

[Former Member]

This question has been asked before here, so if you search you should get some ideas.

The short answer is "yes" but it all depends on selectivity.

Rob

[Former Member]

Hi

an index can be used partially, but it's important the order of fields in where clauses respects the order of the index fields, that means the clauses should be a,c,b if the index is a,c,b,d,e,h,f.

Max

[Former Member]

Hi Anmol ,

Search SDN before posting.

Order of fields in where clauses should be same as order of the secondary index fields for better performance.

Hope this resolves your issue.

[Former Member]

>

> Search SDN before posting.
> Order of fields in where clauses should be same as order of the secondary index fields for better performance.

That, of course, is the problem with searching the forum.

@Rui - I really didn't explain what I meant, so I'll try again.

When constructing an index, you should put the fileds that you have a good chance of konwing at the beginning of the index. If you have two fields: a and b but you will likely only know b at run time, then make b the first field of the index.

Rob

[former_member182566]

> @Rui - I really didn't explain what I meant, so I'll try again.

>

> When constructing an index, you should put the fileds that you have a good chance of konwing at the beginning of the index. If you have two fields: a and b but you will likely only know b at run time, then make b the first field of the index.

Hi Rob. Yes, I understood what you were saying, but the OP question was:

- index with a,c,b,d,e,h,f

- query with a,b,c (and not a,c,b as in the index)

so I understood that he wanted to know if the order in the where clause was important.

if his where clause only had b,c (and not a) then of course I would agree with you.

Rui

[Former Member]

And I was looking at

> Like if I am creating a secondary index Z01 for some table X

So I took the question to be about creating the index.

If the OP wants a good answer, he has to ask a good question.

Rob

[Former Member]

Hi Rob,

The Question was not about creating the index I want to know that is sequence is important in case of secondary indexes to be choosen by DB OP.

Thanks.

Anmol.

[Former Member]

>

>           The Question was not about creating the index I want to know that is sequence is important in case of secondary indexes to be choosen by DB OP.

That may be what you meant, but I saw what I saw

And so the answer to this question is no. As suggested earlier, try it yourself (and repeat multiple times).

Rob

Edited by: Rob Burbank on Jan 6, 2010 11:12 AM

[Former Member]

Hi

Just as I wrote I think it's important if the table have many indexes.

The optmizer checks the field names of where clause in order to choose the index, if the same fields are used in different indexes, it'll try to use the index having the same order.

So I believe it's better to use the same order of the index needs to be used, anyway why to use a different order?

Max

[Former Member]

HI Max,

I need to use the same index for different queries tht why the sequence is diff from the clause, should I create diff indexes for diff clauses.. And again u had talk about skip mechanism, will it work.

Like if the clause is a,c,e,f and index is a,b,c,d,e,f wil it take all four search columns.

Thanks.

Anmol

[former_member182566]

Max, suppose you have 2 indexes:

1st index A, B, D

2nd index C, B, A

you want to make a query with where clause A, B, C, D.

which sequence would you use in the select? there is no way to respect both orders!

It also seems to me that you are suggesting that you should use the sequence in the where clause to influence the optimizer decision, but this does not make sense. On one hand, the optimizer will not be influenced by this. On the other hand, you are assuming that there is a correct index; this is old-fashioned RULE thinking. Modern optimizers are influenced by table statistics and the "correct" index today will not be the "correct" index tomorrow.

(moderators: sorry if this is straying away from the original question)

[former_member182566]

As said this has been answered before, but I would disagree with Rob: the short answer is "no". (which is even shorter than "yes".. )

- It is important that you use the first fields of the index in the select clause (in this case you are using the first 3)

- For any optimizer worth the name it should be irrelevant if the where clause has "a,b,c" or "a,c,b" or "c,b,a" or whatever.

- For the longer answer: you should try to use always the same sequence to reduce the SQL cache size, and the criteria some people use is to have the same sequence as in the index (though this might not be that feasible, since the table might have several indexes, and the index that will actually be used depends on the table contents and can very over time).

Hope this helps,

Rui Dantas

[Former Member]

Hi All,

I know that this Question is already discussed in the forum, I think this question is not yet resolved.

Some guys says that the Sequence is important and some used to say no it is not. So I want to start the discussion Again.

I hope every one will try to contribute in this and we will come to some sort of conclusion.

Also I need to know that if my index is like a,b,c,d,e,f and the where clause is some thing like a,b,e,f will this query uses all the search fields from the index or it will search for other 1.

Thanks.

Anmol

[former_member182566]

Hi Anmol,

I suggest you try and measure it yourself; you will see that a,b,c or a,c,b in the where clause is the same thing. Remember to measure more than once to avoid caching effects.

About a,b,e,f: most databases will only use the first 2 fields (a,b). Oracle (from 9i) can make an INDEX SKIP SCAN and with that use the other 2 fields (e,f) but that is only efficient if the fields in the middle (c,d) have few distinct values.

Rui Dantas

[Former Member]

Hi Anmol,

When a base table has multiple indices, the where clause should be in the order of the index, either a primary or a secondary index.

To choose an index, the optimizer checks the field names specified in the where clause and then uses an index that has the same order of the fields.

Srilatha.

[former_member182566]

>

> To choose an index, the optimizer checks the field names specified in the where clause and then uses an index that has the same order of the fields.
> Srilatha.

Ok, I think I will just give up, but before that:

Srilatha, optimizers use complex algorithmics developed by intelligent guys. Isn't it rather insulting to them to suggest that the best thing they could think of is to use the sequence of the fields in the where clause? This would not be really "optimizing", would it?

[Former Member]

>

> Ok, I think I will just give up, but before that:

That was my feeling before getting into this

A couple more points.

This thread originally started off in the data dictionary forum and I moved it here. That's why I thought this was about creating an index.

I think it's just good programming practice to put the WHERE in the same order as the index you expect. Whoever comes along next to maintain the program will have a better idea of what you wre thinking.

Rob

[former_member194613]

> To choose an index, the optimizer checks the field names specified in the where clause and then uses an index that has the > same order of the fields.

No, that is not true. Many people seem to believe that, but no optimizer works in such a simple way.

This question was actually answered several times. But wrong answers still appear very often.

The order of the fields in the WHERE condition are IRRELEVANT for the optimizer. Every optimizer chooses the index with the lowest costs for the access. Costs and selectivity are actually nearly never discussed in this forum. Unfortunately the discussion is not so easy. If you are able to read German, then you will find a detailed discussion here:

http://www.dpunkt.de/buecher/3096.html

And as already said above - independent of the performance - we recommend to use the same order of the fields in the

WHERE condition as in the table. That is nearly no performance impact, it reduces the amount of data in the cursor cache.

Statements are preprocessed by the database interface and it is checked whether an identical statement was already executed, maybe by a very different program. If so, the optimizer is not called but the result is directly taken from the cursor cache (i.e. small performance impact). But here the size of the cursor cache is more important than the small performance impact.

Siegfried

[former_member182566]

>

> you are able to read German, then you will find a detailed discussion here:
> http://www.dpunkt.de/buecher/3096.html
> Siegfried

Siegfried, for us common mortals, can we expect an english edition?

[Former Member]

So, After the wide discoursion, may be the conclusion is that

Optimizer chooses the index which is having list value

Sequence in the where clause is irrespective of the sequence in INDEX.

Pls correct me if anything is wrong.

Anmol

[Former Member]

Marking Question as answed but still can add valuable remarks.

Anmol.

[former_member194613]

>Optimizer chooses the index which is having list value

>Sequence in the where clause is irrespective of the sequence in INDEX.

too simple

Sequence in the WHERE clause should follow the sequence of the table, it wull not influence which index is used. Sequence in index is crucial, if this a value in WHERE missing then only fields before the gap can be used.

Optimizer evaluates the indexes by their usefulness for the WHERE clause (only filled select-options, not pure coding!). The evaluation uses statistical information and is unfortunately not so easy to explain it a few lines.