I want to understand :

How index work in oracle.

How they are stored in the disk.

How they increase the efficiency of a query?

By third point I means how Index contribute to the efficiency of query such as in joins,group by clauses.

One more point is How index behave in Flashback queries.


1 Answer 1


If a heap-organized table has no indexes, then the database must perform a full table scan to find a value.

Following link provides information on indexes in detail http://docs.oracle.com/cd/E11882_01/server.112/e25789/indexiot.htm#CNCPT721 here you will find details of various kinds of indexes Oracle supports

If you have query like

Find a,b,c where emp_name = 'Adam Smith'

In case you have an index on column emp_name than instead of searching the entire table the data can be fetched directly. In case the column is not unique multiple rows that have emp_name Adam Smith will be fetched.

On large tables Indexes can improve the performance of queries even 100 times.

Performance improvement depends on the selectivity. In million values in a column if 100% are unique you get maximum selectivity and performance improvement is maximum, but if you have only 2 different values of a column say like 0 or 1 or yes or no you may get some improvement or no improvement or even degradation.

There are different types of Indexes like B-Tree, Bitmap, Bitmap join etc. Most commonly used indexes are B-Tree.

B-tree is a tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time. Indexes are stored as B-Tree on disk

You can find details of B-Tree indexes on the following link http://en.wikipedia.org/wiki/B_tree_indexing

Not the answer you're looking for? Browse other questions tagged or ask your own question.