Q
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.


This was last published in July 2004

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close