does it matter to access tables in the order of their indexes?
I have a table tab1(col1 number, col2 number) and created two indexes (non clustered) on these two columns.
So if i want to select col1 , with conditions on col1 and col2, then does it really matters to select the order of the
Indexes? I mean some thing like this
Query 1 Query 2 Could any body please tell ,from the above two queries which query would run faster?
no, in Oracle the order of your where criteria in the select won't have an effect. Oracle has a built in Optimizer which analyzes your query, checks which indexes are available and then decides if any of the indexes will be used or if a full table scan will be performed.
In earlier days this Optimizer was a so called rule based optimizer, which used some rules (as the name implies) to find out what to do, e.g. it would prefer the index of the pk over a non unique index, etc.
Nowadays usually a cost based optimizer is used. For the cost based optimizer to work properly you need statistics of your tables (including the indexes). The cost based optimizer then will prefer the most selective index. If in your example the column col1 holds 100 different values and table col2 holds 1000 different values then the index on col2 will be used - to describe it in a simple way. In fact it is much more complicated.
If you use a cost based optimizer and you have no statistics, then it uses the rules of the rule based optimizer.
As I mentioned before this topic is in real life a lot more complicated. E.g. statistics might get stale or might be misleading (e.g. if there are only two distinct values, 'Y' and 'N', but 99.99% of the entries have 'Y', then it would be good to use the index if you're looking for the entries with 'N'. For this there is an additional feature called histograms).
I hope this answer was not an overkill to your question. If you want further information you can google "Oracle Cost Based Optimizer" or start from here: