索引組織表上建立BITMAP索引（一）

Oracle的索引組織表也支援BITMAP索引型別，不過需要對映表的支援。

 

 

如果沒有建立對映表，直接建立BITMAP索引，則會報錯：

SQL> CREATE TABLE T_INDEX_ORG
  2  (ID NUMBER PRIMARY KEY,
  3  NAME VARCHAR2(30),
  4  TYPE VARCHAR2(30))
  5  ORGANIZATION INDEX;

表已建立。

SQL> CREATE BITMAP INDEX IND_B_ORG_TYPE
  2  ON T_INDEX_ORG (TYPE);
ON T_INDEX_ORG (TYPE)
   *
第 2 行出現錯誤:
ORA-28669: 在沒有對映表的情況下, 不能在 IOT 上建立點陣圖索引

只有建立了對映表MAPPING TABLE，建立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;

表已建立。

SQL> CREATE BITMAP INDEX IND_B_ORG_TYPE
  2  ON T_INDEX_ORG (TYPE);

索引已建立。

可以看一下這時索引組織表的結構：

SQL> SELECT TABLE_NAME, IOT_NAME, IOT_TYPE
  2  FROM USER_TABLES
  3  WHERE IOT_TYPE IS NOT NULL;

TABLE_NAME                     IOT_NAME                       IOT_TYPE
------------------------------ ------------------------------ ------------
SYS_IOT_MAP_32375              T_INDEX_ORG                    IOT_MAPPING
T_INDEX_ORG                                                   IOT

SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME = 'T_INDEX_ORG';

INDEX_NAME           INDEX_TYPE        TABLE_NAME           UNIQUENES
-------------------- ----------------- -------------------- ---------
IND_B_ORG_TYPE       BITMAP            T_INDEX_ORG          NONUNIQUE
SYS_IOT_TOP_32375    IOT - TOP         T_INDEX_ORG          UNIQUE

現在這個索引組織表的結構已經很清晰了，T_INDEX_ORG說是一張表，其實是以索引的結構來報錯的，這個索引就是表的主鍵SYS_IOT_TOP_32375。而IND_B_ORG_TYPE索引是表的第二索引，由於是BITMAP型別，因此要求建立一個MAPPING TABLE。從上面的查詢可以看到對應的MAPPING TABLE就是SYS_IOT_MAP_32375。

無論是ORACLE建立的主鍵名稱，還是自動生成的MAPPING TABLE名稱，在字尾部分都包括了一個32375數值。一般來說這種數值都是取自一個序列，但是兩個物件取值相同，說明這個數值是存在一定意義的，懷疑32375是資料庫物件的ID：

SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, OBJECT_ID
  2  FROM DBA_OBJECTS
  3  WHERE OBJECT_ID = 32375;

OWNER           OBJECT_NAME          OBJECT_TYPE         OBJECT_ID
--------------- -------------------- ------------------ ----------
YANGTK          T_INDEX_ORG          TABLE                   32375

果然如此，那麼現在不但可以根據查詢得到一個MAPPING TABLE對應的索引組織表，也可以根據索引組織表的OBJECT_ID以及MAPPING TABLE的命名規則，找到MAPPING TABLE。

上面演示的是在建立索引組織表的時候建立對應的MAPPING TABLE，有些時候是需要在一個已經存在的索引組織表上新增對映表的，不過Oracle的官方文件SQL REFERENCES中的語法圖這裡似乎有點問題。而且Oracle的文字描述似乎也有些問題，Oracle提到可以將一個堆表改變為索引組織表，而測試發現似乎根本行不通，對應的MAPPING TABLE語法也是有問題的。

SQL> DROP TABLE T_INDEX_ORG;

表已刪除。

SQL> CREATE TABLE T_INDEX_ORG
  2  (ID NUMBER PRIMARY KEY,
  3  NAME VARCHAR2(30),
  4  TYPE VARCHAR2(30))
  5  ORGANIZATION INDEX;

表已建立。

SQL> ALTER TABLE T_INDEX_ORG MOVE MAPPING TABLE;

表已更改。

SQL> SELECT TABLE_NAME, IOT_NAME, IOT_TYPE
  2  FROM USER_TABLES
  3  WHERE IOT_TYPE IS NOT NULL;

TABLE_NAME                     IOT_NAME                       IOT_TYPE
------------------------------ ------------------------------ ------------
SYS_IOT_MAP_32383              T_INDEX_ORG                    IOT_MAPPING
T_INDEX_ORG                                                   IOT

對映表無法直接刪除，直接刪除會報錯：

SQL> DROP TABLE SYS_IOT_MAP_32383;
DROP TABLE SYS_IOT_MAP_32383
           *
第 1 行出現錯誤:
ORA-28668: 無法引用按索引組織的表的對映表

可以仍然透過MOVE來刪除索引組織表的對映表：

SQL> ALTER TABLE T_INDEX_ORG MOVE NOMAPPING;

表已更改。

SQL> SELECT TABLE_NAME, IOT_NAME, IOT_TYPE
  2  FROM USER_TABLES
  3  WHERE IOT_TYPE IS NOT NULL;

TABLE_NAME                     IOT_NAME                       IOT_TYPE
------------------------------ ------------------------------ ------------
T_INDEX_ORG                                                   IOT

而如果在建立了BITMAP索引的索引組織表上，是無法直接去掉對映表的：

SQL> ALTER TABLE T_INDEX_ORG MOVE MAPPING TABLE;

表已更改。

SQL> CREATE BITMAP INDEX IND_B_INDEX_TYPE
  2  ON T_INDEX_ORG (TYPE);

索引已建立。

SQL> ALTER TABLE T_INDEX_ORG MOVE NOMAPPING;
ALTER TABLE T_INDEX_ORG MOVE NOMAPPING
            *
第 1 行出現錯誤:
ORA-28670: 由於某個現有點陣圖索引而導致無法刪除對映表

必須先執行刪除BITMAP索引的操作，然後才能去掉對映表：

SQL> DROP INDEX IND_B_INDEX_TYPE;

索引已刪除。

SQL> ALTER TABLE T_INDEX_ORG MOVE NOMAPPING;

表已更改。

當然，如果索引組織表本身不需要了，也可以直接刪除索引組織表。