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
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.