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.
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
