INDEXING IN ORACLE DATABASE

A BRIEF INTRO ON INDEX:

Just like a book index helps us in finding the chapter and page number, an oracle index speeds up access time to the rows.Indexes are optional structures associated with tables.An index is also a schema object.We can drop an index without dropping the table it indexes.An index can be created explicitly or automatically.When we drop a table the corresponding indexes of the table are dropped.A unique index gets created when we create a unique key or primary key in a table definition.   The name of the index is the name of the constraint.Indexes can be unique or non-unique.  Unique indexes guarantee that no two rows of a table have duplicate values in the columns that define the index.  Non-unique indexes do not impose this restriction on the column values.The presences of more number of indexes on a table decreases the performance of DML statements, because Oracle must make changes to the indexes associated with the tableFor more clear knowledege and for concept you can use the sql developer tool for that
 two type of index are simple and complexin simple we create index on single column on table whereas in complex we can create one index on one or more columnsanother type are unique and non unique,function based

The B-tree index is the most-used type of index that Oracle provides. It provides fast lookup of rows containing a desired key value. It is not suitable if the column(s) being indexed are of low cardinality (number of distinct values). For those situations, a bitmap index is very useful, but be aware that bitmap indexes are very expensive to update when DML is performed on the indexed table.

When a SQL predicate refers to the value of a function applied to a column (e.g., where upper(lastname = 'SMITH'), function-based indexes can provide fast lookup--the optimizer cannot use a standard B-tree index in the execution plan for such a statement. Function-based indexes apply a specified function (for example, upper()) to the values in the column being indexed, and store the function values, rather than the column values, in the index blocks.

In index-organized tables, the table data is stored entirely in the index B-tree blocks. When most accesses to a table are based on a value of a single key, these can be good choices for maximum performance.