Oracle Concepts - Special Non-Tree Indexes Oracle Tips by Burleson Consulting

Special Non-Tree Indexes

Oracle includes many new indexing algorithms that dramatically increase the speed with which Oracle queries are serviced. This section explores the internals of Oracle indexing; reviews the standard b-tree index, bitmap indexes, function-based indexes, and index-only tables (IOTs); and demonstrates how these indexes may dramatically increase the speed of Oracle SQL queries.

Oracle uses indexes to avoid the need for large-table, full-table scans and disk sorts, which are required when the SQL optimizer cannot find an efficient way to service the SQL query. I begin our look at Oracle indexing with a review of standard Oracle b-tree index methodologies.

While b-tree indexes are great for simple queries, they are not very good for the following situations:

* Low-cardinality columns—columns with less than 200 distinct values do not have the selectivity required in order to benefit from standard b-tree index structures.

* No support for SQL functions—B-tree indexes are not able to support SQL queries using Oracle's built-in functions.

Oracle provides a variety of built-in functions that allow SQL statements to query on a piece of an indexed column or on any one of a number of transformations against the indexed column.

Prior to Oracle9i, the Oracle SQL optimizer had to perform time-consuming long-table, full-table scans due to these shortcomings. Consequently, it was no surprise when Oracle introduced more robust types of indexing structures.

This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master).  It’s only $19.95 when you buy it directly from the publisher here.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.