Database Reference
In-Depth Information
SQL> SELECT blocks FROM index_stats;
BLOCKS
----------
32
From a performance point of view, the key advantage of compressed indexes is that because of their smaller size,
not only are fewer logical reads needed to perform index range scans and index full scans, but, in addition, their
blocks are more likely to be found in the buffer cache. the disadvantage, however, is the increasing likelihood of
suffering from block contention (this topic is covered in Chapter 16).
Bitmap Indexes
Composite bitmap indexes are rarely created. This is because several indexes can be combined efficiently in order to
apply a restriction. To see how powerful bitmap indexes are, let's look at several queries.
The first query takes advantage of three bitmap indexes that are combined with AND in order to apply three
equality conditions. Note that the index_combine hint forces this type of execution plan. First, operation 4 scans the
index based on the n5 column by looking for the rows that fulfill the restriction on that column. The resulting bitmaps
are passed to operation 3. Then operations 5 and 6 perform the same scan on the indexes created on the n6 and n4
columns, respectively. Once the three index scans are completed, operation 3 computes the AND of the three sets of
bitmaps. Finally, operation 2 converts the resulting bitmap into a list of rowids, and then they're used by operation 1
to access the table:
SELECT /*+ index_combine (t i_n4 i_n5 i_n6) */ *
FROM t
WHERE n4 = 6 AND n5 = 42 AND n6 = 11
-------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 7 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 1 | 7 |
| 2 | BITMAP CONVERSION TO ROWIDS| | 1 | 1 | 6 |
| 3 | BITMAP AND | | 1 | 1 | 6 |
|* 4 | BITMAP INDEX SINGLE VALUE| I_N5 | 1 | 1 | 2 |
|* 5 | BITMAP INDEX SINGLE VALUE| I_N6 | 1 | 1 | 2 |
|* 6 | BITMAP INDEX SINGLE VALUE| I_N4 | 1 | 1 | 2 |
-------------------------------------------------------------------------
4 - access("N5"=42)
5 - access("N6"=11)
6 - access("N4"=6)
 
Search WWH ::




Custom Search