OCP-043 bitmap indexes on index-Organized Tables

50. While designing your database, you have created the EMPLOYEES table as an indexorganized  table
(IOT). You want to create a bitmap index on the JOB_ID column to make queries faster.
Which task must have been completed so that you are able to create the bitmap index?
A.A primary key must have been created.
B.A mapping table must have been created.
C.An overflow tablespace must have been specified.
D.The PCTTHRESHOLD option must have been specified.
Answer: B

Bitmap Indexes on Index-Organized Tables

Oracle supports bitmap indexes on partitioned and nonpartitioned index-organized tables.A mapping table is required for creating bitmap indexes on an index-organized table.

Mapping Table

The mapping table is a heap-organized table that stores logical rowids of the index-organized table. Specifically, each mapping table row stores one logical rowid for the corresponding index-organized table row. Thus, the mapping table provides one-to-one mapping between logical rowids of the index-organized table rows and physical rowids of the mapping table rows.

A bitmap index on an index-organized table is similar to that on a heap-organized table except that the rowids used in the bitmap index on an index-organized table are those of the mapping table as opposed to the base table. There is one mapping table for each index-organized table and it is used by all the bitmap indexes created on that index-organized table.

In both heap-organized and index-organized base tables, a bitmap index is accessed using a search key. If the key is found, the bitmap entry is converted to a physical rowid. In the case of heap-organized tables, this physical rowid is then used to access the base table. However, in the case of index-organized tables, the physical rowid is then used to access the mapping table. The access to the mapping table yields a logical rowid. This logical rowid is used to access the index-organized table.

Though a bitmap index on an index-organized table does not store logical rowids, it is still logical in nature.

Note:

Movement of rows in an index-organized table does not leave the bitmap indexes built on that index-organized table unusable. Movement of rows in the index-organized table does invalidate the physical guess in some of the mapping table's logical rowid entries. However, the index-organized table can still be accessed using the primary key.