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.