Databases Reference
In-Depth Information
We have talked about a performance gain obtained when we use reverse key indexes
because they reduce contention on the index database blocks.
There is also another feature we can appreciate, related to the way the indexes grow.
When we add more rows in a table, the related index is updated. The index entries are
stored in an ordered fashion in the B-tree index structure. So there will be a situation in
which we have a full leaf block and we have to insert a new entry into that leaf.
When this occurs, the leaf block is split into two blocks, each one containing about 50
percent of the original block. The newly created block has to be added to the parent
branch block, which in turn can be full, and the splitting process is iterated again.
When a row is deleted—or the index key field values are updated—the corresponding index
entry is logically deleted, and the space will be reused only if we insert a new index entry
with a value, which has to be inserted in that particular block, due to the sort constraint.
In situations similar to the one described in this recipe—a field populated with a growing
value—when we delete an index entry, the space won't be claimed, ever, because the new
values will always be greater than the old value. And if the block is full, new entries will
always be in a new block.
Using reverse key indexes also solves this issue. For example, when the key 123—stored
as 321—is deleted, the space could be reused by a key 723—stored as 327—so the empty
space in index database blocks will be refilled when we insert new rows in the table.
Using bitmap indexes
In the last recipe, we looked at the use of B-tree indexes in depth.
In the Oracle database, there is also another type of index available, the bitmap index,
presented in this recipe.
How to do it...
The following steps will demonstrate bitmap indexes:
1.
Connect to SQL*Plus as user SH:
CONNECT sh@TESTDB/sh
2.
Create a table to do some tests:
CREATE TABLE MYCUSTOMERS AS SELECT * FROM CUSTOMERS;
