索引组织表 - 数据库编程

LE_NAME IOT_NAME IOT_TYPE ------------------------------ ------------------------------ ------------ DEPT 12 rows selected. SQL>

压缩索引组织表：
主要是值压缩复合主键列，如果主键设置为单列则无法进行压缩。
eg：

SQL> alter table employees move compress 1;      
alter table employees move compress 1
            *
ERROR at line 1:
ORA-25193: cannot use COMPRESS option for a single column key

SQL> SET PAGESIZE 2000
SQL> R
  1* SELECT DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','AMY') FROM DUAL

DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','AMY')
--------------------------------------------------------------------------------

  CREATE TABLE "AMY"."EMPLOYEES"
   (    "EMPLOYEE_ID" NUMBER(6,0),
        "FIRST_NAME" VARCHAR2(20),
        "LAST_NAME" VARCHAR2(25),
        "EMAIL" VARCHAR2(24),
        "PHONE_NUMBER" VARCHAR2(20),
        "HIREE_DATE" DATE,
         CONSTRAINT "EMPLOYEES_PK" PRIMARY KEY ("EMPLOYEE_ID") ENABLE
   ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 163840 NEXT 163840 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TEST"
 PCTTHRESHOLD 40 MAPPING TABLE  INCLUDING "LAST_NAME" OVERFLOW
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 163840 NEXT 163840 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TEST"


SQL> DROP TABLE EMPLOYEES;

Table dropped.

SQL> PURGE RECYCLEBIN;

Recyclebin purged.

SQL> CREATE TABLE "AMY"."EMPLOYEES"
  2     (    "EMPLOYEE_ID" NUMBER(6,0),
  3          "FIRST_NAME" VARCHAR2(20),
  4          "LAST_NAME" VARCHAR2(25),
  5          "EMAIL" VARCHAR2(24),
  6          "PHONE_NUMBER" VARCHAR2(20),
  7          "HIREE_DATE" DATE,
  8           CONSTRAINT "EMPLOYEES_PK" PRIMARY KEY ("EMPLOYEE_ID","FIRST_NAME") ENABLE
  9     ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
 10    STORAGE(INITIAL 163840 NEXT 163840 MINEXTENTS 1 MAXEXTENTS 2147483645
 11    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 12    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 13    TABLESPACE "TEST"
 14   PCTTHRESHOLD 40 MAPPING TABLE  INCLUDING "LAST_NAME" OVERFLOW
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
 15   16    STORAGE(INITIAL 163840 NEXT 163840 MINEXTENTS 1 MAXEXTENTS 2147483645
 17    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 18    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 19    TABLESPACE "TEST";

Table created.

SQL> ALTER TABLE EMPLOYEES MOVE COMPRESS 1;

Table altered.

创建二级位图索引：‘

SQL> 
SQL> ALTER TABLE EMPLOYEES MOVE MAPPING TABLE;

Table altered.

SQL> DESC EMPLOYEES;
 Name                                                                                                              Null     Type
 ----------------------------------------------------------------------------------------------------------------- -------- 

----------------------------------------------------------------------------
 EMPLOYEE_ID                                                                                                       NOT NULL NUMBER(6)
 FIRST_NAME                                                                                                        NOT NULL VARCHAR2(20)
 LAST_NAME                                                                                                                  VARCHAR2(25)
 EMAIL                                                                                                                      VARCHAR2(24)
 PHONE_NUMBER                                                                                                               VARCHAR2(20)
 HIREE_DATE                                                                                                                 DATE


SQL> CREATE BITMAP INDEX EMPLOYEES_BTIDX1 ON EMPLOYEES(LAST_NAME);

Index created.

SQL>

由于时间之后索引组织表可能存在叶块分裂导致逻辑猜测rowid不准确，需要重新调整索引：

重建索引组织表：
使用alter table move操作，如果是在线不会防止其他访问应该使用online字，但是如果是分区索引组织表，那么只能通过user_tab_partitio

索引组织表(二)