Oracle的索引组织表也支持BITMAP索引类型,不过需要映射表的支持。
描述分区索引组织表的映射表的结构。
如果索引组织表本身是分区的,那么对应的映射表采用等同分区策略:
SQL> CREATE TABLE T_INDEX_ORG
2 (ID NUMBER PRIMARY KEY,
3 NAME VARCHAR2(30),
4 TYPE VARCHAR2(30))
5 ORGANIZATION INDEX
6 MAPPING TABLE
7 PARTITION BY RANGE (ID)
8 (PARTITION P1 VALUES LESS THAN (10000),
9 PARTITION P2 VALUES LESS THAN (20000),
10 PARTITION P3 VALUES LESS THAN (MAXVALUE));
Table created.
SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, PARTITION_COUNT
2 FROM USER_PART_TABLES;
TABLE_NAME PARTITI PARTITION_COUNT
------------------------------ ------- ---------------
SYS_IOT_MAP_149936 RANGE 3
T_INDEX_ORG RANGE 3
SQL> SELECT TABLE_NAME, PARTITION_NAME
2 FROM USER_TAB_PARTITIONS;
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
SYS_IOT_MAP_149936 P1
SYS_IOT_MAP_149936 P2
SYS_IOT_MAP_149936 P3
T_INDEX_ORG P1
T_INDEX_ORG P2
T_INDEX_ORG P3
6 rows selected.
在建立LOCAL的BITMAP索引后,索引和表保持等同分区:
SQL> CREATE BITMAP INDEX IND_B_ORG_TYPE
2 ON T_INDEX_ORG (TYPE)
3 LOCAL;
Index created.
SQL> SELECT INDEX_NAME, PARTITION_NAME
2 FROM USER_IND_PARTITIONS
3 ORDER BY 1, 2;
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
IND_B_ORG_TYPE P1
IND_B_ORG_TYPE P2
IND_B_ORG_TYPE P3
SYS_IOT_TOP_149936 P1
SYS_IOT_TOP_149936 P2
SYS_IOT_TOP_149936 P3
6 rows selected.
和不分区索引组织表相比,分区的索引组织表的映射表并没有什么不同,只不过从原本是一个BITMAP索引对应一个映射表再对应到一个索引组织表,而分区后,变为一个BITMAP索引分区对应一个映射表分区再对应到一个索引组织表的分区上。