Problem solve Get help with specific problems with your technologies, process and projects.

Explanation of the different types of indexes

Can you describe the differences between the types of Oracle indexing and when I should do which? (i.e. B-tree, function-based, bitmap, and so on...)

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.

Dig Deeper on Oracle database performance problems and tuning