Databases Reference
In-Depth Information
CHAPTER 5
■ ■ ■
Specialized Indexes
Thus far you've learned how to create normal Oracle indexes, which include both B-tree indexes as well
as bitmap indexes. You also learned how to create and manage index organized tables, This chapter
explains several specialized indexes that you can create for serving various needs. Most of these
specialized indexes are actually B-tree indexes, so it's not the organization of the index itself that makes
them special. For example, an invisible index is an index that's not automatically available to the cost-
based optimizer, unless you make it available by setting a specific initialization parameter. You use
invisible indexes mainly for testing the use of an index and to make sure an index is redundant before
you drop it.
The chapter explains how to use function-based indexes when your application applies functions to
a column value. Besides using Oracle-provided functions, you can create custom functions and create
indexes based on those functions. Function-based indexes let you create indexes based on arithmetical
expressions. You will also learn how to use the
CASE
construct to generate indexes based only on some
rows in a table. Reverse key indexes are a great solution in a high-volume Oracle RAC environment. This
chapter explains the rationale behind the use of reverse key indexes and discusses alternatives to these
indexes when dealing with index contention in an Oracle RAC environment. The chapter also introduces
application domain indexes, which help you create your own index structures to implement index types
that Oracle doesn't currently provide.
Invisible Indexes
When you create any index, by default, the optimizer is aware of the index and it starts taking account of
the index in all its cost estimations. However, there are occasions when you may want to create an index
but control when the optimizer can use the index. You can hide an index from the optimizer by creating
an index as an "invisible" index. You can also alter the status of an existing index by making it invisible.
You can toggle the status of the index between visible and invisible as you please.
Note
Since the database must continue to maintain a visible index when inserts, deletes, and updates occur, it
is important that you're fully aware of any invisible indexes.
