If you are new to databases, or perhaps new to Oracle, you may find the discussion on indexes and indexing strategy complicated.  Don't fret.  To get started it's fairly straightforward, and as long as you pay attention to the options relevant to day-to-day dba needs, it should remain fairly simple.

Common Usage Indexes

b-tree index

The most common index type is the b-tree index.  It is named b-tree after a computer science construct of the same name.  Whenever you issue the basic CREATE INDEX statement without further modifications, you're creating a b-tree index.  Without going into a lot of depth about b-trees which you can investigate on your own, basically these store the values of the column you have created the index on, and pointers to the actual table data to find the row itself.  Keep in mind that also means multiple lookups, one for various nodes and the leaf node of the index, and then the table row itself.  That's why Oracle's optimizer will choose in certain circumstances to do full table scans rather than index lookup, because it may actually be faster.  Also note that if your index is on more than one column, the leading column is very important.  For example if you have a multi-column (called concatenated) index on columns c and d in that order, you can also do a query on column c alone and use that index. In some other cases using Oracle's skip- scan technology, one can do a query on non-leading columns as well. Do a google search on "skip scan index access" for details.

function-based index

We mentioned that Oracle would choose not to use an index sometimes, if you're reading a lot of rows, or your index is not selective, or you're using a column other than the leading one in a concatenated index.  What about if you want to do a case-insensitive search?  Something like:

WHERE UPPER(first_name) = 'JOHN'

This won't use an index on first_name.  Why?  Because Oracle would have to go and apply the UPPER function on ALL values in the index, so it might as well do the full table scan.  This was such a common need that Oracle created the function-based index for this purpose.

reverse key indexes

You also may see these indexes, or want to use them from time to time.  Consider a column, which includes names like "restaurant A", "restaurant B", "restaurant C" and so on.  Perhaps a not very glamorous example, but the point is a column with many unique values but not much variation at the front.  Using a reverse-key index would be ideal here, because Oracle will simple REVERSE the string before throwing it into the b-tree.  So, the result will be a more balanced, useful, and ultimately fast index.

More Exotic Index Types on Offer

Oracle offers quite a few more sophisticated types of indexes as well.  Please note, these should be used after you've fully read the docs as they fill very specific niches.  

bitmap indexes

Have a column, which is not very selective, such as gender?  You might consider using a bitmap index on it.  That's what they were created for.  But also consider what's happening behind the scenes.  Generally bitmap indexes become useful when you have a whole bunch of them on different columns so that they can all be used together to be more selective on rows that otherwise you'd need a full table scan for.  So one, use them when you can have quite a few on different columns.  Secondly, these indexes were designed for data warehouses, so the presumption is data that does not change much.  They are not meant for transactional or high update databases.  Updates on tables with bitmap indexes are, shall we say, less than efficient.  

bitmap join indexes

These indexes take bitmap indexes one step further.  They completely take the bitmapped columns out of the table data, and store it in the index.  The presumption is that those sets of columns will always be queried together.  Again, these are meant for data warehousing databases.  The create statement looks like a CREATE BITMAP INDEX except it has a WHERE clause at the end!

compressed indexes

This is really an option to a normal b-tree index.  It results in fewer leaf nodes, so less overall I/O and less to cache.  All of this means Oracle's optimizer will be just a little more likely to use these than a normal uncompressed index.  There is a cost to all of this, in CPU to uncompress when you access these.  Also, in reading about how the optimizer uses these, and choosing the proper compression level, it begins to look daunting.  Your mileage may vary.

descending

These are a special type of function-based index.  They are obviously optimized for ORDER BY x, y, z DESC clauses.

partitioned indexes

If you have a partitioned table, a whole world of new index types opens up to you, from ones that index across all the partitions (global) to ones that are focused on each partition individually (local).  Check the documentation for details.

index organized tables

Imagine you take your concatenated index, and extend it to all the columns in the table.  Then you can remove the table itself.  That's what an index organized table is. 

cluster indexes

I personally have never seen these in the wild.  All I've read is they have performance issues left and right.  Basically, you take two tables with one column in common, and that column has a cluster index on it.

domain indexes

These indexes are used when creating custom indextypes for user defined datatypes. 

invisible indexes

These are new in 11g.  They are created as a normal index, but invisible to the cost based optimizer.  This can allow you to test out performance of large queries, without impacting the live running application.

virtual (no segment) indexes

Another tool for the testers and developers.  They allow you to test new indexes and their effect on query plans, without actually building them.  On gigabyte tables, the index build can be very resource intensive, and take a lot of time.   See also the Virtual Index Wizard of OEM.

miscellaneous

There are other types of indexes as well, such as Oracle TEXT for indexing CLOB or other large text data, and Oracle Spatial.  Investigation of those is left as an exercise to the reader.

It's All About the Optimizer

Having worked extensively with MySQL, and some other databases, I can tell you it is not Oracle's user-friendliness that makes it the world leader.  I could go digress on this point, but primarily Oracle's bread and butter is it's optimizer.  This is the special sauce.  And it keeps getting better and better.  There are whole books written on the topic of the Oracle's CBO (Cost Based Optimizer) discussing hints (comments embedded in SQL to push the optimizer one way or another), strategies for analyzing your tables and indexes, and histograms for those finicky columns where data distribution is not balanced.  

Besides keeping your statistics up to date, you'll want to always test your new queries.  Use the explain plan mechanism, and optimize to reduce overall I/O and computational sorting and merging of data, and you will be on the path to better performance.

Conclusion

Although the Oracle landscape of index types can be intimidating, there are really only a few that you'll use most often day-to-day.  Furthermore, the optimizer has gotten so good that despite what the naysayers may go on about; on the whole Oracle is good at getting your data efficiently.  That doesn't mean you don't need to tune your SQL, but it does mean if you keep your statistics up to date, and ask Oracle for the minimal dataset you need, it'll probably get it for you lickety-split!

» See All Articles by Columnist Sean Hull