Auto Indexing Wrap-Up-Indexes

Does this mean the DBAs and application developers don’t need to worry about indexing anymore? Far from it. Even with automatic indexing, you still need to monitor the system and ensure that the indexing is doing what you think it should do. You’ll haveinsights into your database and application code that the auto indexing feature may not be aware of. That said, this feature should be used to augment your indexing strategy, not entirely replace it.

Summary

In this chapter, we covered the different types of indexes Oracle has to offer. We started with the basic B*Tree index and looked at various subtypes of this index, such as the reverse key index (designed for Oracle RAC) and descending indexes for retrieving data sorted in a mix of descending and ascending order. We spent some time looking at when you should use an index and why an index may not be useful in various circumstances.

We then looked at bitmap indexes, an excellent method for indexing low to medium cardinality data in a data warehouse (read-intensive, non-OLTP) environment. We covered the times it would be appropriate to use a bitmapped index and why you would never consider one for use in an OLTP environment—or any environment where multiple users must concurrently update the same column.

We moved on to cover function-based indexes, which are actually special cases of B*Tree and bitmapped indexes. A function-based index allows us to create an index on a function of a column (or columns), which means that we can precompute and store the results of complex calculations and user-written functions for blazingly fast index retrieval later.

We looked at some important implementation details surrounding function-­based indexes, such as the necessary system- and session-level settings that must be in place for them to be used. We followed that with examples of function-based indexes using both built-in Oracle functions and user-written ones. Lastly, we looked at a few caveats with regard to function-based indexes.

We then examined a very specialized index type called the application domain index. Rather than go into how to build one of those from scratch (which involves a long, complex sequence of events), we looked at an example that had already been implemented: the text index.

We then discussed a couple of 12c topics: indexing extended columns and multiple indexes on the same column combinations. With indexing extended columns, this requires either using a virtual column and associated index or a function-based index. When indexing the same column combinations, you must use different physical index types, and only one index can be designated as visible.

We also addressed some of the most frequently asked questions on indexes as well as some myths about indexes. This section covered topics ranging from the simple question “Do indexes work with views?” to the more complex and subtle myth “Space is never reused in an index.” We answered these questions and debunked the myths mostly through examples, demonstrating the concepts as we went along.

Lastly, we covered a new feature, automatic indexing. This allows you to delegate many of the index management functions to the database. When enabled, Oracle will automatically identify new indexes that can improve performance.

This feature will also identify indexes that need to be rebuilt and unused indexes that should be removed. The DBA still needs to manage and monitor this activity and ensure that automatic indexing is properly functioning.