What is Indexes

Indexes are optional structures associated with tables and clusters. You can create indexes on one or more columns of a table to speed SQL statement execution on that table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle index provides a faster access path to table data. Indexes are the primary means of reducing disk I/O when properly used. You can create many indexes for a table as long as the combination of columns differs for each index. You can create more than one index using the same columns
if you specify distinctly different combinations of the columns. For example, the following statements specify valid combinations:

CREATE INDEX employees_idx1 ON employees (last_name, job_id);
CREATE INDEX employees_idx2 ON employees (job_id, last_name);

Oracle provides several indexing schemes, which provide complementary performance functionality:

B-tree indexes
B-tree cluster indexes
Hash cluster indexes
Reverse key indexes
Bitmap indexes
Bitmap join indexes

Oracle also provides support for function-based indexes and domain indexes specific to an application or cartridge.

The absence or presence of an index does not require a change in the wording of any SQL statement. An index is merely a fast access path to the data. It affects only the speed of execution. Given a data value that has been indexed, the index points directly to the location of the rows containing that value. Indexes are logically and physically independent of the data in the associated table. You can create or drop an index at any time without affecting the base tables or
other indexes. If you drop an index, all applications continue to work. However, access of previously indexed data can be slower. Indexes, as independent structures, require storage space. Oracle automatically maintains and uses indexes after they are created. Oracle automatically reflects changes to data, such as adding new rows, updating rows, or deleting rows, in all relevant indexes with no additional action by users. Retrieval performance of indexed data remains almost constant, even as new rows are inserted. However, the presence of many indexes on a table decreases the
performance of updates, deletes, and inserts, because Oracle must also update the indexes associated with the table. The optimizer can use an existing index to build another index. This results in a much faster index build.