Database Reference
In-Depth Information
n4 != 6
condition. Finally, operation 2 converts the resulting bitmap into a list of rowids, which are then 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 | 9 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 1 | 9 |
| 2 | BITMAP CONVERSION TO ROWIDS | | 1 | 1 | 8 |
| 3 |
BITMAP MINUS
| | 1 | 1 | 8 |
| 4 |
BITMAP MINUS
| | 1 | 1 | 6 |
| 5 | BITMAP AND | | 1 | 1 | 4 |
|* 6 | BITMAP INDEX SINGLE VALUE| I_N5 | 1 | 1 | 2 |
|* 7 | BITMAP INDEX SINGLE VALUE| I_N6 | 1 | 1 | 2 |
|* 8 | BITMAP INDEX SINGLE VALUE | I_N4 | 1 | 1 | 2 |
|* 9 | BITMAP INDEX SINGLE VALUE | I_N4 | 1 | 1 | 2 |
---------------------------------------------------------------------------
6 - access("N5"=42)
7 - access("N6"=11)
8 - access("N4"=6)
9 - access("N4" IS NULL)
In summary, bitmap indexes can be combined efficiently and have several SQL conditions applied during
combinations. In a few words, they're very flexible. Because of these characteristics, they're essential for reporting
systems where the queries aren't known (fixed) in advance.
Bitmap Plans for B-tree Indexes
The bitmap plans described in the previous section perform so well, they can also be applied to B-tree indexes.
The idea is that the database engine is able to build a kind of in-memory bitmap index based on the data returned
by B-tree index scans. The following query, which is the same as the one used in the part about composite B-tree
indexes, is an example. Note that the
BITMAP CONVERSION FROM ROWIDS
operations are responsible for the conversion
in the execution plan:
SELECT /*+
index_combine
(t i_n1 i_n2 i_n3) */ *
FROM t
WHERE n1 = 6 AND n2 = 42 AND n3 = 11
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 10 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 1 | 10 |
| 2 | BITMAP CONVERSION TO ROWIDS | | 1 | 1 | 9 |
| 3 | BITMAP AND | | 1 | 1 | 9 |
