Database Reference
In-Depth Information
This single index could be used by either of the queries. Additionally, using index key compression (which we
looked at with regard to IOTs and will examine further later), we can build a smaller index if
C2
is first. This is because
each value of
C2
repeats itself, on average, four times in the index. If
C1
and
C2
are both, on average, 10 bytes in length,
the index entries for this index would nominally be 2,000,000 bytes (100,000 × 20). Using index key compression on
(C2, C1)
, we could shrink this index to 1,250,000 (100,000 × 12.5), since three out of four repetitions of
C2
could be
suppressed.
In Oracle 5 (yes, version 5), there was an argument for placing the most selective columns first in an index. It had
to do with the way version 5 implemented index compression (not the same as index key compression). This feature
was removed in version 6 with the addition of row-level locking. Since then, it is not true that putting the most
discriminating entries first in the index will make the index smaller or more efficient. It seems like it will, but it will
not. With index key compression, there is a compelling argument to go the other way since it can make the index
smaller. However, it should be driven by
how
you use the index, as previously stated.
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 usinWg 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 12
c
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 closed with 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 example, demonstrating the concepts as we went along.
