CBO - Order of columns in Index

[rpardip]

I am running Oracle with CBO.

I have a composite index (colA, colB) on a table.

Does the order of columns in the WHERE clause matter???
OR do they have to be in the same order in which the index
was created??? Rememeber, under CBO.

[chrisrlong]

So I take it that you didn't like the answer to your last post of this question :)

One of the nice things about the CBO is that it does not matter what order you do anything in the actual query (with one small exception). The plan will be the same. I'll get to the exception in a minute. Mind you, I said that the 'order' of things in the query is irrelevant. The 'structure' of the query (join vs sub-select, etc.) can be extremely important.

What *is* important to you is the order of the index. And *how* it is important is based on when you do *not* have all the columns in the WHERE clause. If you *always* have both columns in the WHERE clause, then the order of the columns in the index really doesn't matter to the CBO. (There may be some esoteric differences, such as the index compression mentioned in an answer to your previous post).

But we're not done. First of all, I doubt that you will *always* use both columns in the WHERE clause. Kudos if you do, but it would be a rare thing indeed. Usually, you have 2 indexes on the table

PKIDX (Col1, Col2)
IDX2 (Col2)

This will cover every permutation of WHERE clause for you. You could also make:

PKIDX(Col2, Col1)
IDX2(Col1)

This is up to you. The choice would be based on which of the 2 columns are usually found in the WHERE clause by themselves. Whichever one is used alone more often should have its own index, because it will be a smaller index. If they are equally likely to be by themselves, then put the smaller field by itself. If they are also the same size, then put the more selective one by itself. If they also have the same selectivity, then flip a coin :)

You must also consider the issue of FK indexes. I would guess that both columns are Foreign Keys to other table. If so, then you must worry about this issue. If you plan on deleting entries from the parent tables, or updating their keys, then you will want indexes on the FK fields in the child. And they must be the left-leading field in the index to count. My link to the white paper on this is broken since Oracle changed their damn site, so you'll have to find that yourself.

Finally, back to the one exception I mentioned earlier. If the CBO determines that there is more than one way to attack your statement *that has the same, cheapest cost*, then it will use the one that matches the order of the FROM clause from top to bottom. In other words, if you do

SELECT * FROM T1,T2 WHERE T1.PK=T2.PK

If both tables are approx the same size, with the same stats and joining in either order would be equivalent in the CBO's eyes, then it will do T1,T2 because that is what the query says. This is actually an insignificant fact because it only applies, basically, when it doesn't matter, so don't worry about it. I only mention it because if I didn't, someone else would correct me later anyway :).

However, as a matter of style, it is a good idea to try to structure your FROM clause and WHERE clause in the order that *seems* logical, ie. in order of what the plan will probably be anyway. But now I'm just being anal :).

Hope this helped,

- Chris

[rpardip]

chrisrlong,

Thank you very much for your time and patience in explaining this things here.