Indexes - Expert Oracle Database Architecture - page 466

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)...
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 408 | 13872 | 68 (0)...
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 408 | 13872 | 68 (0)...
| 2 | BITMAP CONVERSION TO ROWIDS | | | | ...
| 3 | BITMAP AND | | | | ...
|* 4 | BITMAP INDEX SINGLE VALUE | AGE_GROUP_IDX | | | ...
| 5 | BITMAP OR | | | | ...
| 6 | BITMAP AND | | | | ...
|* 7 | BITMAP INDEX SINGLE VALUE | LOCATION_IDX | | | ...
|* 8 | BITMAP INDEX SINGLE VALUE | GENDER_IDX | | | ...
| 9 | BITMAP AND | | | | ...
|* 10 | BITMAP INDEX SINGLE VALUE | LOCATION_IDX | | | ...
|* 11 | BITMAP INDEX SINGLE VALUE | GENDER_IDX | | | ...
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("AGE_GROUP"='18 and under')
7 - access("LOCATION"=22)
8 - access("GENDER"='F')
10 - access("LOCATION"=20)
11 - access("GENDER"='M')
This shows similar logic: the plan shows the OR 'd conditions are each evaluated by AND -ing together the
appropriate bitmaps and then OR -ing together those results. Throw in another AND to satisfy the AGE_GROUP='18 AND
UNDER' and we have it all. Since we asked for the actual rows this time, Oracle will convert each bitmap 1 and 0 into
rowids to retrieve the source data.
In a data warehouse or a large reporting system supporting many ad hoc SQL queries, this ability to use as
many indexes as make sense simultaneously comes in very handy indeed. Using conventional B*Tree indexes here
would not be nearly as usual or usable, and as the number of columns that are to be searched by the ad hoc queries
increases, the number of combinations of B*Tree indexes you would need increases as well.
However, there are times when bitmaps are not appropriate. They work well in a read-intensive environment, but
they are extremely ill suited for a write-intensive environment. The reason is that a single bitmap index key entry points to
many rows. If a session modifies the indexed data, then all of the rows that index entry points to are effectively locked in
most cases. Oracle cannot lock an individual bit in a bitmap index entry; it locks the entire bitmap index entry. Any other
modifications that need to update that same bitmap index entry will be locked out. This will seriously inhibit concurrency,
as each update will appear to lock potentially hundreds of rows preventing their bitmap columns from being concurrently
updated. It will not lock every row as you might think—just many of them. Bitmaps are stored in chunks, so using the
earlier EMP example we might find that the index key ANALYST appears in the index many times, each time pointing to
hundreds of rows. An update to a row that modifies the JOB column will need to get exclusive access to two of these index
key entries: the index key entry for the old value and the index key entry for the new value. The hundreds of rows these two
entries point to will be unavailable for modification by other sessions until that UPDATE commits.
Bitmap Join Indexes
In Oracle9 i an index type was added: the bitmap join index. Normally, an index is created on a single table, using only
columns from that table. A bitmap join index breaks that rule and allows you to index a given table using columns from
some other table. In effect, this allows you to denormalize data in an index structure instead of in the tables themselves.