wood burning stoves 2.0*
The moose likes Oracle/OAS and the fly likes Table Accessing through indexes Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Table Accessing through indexes" Watch "Table Accessing through indexes" New topic
Author

Table Accessing through indexes

Deepika Saxena
Ranch Hand

Joined: Jul 05, 2009
Posts: 59
Hi,
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?
Thanks.
--Deepika
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Hmm. The queries are identical. My guess is they will perform the same

The ordering of columns should not affect which index is called first, query plan calculations are fairly sophisticated things. You could try generating explain plans of both and see?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Deepika Saxena
Ranch Hand

Joined: Jul 05, 2009
Posts: 59
Hi Paul,
I have corrected the queries.
Please have a look.
Thanks.
--Deepika
John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 132
Hi Deepika,

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:

Inside the Oracle Optimizer

John
John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 132
Ahh, by the way: with cost based optimizer the execution plan might change when the statistics are rebuilt, and they might be different between development and production environment, too.

So your explain plan will always be just a snapshot showing you what the execution plan is now in this environment.

With a rule based optimizer the execution plan was "fixed" unless someone added optimizer hints or added/dropped/modified some indexes.

But the advantage of the cost based approach is that it adapts itself to changed data conditions and does this very well in most cases.
Deepika Saxena
Ranch Hand

Joined: Jul 05, 2009
Posts: 59
Thank you very much john,
That is really a very good explanation for better understanding of indexes.

--Deepika
 
 
subject: Table Accessing through indexes
 
Similar Threads
Simple sql
Update with FROM table concept
Regarding java caching framework
result set access with multiple tables, same column name
Question of using SqlResultSetMapping