Databases Reference
In-Depth Information
Bitmap join indexes are appropriate in situations in which you're joining two tables, using the foreign key
column (or columns) in one table relating to the primary key column (or columns) in the other table. For example,
suppose you typically retrieve the
FIRST_NAME
and
LAST_NAME
from the
CUST
dimension table while joining to a large
F_SHIPMENTS
fact table. This next example creates a bitmap join index between the
F_SHIPMENTS
and
CUST
tables:
create bitmap index f_shipments_bmx1
on f_shipments(cust.first_name, cust.last_name)
from f_shipments, cust
where f_shipments.cust_id = cust.cust_id;
Now, consider a query such as this:
select c.first_name, c.last_name
from f_shipments s, cust c
where s.cust_id = c.cust_id
and c.first_name = 'JIM'
and c.last_name = 'STARK';
The optimizer can choose to use the bitmap join index, thus avoiding the expense of having to join the tables.
For small amounts of data, the optimizer will most likely choose not to use the bitmap join index, but as the data in
the table grow, using the bitmap join index becomes more cost-effective than full-table scans or using other indexes.
Implementing Reverse-Key Indexes
Reverse-key indexes are similar to B-tree indexes, except that the bytes of the index key are reversed when an index entry
is created. For example, if the index values are 201, 202, and 203, the reverse-key index values are 102, 202, and 302:
Index value Reverse key value
------------- --------------------
201 102
202 202
203 302
Reverse-key indexes can perform better in scenarios in which you need a way to evenly distribute index data that
would otherwise have similar values clustered together. Thus, when using a reverse-key index, you avoid having I/O
concentrated in one physical disk location within the index during large inserts of sequential values.
Use the
REVERSE
clause to create a reverse-key index:
SQL> create index cust_idx1 on cust(cust_id) reverse;
You can verify that an index is reverse key by running the following query:
SQL> select index_name, index_type from user_indexes;
Here is some sample output, showing that the
CUST_IDX1
index is reverse key:
INDEX_NAME INDEX_TYPE
-------------------- ---------------------------
CUST_IDX1 NORMAL/REV
