索引组织表中的MAPPING TABLE

Oracle支持在分区或非分区的索引组织表上创建位图索引,需要一个映射表(MAPPING TABLE)来存储逻辑ROWID。MAPPING TABLE是一个堆组织表,提供索引组织表行与映射表行的一对一映射。位图索引通过映射表的ROWID访问数据,而非直接使用索引组织表的物理ROWID。在索引组织表上创建位图索引时,必须先创建MAPPING TABLE。
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.

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.

大意是说:Oracle数据库支持在分区或者不分区的索引组织表(IOT)上创建位图索引。在向索引组织表创建位图索引的时候必须要有MAPPING TABLE。

MAPPING TABLE是一个HEAP表(即普通表),用来存储索引组织表的逻辑rowid。具体来说,MAPPING TABLE的每一行存储了一个逻辑rowid,用来关联索引组织表的行。因此,MAPPING TABLE的物理rowid和索引组织表的行之间产生了一对一的关联。因为索引组织表的rowid会经常变化,所以只有逻辑rowid,而不像普通表的行具有物理rowid,可以直接对应到具体的物理存放地点。在索引组织表上创建的位图索引和在普通HEAP表上创建的位图索引是类似的,只不过如果是索引组织表,那位图索引会使用MAPPING TABLE的rowid。在查询的时候,2种表都是通过key字段访问,在key字段找到之后,普通HEAP表会直接把key转换成rowid,然后通过rowid定位到具体的数据块中。但是如果是索引组织表,会通过key转换成rowid后,到MAPPING TABLE中去查找逻辑rowid,然后通过这个逻辑rowid去访问索引组织表里的数据。

SQL> create table t_testmap(eno number primary key,ename varchar2(10)) organization index;


Table created.


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

无法在没有mapping table的情况下给IOT创建位图索引。

SQL> create table t_testmap2(eno number primary key,ename varchar2(10)) organization index mapping table;


Table created.


SQL> create bitmap index idx_map on t_testmap2(ename);


Index created.

位图索引创建成功。

SQL> select table_name,iot_name,iot_type from user_tables;


TABLE_NAME IOT_NAME IOT_TYPE
------------------------------ ------------------------------ ---------------------
SYS_IOT_MAP_10258 T_TESTMAP2 IOT_MAPPING
T_TESTMAP2 IOT
T_TESTMAP IOT

SQL> select index_name, index_type, table_name, uniqueness from user_indexes;


INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES
------------------------------ --------------------------- ------------------------------ ------------------
IDX_MAP BITMAP T_TESTMAP2 NONUNIQUE
SYS_IOT_TOP_10258 IOT - TOP T_TESTMAP2 UNIQUE
SYS_IOT_TOP_10256 IOT - TOP T_TESTMAP UNIQUE

这里可以看出索引组织表虽然是表,但是存储的结构是索引

SQL> drop table SYS_IOT_MAP_10258;
drop table SYS_IOT_MAP_10258
*
ERROR at line 1:
ORA-28668: cannot reference mapping table of an index-organized table

mapping table 无法被直接删除,但是可以通过move进行删除

SQL> alter table t_testmap2 move nomapping;
alter table t_testmap2 move nomapping
*
ERROR at line 1:
ORA-28670: mapping table cannot be dropped due to an existing bitmap index

在创建了bitmap index的时候,mapping table也无法被删除。

SQL> drop index idx_map;


Index dropped.

SQL> alter table t_testmap2 move nomapping;


Table altered.


SQL> select table_name,iot_name,iot_type from user_tables;


TABLE_NAME IOT_NAME IOT_TYPE
------------------------------ ------------------------------ ------------
T_TESTMAP2 IOT
T_TESTMAP IOT

这时再查询就发现mapping table被删除了。

SQL> select object_name, original_name, type from recyclebin;


no rows selected

而且这种删除不会把mapping table放入回收站。

如果因为误操作等原因想恢复mapping table,可以使用以下命令重建。

SQL> alter table t_testmap move mapping table;


Table altered.

SQL> select table_name,iot_name,iot_type from user_tables;


TABLE_NAME IOT_NAME IOT_TYPE
------------------------------ ----------------------- --------------------------
SYS_IOT_MAP_10256 T_TESTMAP IOT_MAPPING
T_TESTMAP2 IOT
T_TESTMAP IOT

此时mapping table又重新创建。

SQL> create bitmap index idx_map on t_testmap(ename);


Index created.

重新创建索引以进行下面的实验。

SQL> drop table t_testmap;


Table dropped.


SQL> select object_name, original_name, type from recyclebin;


OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ -------------------------------- -------------------------
BIN$tKX6TiUGBMLgQAB/AQATjQ==$0 IDX_MAP INDEX
BIN$tKX6TiUHBMLgQAB/AQATjQ==$0 SYS_IOT_TOP_10256 IOT TOP INDEX
SYS_IOT_MAP_10256 SYS_IOT_MAP_10256 IOT MAPPING TABLE
BIN$tKX6TiUIBMLgQAB/AQATjQ==$0 T_TESTMAP TABLE

如果直接删除表t_testmap,那么该索引组织表上关联的mapping table 和 bitmap index 会一起被放入回收站中。

SQL> flashback table t_testmap to before drop;


Flashback complete.


SQL> select table_name,iot_name,iot_type from user_tables;


TABLE_NAME IOT_NAME IOT_TYPE
------------------------------ ------------------------------ ------------
SYS_IOT_MAP_10256 T_TESTMAP IOT_MAPPING
T_TESTMAP IOT
T_TESTMAP2 IOT


SQL> select index_name, index_type, table_name from user_indexes;


INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ -------- --------------------------- ----------------------
BIN$tKX6TiUGBMLgQAB/AQATjQ==$0   BITMAP        T_TESTMAP
BIN$tKX6TiUHBMLgQAB/AQATjQ==$0   IOT - TOP     T_TESTMAP
SYS_IOT_TOP_10258                           IOT - TOP      T_TESTMAP2

转自:http://blog.sina.com.cn/s/blog_68ea97d10101cmyn.html
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值