Databases Reference
In-Depth Information
Since the mapping table has been specified on the IOT, bitmap indexes can be created on the IOT.
SQL> CREATE BITMAP INDEX employees_part_1i
2 ON employees_part (department_id)
3 NOLOGGING
3 LOCAL;
Index created.
If no mapping table is specified on the IOT, you will receive the following error when attempting to
create the bitmap index:
ON employees_part (department_id)
*
ERROR at line 2:
ORA-28669: bitmap index can not be created on an IOT with no mapping table
If you are trying to create a bitmap index on an existing IOT with no mapping, simple alter the table.
For example,
SQL> alter table employees_part move mapping table;
Table altered.
■
Note
You can't use bitmap indexes in conjunction with reverse key indexes.
Performance Implications Querying with Bitmap Indexes
Bitmap indexes are primarily built to aid in performance, especially in the data warehouse environment.
The key performance benefits of using bitmap indexes include the following:
•
Query speed improves with the use of a bitmap index over the traditional B-tree
index.
•
Creation speed of a bitmap index is far faster than that of a comparable B-tree
index.
Maintenance operations such as rebuilding indexes are much faster with a bitmap
index.
One of the key reasons for some of these benefits is simply because the bitmap index is so much
smaller
than its B-tree equivalent. Of course this assumes you have created the bitmap index on lower
cardinality columns. All these benefits being said, the primary reason to build a bitmap index is to get
superior query performance. See the following example of a query against your table using two
scenarios; the first is a query against a B-tree indexed column and the second is a query against the same
column indexed with a bitmap index.
•
