Database Reference
In-Depth Information
Matching WHERE Clauses to Indexes
. If your query's WHERE
clause contains only the second column in an index, Oracle Database
10
g
may not use the index for your query because you don't have the
first column in the index included in the WHERE clause. Consider
the columns used in the WHERE clauses whenever adding more
indexes to a table.
Skip Scanning Indexes
. A new feature introduced in Oracle Database
9
i
called Index Skip Scanning may help the Optimizer use indexes,
even for queries not having the first indexed column in the WHERE
clause. In other words, Index Skip Scanning is employed by the Opti-
mizer to search within composite indexes, without having to refer to
the first column in the index, commonly called the
index prefix
.
Bitmap Indexes and the WHERE Clause
. Using bitmap indexes
allows optimized SQL statement parsing and execution, without hav-
ing to match WHERE clause order against composite index orders.
In other words, multiple bitmap indexes can be used in a WHERE
clause. However, bitmap indexes can only be used for equality com-
parisons (e.g., COUNTRY='USA'). The Optimizer will not use a bit-
map index if the WHERE clause has range comparisons (e.g.,
COUNTRY LIKE 'U%') on the indexed columns.
Refer to the Oracle documentation for more details on how the Opti-
mizer evaluates the WHERE clause for index usage.
2
The next section delves briefly into using clusters.
21.2
Clusters
A cluster is somewhat like an IOT and somewhere between an index and a
table. A cluster, a little like a bitmap join index, can also join multiple tables
to get prejoined indexes.
21.2.1
What is a Cluster?
A cluster is literally a clustering or persistent “joining together” of data from
one or more sources. These multiple sources are tables and indexes. A clus-
ter places data and index space rows together into the same object. Obvi-
ously, clusters can be arranged such that they are very fast performers for
read-only data. Any type of DML activity on a cluster will overflow. Rows
