Using Index in oracle

Question

How to use index in a select query in oracle? When creating indexes can enhance performance?

How to use indexed explicitly?

Answer 1

You don't have to do anything to use an index in a select query - Oracle will decide what index, if any, will best help perform the query. Index help performance in some queries (not all) by providing a short-cut to the data you want. You should read up on indexes in the Oracle Concepts Guide - read the section headed "Overview of Indexes".

Answer 2

Usually, you don't need to say anything on the query in order to use the index. Oracle's plan evaluator will pick (hopefully) the best way to execute that query for you. It's one of the Codd's principles to relational database.

When creating indexes can enhance performance?

Several scenarios. Some of them:

1. When you try to search for a criteria (i.e.: select * from foo where field = 121, it will help if your table is big and you have an index on field)
2. When you want to sort your results. (i.e.: select * from foo order by field, it will help if your table is big and you have an index on field)
3. When you perform joins

Answer 3

In case oracle comes up with a bad execution plan and you really really have to tell it which index to use (happens very seldom, but it happens), you can user optimizer hints:

SELECT /*+ INDEX(mytable myindex) */ foobar from mytable

But that should be your last resort. Before you even try to do it that way, use

execute dbms_stats.gather_table_stats('myschema','mytable')

to tell Oracle to analyze the table; hopefully, it will find out when to use the index that way.