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! |
