|
This question already has an answer here:
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. |
||||
|
|
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
|
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 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 |
|||
|
|
