Bitmap Indexes on IOT.

Learnt newly from oracle docs..Its about Bitmap Indexes on IOT. Bitmap indexes cannot be created on IOT tables unless you have a mapping table defined.

rajesh@10GR2> create table t(
  2     owner,
  3     object_name,
  4     object_type,
  5     object_id,
  6     created,
  7     deptno,
  8     constraint t_pk primary key(object_id)
  9  )organization index
 10  nologging
 11  as
 12  select owner,object_name,object_type,object_id,created,mod(rownum,10)
 13  from all_objects;

Table created.

Elapsed: 00:00:07.79
rajesh@10GR2>
rajesh@10GR2> create bitmap index t_bitmap on t(deptno);
create bitmap index t_bitmap on t(deptno)
                                *
ERROR at line 1:
ORA-28669: bitmap index can not be created on an IOT with no mapping table

Elapsed: 00:00:00.01

This error clearly specifies that Bitmap indexes cannot be created on non mapping IOT segments with no
mapping table.The solution is to design the Mapping table with IOT segments. Specify MAPPING TABLE to instruct the database to create a mapping of local to physical ROWIDs and store them in a heap-organized table. This mapping is needed in order to create a bitmap index on the index-organized table.

rajesh@10GR2> create table t(
  2             owner,
  3             object_name,
  4             object_type,
  5             object_id,
  6             created,
  7             deptno,
  8             constraint t_pk primary key(object_id)
  9  )organization index mapping table
 10  nologging
 11  as
 12  select owner,object_name,object_type,object_id,created,mod(rownum,10)
 13  from all_objects;

Table created.

Elapsed: 00:00:09.03
rajesh@10GR2>
rajesh@10GR2> create bitmap index t_bitmap on t(deptno);

Index created.

Elapsed: 00:00:00.45
rajesh@10GR2>
rajesh@10GR2> SELECT table_name,iot_type, uo.object_id
  2  FROM user_tables ut, user_objects uo
  3  where iot_type like 'IOT%'
  4  and uo.object_name = ut.table_name
  5  /

TABLE_NAME                     IOT_TYPE      OBJECT_ID
------------------------------ ------------ ----------
SYS_IOT_MAP_151777             IOT_MAPPING      151778
T                              IOT              151777

Elapsed: 00:00:00.43
rajesh@10GR2>

This mapping table ( SYS_IOT_MAP_151777 ) is nothing but a Heap organized table having mapping of local to physical rowids.

rajesh@10GR2> select *
  2  from sys_iot_map_151777
  3  where rownum <=5;

SYS_NC_01
-----------------------------------------------------------
*BAHABJMDwjIP/g
*BAHABJMDwjIR/g
*BAHABJMDwjIS/g
*BAHABJMDwjIT/g
*BAHABJMDwjIU/g

Elapsed: 00:00:00.09
rajesh@10GR2>

If the index-organized table is partitioned, then the mapping table is also partitioned and its partitions have the same name and physical attributes as the base table partitions.

rajesh@10GR2> create table t(
  2             owner,
  3             object_name,
  4             object_type,
  5             object_id,
  6             created,
  7             deptno,
  8             constraint t_pk primary key(object_id)
  9  )organization index mapping table
 10  nologging
 11  partition by range(object_id)
 12  (
 13     partition p_1 values less than(10000),
 14     partition p_2 values less than(20000),
 15     partition p_3 values less than(30000),
 16     partition p_4 values less than(40000),
 17     partition p_5 values less than(50000),
 18     partition p_max values less than (maxvalue)
 19  )
 20  as
 21  select owner,object_name,object_type,object_id,created,mod(rownum,10)
 22  from all_objects;

Table created.

Elapsed: 00:00:10.28
rajesh@10GR2> SELECT table_name, ut.partition_name,uo.object_id
  2  FROM user_tab_partitions ut, user_objects uo
  3  where uo.object_name = ut.table_name
  4  and  uo.subobject_name = ut.partition_name
  5  /

TABLE_NAME                     PARTITION_NAME                  OBJECT_ID
------------------------------ ------------------------------ ----------
SYS_IOT_MAP_151805             P_MAX                              151818
SYS_IOT_MAP_151805             P_5                                151817
SYS_IOT_MAP_151805             P_4                                151816
SYS_IOT_MAP_151805             P_3                                151815
SYS_IOT_MAP_151805             P_2                                151814
SYS_IOT_MAP_151805             P_1                                151813
T                              P_MAX                              151811
T                              P_5                                151810
T                              P_4                                151809
T                              P_3                                151808
T                              P_2                                151807
T                              P_1                                151806

12 rows selected.

Elapsed: 00:00:00.10
rajesh@10GR2>
rajesh@10GR2>

So how converting a IOT segments having non-mapping table to a mapping table in real world Applications? Now that can be done by re-organizing the table online using dbms_redefinition API.

rajesh@10GR2>
rajesh@10GR2> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "RAJESH"."T"
   (    "OWNER" VARCHAR2(30) NOT NULL ENABLE,
        "OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
        "OBJECT_TYPE" VARCHAR2(19),
        "OBJECT_ID" NUMBER NOT NULL ENABLE,
        "CREATED" DATE NOT NULL ENABLE,
        "DEPTNO" NUMBER,
         CONSTRAINT "T_PK" PRIMARY KEY ("OBJECT_ID") ENABLE
   ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TOOLS"
 PCTTHRESHOLD 50

Elapsed: 00:00:08.95
rajesh@10GR2> create table t_part
  2  (
  3     OWNER                   VARCHAR2(30),
  4     OBJECT_NAME     VARCHAR2(30),
  5     OBJECT_TYPE     VARCHAR2(19),
  6     OBJECT_ID               NUMBER,
  7     CREATED         DATE,
  8     DEPTNO          NUMBER,
  9     constraint t_part_pk primary key(object_id)
 10  )
 11  organization index mapping table;

Table created.

Elapsed: 00:00:00.34
rajesh@10GR2>
rajesh@10GR2> begin
  2     dbms_redefinition.can_redef_table(
  3             uname=>user,
  4             tname=>'T'
  5     );
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
rajesh@10GR2>
rajesh@10GR2> begin
  2     dbms_redefinition.start_redef_table(
  3             uname=>user,
  4             orig_table=>'T',
  5             int_table=>'T_PART');
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.89
rajesh@10GR2>
rajesh@10GR2> variable x number;
rajesh@10GR2>
rajesh@10GR2> begin
  2     dbms_redefinition.copy_table_dependents(
  3             uname=>user,
  4             orig_table=>'T',
  5             int_table=>'T_PART',
  6             num_errors=>:x);
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.78
rajesh@10GR2> begin
  2     dbms_redefinition.finish_redef_table(
  3             uname=>user,
  4             orig_table=>'T',
  5             int_table=>'T_PART');
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.84
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "RAJESH"."T"
   (    "OWNER" VARCHAR2(30),
        "OBJECT_NAME" VARCHAR2(30),
        "OBJECT_TYPE" VARCHAR2(19),
        "OBJECT_ID" NUMBER,
        "CREATED" DATE,
        "DEPTNO" NUMBER,
         CONSTRAINT "T_PART_PK" PRIMARY KEY ("OBJECT_ID") ENABLE
   ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TOOLS"
 PCTTHRESHOLD 50 MAPPING TABLE



Elapsed: 00:00:02.98
rajesh@10GR2>