Database Reference
In-Depth Information
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"<=30000 AND "X">=20000)
Here we have the same table structures—the same indexes—but different clustering factors. The optimizer in this
case chose an index access plan for the
COLOCATED
table and a full scan access plan for the
DISORGANIZED
table.
The key point to this discussion is that indexes are not always the appropriate access method. The optimizer may
very well be correct in choosing to not use an index, as the preceding example demonstrates. Many factors influence the
use of an index by the optimizer,
including
physical data layout. You might be tempted therefore to run out and try to
rebuild all of your tables now to make all indexes have a good clustering factor, but that would
be a waste of time
in most
cases. It will affect cases where you do index range scans of a large percentage of a table. Additionally, you must keep in
mind that, in general, the table will have only
one
index with a good clustering factor! The rows in a table may be sorted
in only one way. In the example just shown, if I had another index on the column
Y
it would be very poorly clustered
in the
COLOCATED
table, but very nicely clustered in the
DISORGANIZED
table. If having the data physically clustered is
important to you, consider the use of an IOT, a B*Tree cluster, or a hash cluster over continuous table rebuilds.
B*Trees Wrap-up
B*Tree indexes are by far the most common and well-understood indexing structures in the Oracle database. They
are an excellent general-purpose indexing mechanism. They provide very scalable access times, returning data from a
1,000-row index in about the same amount of time as a 100,000-row index structure.
When to index and what columns to index are things you need to pay attention to in your design. An index does
not always mean faster access; in fact, you will find that indexes will decrease performance in many cases if Oracle
uses them. It is purely a function of how large of a percentage of the table you will need to access via the index and
how the data happens to be laid out. If you can use the index to answer the question, accessing a large percentage of
the rows makes sense, since you are avoiding the extra scattered I/O to read the table. If you use the index to access
the table, you will need to ensure you are processing a small percentage of the total table.
You should consider the design and implementation of indexes
during
the design of your application, not as an
afterthought (as I so often see). With careful planning and due consideration of how you are going to access the data,
the indexes you need will be apparent in most all cases.
Bitmap Indexes
Bitmap indexes were added to Oracle in version 7.3 of the database. They are currently available with the Oracle
Enterprise and Personal Editions, but not the Standard Edition. Bitmap indexes are designed for data warehousing/
ad hoc query environments where the full set of queries that may be asked of the data is not totally known at system
implementation time. They are specifically
not
designed for OLTP systems or systems where data is frequently
updated by many concurrent sessions.
Bitmap indexes are structures that store pointers to many rows with a single index key entry, as compared to a
B*Tree structure where there is parity between the index keys and the rows in a table. In a bitmap index, there will be
a very small number of index entries, each of which points to many rows. In a conventional B*Tree, one index entry
points to a single row.
Let's say we are creating a bitmap index on the
JOB
column in the
EMP
table as follows:
EODA@ORA12CR1> create BITMAP index job_idx on emp(job);
Index created.
Oracle will store something like what is shown in Table
11-6
in the index.
