True or false? (Indexes)

[hemanr]

Hi Gurus,
I have a pk index on 3 columns on a table col1 col2 col3.
In my select
I say select count(*) from table where col1=... and col2=....and col3=....

If I change the order and say

Select count(*) from table where col3=... and col2 =.... and col3=....

Will it make any difference?

I read that if the select order is different from that of the index order, the select will not use that index....

[smoothyc]

True, if you change the "where" clause like so, the index will not be used.

Oracle uses indexes based off the order of the "where" clause. The difference will be a longer run time (depending on the number of rows).

When Oracle sees col3 as your first statement in the where clause, it will then search for an index with col3 as it's first column.

If it doesn't find that, then it's a full table scan (unless you force it to use that index...via "hint" command, but you could be causing yourself even more heartache with that)

Therefore it's always a good idea to know how your data will be queried, that way you can always create useful indexes.

charles

[hemanr]

Hi Charles,
Thats what I thought. But when I ran an explain plan on the select by keeping the order the same as in the index, I got this:
SELECT STATEMENT Cost = 1

1
SORT AGGREGATE

2
INDEX UNIQUE SCAN TABLE_PK

But when I changed the order and ran the explain plan, I still got

SELECT STATEMENT Cost = 1

1
SORT AGGREGATE

2
INDEX UNIQUE SCAN OODI_OUTAGE_TASK_PK

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[kkpareek]

Hi,

If you are using CBO, it will look for indexes on all columns in where clause ir-respective of order and choose the one with least cost.

So do not worry about the order. Even RBO will also use the index ir-respective of the order in where clause.

Kailash Pareek

[woldcl]

smoothyc is right.......... the order of the fields in the where clause DOES make a difference when using indexes. From the output you posted, it appears that there is another index on that third field that the optimizer picked up when you changed the order. It did not use the PK index as it did in the first run.

[Heath]

FYI, here is a previous thread on this topic.
http://www.dbasupport.com/forums/sho...threadid=10509

Heath

[khussain]

I have had the same question for a long time ! Recently I tried it out myself, and discovered that the column order in the where clause did not make a difference in Ora 8.1.5 !

Even after reading the above mentioned previous thread, there were conflicting views.

Can one of the senior member please provide some input on this issue - thanks.

[Zamial]

Well it also depends how accurate the 'compute statistic' information is.

Senior input is always welcome but I think it's impossible to give an actual answer as it may actually work differently 2 months down the line.

Due to the way Oracle implicitly decided to optimize the statement.

As a DBA you get to know YOUR system and tune accordingly I think .

[Heath]

Originally posted by khussain
Even after reading the above mentioned previous thread, there were conflicting views.

Can one of the senior member please provide some input on this issue - thanks.

One of the reasons that I posted that link was that one of our most "senior", i.e. most knowledgeable members is currently taking a vaction and is unable to answer this himself right now. I'm sure jmodic would already have weighed in if he were available right now. So, I thought I'd re-post his earlier answer for him.

Heath

[amar]

2'c from a not-so-junior member

For the CBO, the positioning of the columns is IRRELEVENT (do I have the spelling right?). You can prove it easily by doing the explain plan (you can't argue with the facts hey?). This is true if the CBO decides to USE the index at all.

One of the reasons why the execution plan changes over time is because it is computed in conjunction with the High Water Mark - if the HWM changes, the execution plan might change.


-amar