Database Reference
In-Depth Information
For this first query, it's worthwhile to also show you the execution plan with a composite bitmap index. As you can
see, the number of logical reads isn't much lower (4 instead of 7). It's better, but such a composite index is far less flexible
that the three single-column indexes. This is the reason why in practice composite bitmap indexes are rarely created:
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 1 | 4 |
| 2 | BITMAP CONVERSION TO ROWIDS| | 1 | 1 | 3 |
|* 3 | BITMAP INDEX SINGLE VALUE | I_N456 | 1 | 1 | 3 |
---------------------------------------------------------------------------
3 - access("N4"=6 AND "N5"=42 AND "N6"=11)
The second query is very similar to the first one. The only difference is because of the
OR
instead of the
AND
. Notice
how only operation 3 has changed in the execution plan:
SELECT /*+ index_combine(t i_n4 i_n5 i_n6) */ *
FROM t
WHERE n4 = 6
OR
n5 = 42
OR
n6 = 11
-------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 767 | 420 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 767 | 420 |
| 2 | BITMAP CONVERSION TO ROWIDS| | 1 | 767 | 7 |
| 3 |
BITMAP OR
| | 1 | 1 | 7 |
|* 4 | BITMAP INDEX SINGLE VALUE| I_N4 | 1 | 1 | 3 |
|* 5 | BITMAP INDEX SINGLE VALUE| I_N6 | 1 | 1 | 2 |
|* 6 | BITMAP INDEX SINGLE VALUE| I_N5 | 1 | 1 | 2 |
-------------------------------------------------------------------------
4 - access("N4"=6)
5 - access("N6"=11)
6 - access("N5"=42)
The third query is similar to the first one. This time, the only difference is the
n4 != 6
condition (instead of
n4 = 6
). Because the execution plan is quite different, let's look at it in detail. Initially, operation 6 scans the index
based on the
n5
column by looking for the rows fulfilling the
n5 = 42
condition on that column. The resulting bitmaps
are passed to operation 5. Then, operation 7 performs the same scan on the index created on the
n6
column for the
n6 = 11
condition. Once the two index scans are completed, operation 5 computes the
AND
of the two sets of bitmaps
and passes the resulting bitmaps to operation 4. Next, operation 8 scans the index based on the
n4
column by looking
for rows fulfilling the
n4 = 6
condition (which is the opposite of what is specified in the
WHERE
clause). The resulting
bitmaps are passed to operation 4, which subtracts them from the bitmaps delivered by operation 5. Then, operations
9 and 3 perform the same scan for the
n4 IS NULL
condition. This is necessary because
NULL
values don't fulfill the
