what is an Oracle index

An Index is a tree structure that allows direct access to a row in a table. Indexes can be classified based on their logical design or on their physical implementation. The Logical classification groups indexes from an application perspective, while the physical classification is derived from the way the indexes are stored.

An index is a schema object that can speed up the retrieval of rows by using pointers. If you do not have an index, then a full table scan occurs. Its purpose is to reduce disk I/O by using an indexed path to locate data quickly. If a table is dropped, the corresponding indexes are also dropped.

It is created in existing table to locate rows more quickly & efficiently. The users cannot see the indexes; they are just used to speed up the queries

Oracle provides many different types of indexes:

B*Tree Indexes Index Organized Tables B*Tree Cluster Indexes Reverse Key Indexes Descending Indexes Bitmap Indexes Function-based indexes

USER_INDEXES data dictionary view contains the name of the index and its uniqueness USER_IND_COLUMNS view contains the index name, the table name, and the column name.