• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Table Accessing through indexes

 
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Deepika Saxena
Ranch Hand
Posts: 59
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Paul,
I have corrected the queries.
Please have a look.
Thanks.
--Deepika
 
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 133
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 59
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you very much john,
That is really a very good explanation for better understanding of indexes.

--Deepika
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic