Index Types

Indexes can be categorized in a number of ways. The primary ways are:

Normal Index

A standard, B-tree index contains an entry for each value in the index key along with an address to the row where the value is stored. A B-tree index is the default and most common type of index in an Oracle database.
Text Index

An index is used by Oracle Text for text searching, such as full-text retrieval over documents and Web pages.
Single-Column and Concatenated Indexes

You can create an index on one column, which is called a single-column index, or on multiple columns, which is called a concatenated index. Concatenated indexes are useful when all of the columns are likely to be included in the WHERE clause of frequently executed SQL statements.

For concatenated indexes, define the columns used in the index carefully so that the column with the fewest duplicate values is named first, the column with the next fewest duplicate values is second, and so on. Columns with many duplicate values or many rows with null values should not be included or should be the last named columns in the index definition.
Ascending and Descending Indexes

The default search through an index is from the lowest to highest value where character data is sorted by ASCII values, numeric data is sorted from smallest to largest number, and date data is sorted from the earliest to the latest value. This default behavior is performed by indexes created as ascending indexes. You can reverse the search order of an index by creating the related index with the descending option.
Column and Function-Based Indexes

Typically, an index entry is based on the values found in the columns of the table. This is a column index. Alternatively, you can create a function-based index in which the indexed value is derived from the table data. For example, to find character data that can be in mixed case, you could use a function-based index to search for the values as if they were all in uppercase characters.