Database Reference
In-Depth Information
reads on the table is close to the number of rows. This is reasonable because adjacent rowids in the index almost
never refer to the same block:
SQL> set arraysize
2
SQL> SELECT /*+ index(t t_pk) */ * FROM t;
------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1000 | 1499 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1000 |
1499
|
| 2 | INDEX FULL SCAN | T_PK | 1 | 1000 |
502
|
------------------------------------------------------------------------
SQL> set arraysize
100
------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1000 | 1003 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1000 |
1003
|
| 2 | INDEX FULL SCAN | T_PK | 1 | 1000 |
13
|
------------------------------------------------------------------------
In summary, row prefetching is less effective with a high clustering factor, and therefore, a higher number of
logical reads is performed. The impact of the clustering factor on the resource consumption is so high that the query
optimizer (as described in Chapter 9, specifically, Formula 9-4) does use the clustering factor to compute the cost
related to index accesses, which is very often the main factor in the calculation.
B-tree Indexes vs. Bitmap Indexes
Simply put, there are some situations where only B-tree indexes can be considered. If you aren't in one of those
situations, bitmap indexes should be taken into consideration most of the time. Table
13-2
summarizes the features
that you need to take into account when deciding between B-tree and bitmap indexes.
Table 13-2.
Essential Features Supported only by B-tree and Bitmap Indexes
Feature
B-tree
Bitmap
Primary and unique key
✓
Row-level locking
✓
Efficient combination of several indexes
✓
Global indexes and non-partitioned
indexes on partitioned tables
✓
Note
■
Bitmap indexes are available only in enterprise edition.
