Subject: ORA-28669 Attempting to Create Bitmap Index on an IOT
Doc ID: Note:143185.1 Type: PROBLEM
Last Revision Date: 24-JUL-2002 Status: PUBLISHED


Problem Description
-------------------

You successfully create an index-organized table (IOT) and secondary bitmap
indexes, but receive the following error when you attempt to create the bitmap
index:

SQL> CREATE TABLE countries
( country_id CHAR(2),
country_name VARCHAR2(40),
currency_name VARCHAR2(25),
currency_symbol VARCHAR2(3),
region VARCHAR2(15),
CONSTRAINT country_c_id_pk PRIMARY KEY (country_id))
ORGANIZATION INDEX;

Table created.

SQL> create bitmap index i_b_region on countries (region);
create bitmap index i_b_region on countries (region)
*
ERROR at line 1:
ORA-28669: bitmap index can not be created on an IOT with no mapping table


Solution Description
--------------------

Add a mapping table to the IOT before you create the bitmap index:

SQL> ALTER TABLE countries MOVE MAPPING TABLE TABLESPACE ts_data1;
Table altered.

SQL> create bitmap index i_b_region on countries (region);
Index created.


SQL> select table_name,iot_type,iot_name from dba_tables
where (table_name='COUNTRIES' or iot_name='COUNTRIES');

TABLE_NAME IOT_TYPE IOT_NAME
------------------------------ ------------ ------------------------------
COUNTRIES IOT
SYS_IOT_MAP_34382 IOT_MAPPING COUNTRIES



If you mistakenly use the incorrect "alter table add mapping table"
syntax, you will receive the following error:

SQL> alter table countries add mapping table tablespace ts_data1;
alter table countries add mapping table tablespace ts_data1
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option


Explanation
-----------

A bitmap index contains a series of bitmaps, each representing a potential
range of row locations in the underlying table for the given key value. The
bitmap can locate a row because it assumes that data blocks are contiguous
within an extent.

In an IOT, the blocks in an extent can be either branch blocks or leaf blocks,
hence there is not a correspondence between the location of an entry and its
contents based on the block offset from the start of the extent.

A mapping table provides the translation from a bit position in a secondary
bitmap index to a location in an IOT. The bitmap table contains a column of
modified ROWIDs, each pointing to a specific entry. The order of the rows in
the mapping table corresponds to the bit positions in the related bitmap index.


Search Words
------------

ORA-1735 index organized table
[@more@]