Interesting fact

This might not be new for some but I though of posting it here for others :

Database : Any version
Optimizer : RULE (or CHOOSE without stats)

A table has two indexes with 3 and 5 columns respectively in them. A query uses first column (and only that column) from both these indexes in its WHERE clause. The plan shows INDX_1 (with 3 columns) being used.

The table was reoraganized and both the indexes recreated. Now, the plan for the same query shows INDX_2 being used. Use of this index is not proper because it is less selective and hence slows down the query.

WHY IS THE CHANGE IN THE PLAN ? How reorg affected plan ?

Answer is : For typical queries like those described above - using one (which is the leading column) column from more than one indexes in the WHERE clause, the RBO decides to use the index that was 'created' LAST !!

So, in my case, before reorg, INDX_1 had a later 'created' timestamp than that for INDX_2 and it was getting used. During reorg, it happened that INDX_2 got created after INDX_1 and the RBO started using INDX_2.

It turns out that this is a 'documented' stuff.

Isn't this interesting ? And : all you DBAs that take care of databases running under RBO take care of this when you decide to reorg a table and its indexes ? Moreover, this rule is applicable to PK as well !!

Any comments ?

[Edited by svk on 01-04-2002 at 11:44 AM]

no comment coz none of app here runs rule :D

Quote:

---

Originally posted by svk
Now, the plan for the same query shows INDX_2 being used. Use of this index is not proper because it is less selective and hence slows down the query.

---

Why do you think that INDX_1 is more efficient than INDEX_2 in this case? You can't say that only based on the number of columns in the index! It might very well be the case that INDEX_2 is much more efficient than INDEX_1, unless you forgot to mention that all the columns from INDEX_1 are also included in INDEX_2.

No, columns in these two indexes are totally different.
And I'm not going by number of columns in the indexes. Sorry if that led to any misunderstanding.

Based on the nature of data, I know that INDX_1 is more selective than INDX_2.

And whatever it may be, RBO should not know anything @ the 'selectivity' of an index. Still the plan changed after the reorg !!

If based on predefined rules for RBO, the cost of access paths is same, then index which was last created (only for non-unique) is used.

No !!!!!!!!!
Even PK is not exception to this rule.

RBO may choose to use a non-unique index over the PK in a situation that both are composite key indexes and only first column from each of them are used in WHERE clause of the query.

True, RBO does not know anything about the selectivity of the indexes - that is why both of this indexes are totaly the same for it (in terms of "which one is better"). I was talking in general, not about your true data in those indexes. You know that INDEX_1 is more apropriate for that query, but for RBO both of them are equal. So it has to decide which one to use based on what the programers of RBO have instructed it to do. The programmers of RBO had to "invent" the criteria based on which RBO chooses one of the two equally appropriate indexes. They could probably choose the number of columns in the index as the deciding factor, but they have chosen the date of index creation. They probably had good reasons for that. For example, usually the data in freashly created index is more tightly "packed" together, meaning less I/O.

[Edited by jmodic on 01-04-2002 at 03:49 PM]

So basically, use CBO and analyze data.
And use hints and/or outlines to ensure proper index usage.

This just proves the reason for what is now common practice (or at least should be).