索引组织表

索引组织表   

    对于索引组织表必须有主键，问题它没有rowid，且只有逻辑rowid猜测到溢出部分。索引组织表可以进行分区，但只能是范围分区、散列分区、列表分区，但不能为复合分区。另外索引组织

表其实就是根据表的主键按照一定的顺序将数据表和索引进行了整合一张表，如果该字段过大，那么访问相关特定数据行就会出现穿越多个块，因此对于不经常访问的列归为溢出部分，如果

启用溢出部分需要使用overflow关键字，including字段指定该列之后的列都会放到溢出部分中，另外pctthreshold指定当该块数据达到一个标准值之后，那么其余部分将会放到溢出部分中。

SQL> create table locations_iot(
  2  location_id number(4) not null,
  3  street_address varchar2(40),
postal_code varchar2(12),
  4    5  city varchar2(20)
  6  )
  7  organization index; 
organization index
             *
ERROR at line 7:
ORA-25175: no PRIMARY KEY constraint found


SQL> create table locations_iot(
  2  location_id number(4) not null,
  3  street_address varchar2(40),
postal_code varchar2(12),
  4    5  city varchar2(20),
  6  constraint locations_iot_pk primary key(location_id)
  7  )
  8  organization index;

Table created.

SQL> create table locatios_iot(
  2  location_id number(4) not null,
  3  street_address varchar2(40),
  4  postal_code varchar2(12),
  5  city varchar2(20),
  6  constraint locatios_iot primary key(location_id,street_address)
  7  ) organization index
  8  partition by list(street_address)
  9  (
 10  partition part1 values('Happy','New','YEAR') tablespace test,
 11  partition part2 values('My','leadership','do','not','let','me','go','home') tablespace test
 12  );

Table created.

SQL> 

使用analyze table xxx list chained rows;确定pctthreshold设置是否合理。
出现表的链接记录和迁移记录的rowid都会保存到表chaind_rows中，但是该表必须提前创建，
eg：

SQL> @?/rdbms/admin/utlchain.sql

Table created.

SQL> select table_name from user_tables where table_name='CHAINED_ROWS';

TABLE_NAME
------------------------------
CHAINED_ROWS

SQL> analyze table t list chained rows;

Table analyzed.

SQL> select * from chained_rows;

no rows selected

SQL> 

 

注意在添加映射表或是overflow之后都会自动创建相关表：
eg：

SQL> alter table employees 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_87909              EMPLOYEES                      IOT_MAPPING
SYS_IOT_OVER_87909             EMPLOYEES                      IOT_OVERFLOW
EMPLOYEES                                                     IOT
ACHIVEMENT
ACHIVEEMENT
T_LIST
RANGE_LIST
RANGE_LIST_PARTITION
CHAINED_ROWS
EMP
SALGRADE

TABLE_NAME                     IOT_NAME                       IOT_TYPE
------------------------------ ------------------------------ ------------
DEPT

12 rows selected.

SQL> alter table employees move nomapping;

Table altered.


SQL> select table_name,iot_name,iot_type from user_tables;                             

TABLE_NAME                     IOT_NAME                       IOT_TYPE
------------------------------ ------------------------------ ------------
SYS_IOT_OVER_87909             EMPLOYEES                      IOT_OVERFLOW
EMPLOYEES                                                     IOT
ACHIVEMENT
ACHIVEEMENT
T_LIST
RANGE_LIST
RANGE_LIST_PARTITION
CHAINED_ROWS
EMP
SALGRADE
DEPT

11 rows selected.


SQL> alter table employees 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_87909              EMPLOYEES                      IOT_MAPPING
SYS_IOT_OVER_87909             EMPLOYEES                      IOT_OVERFLOW
EMPLOYEES                                                     IOT
ACHIVEMENT
ACHIVEEMENT
T_LIST
RANGE_LIST
RANGE_LIST_PARTITION
CHAINED_ROWS
EMP
SALGRADE

TABLE_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不准确，需要重新调整索引：

住：查看二级索引是否会导致物理猜测不准确可以通过查看user_indexes的pct_direct_access如果该值低于100说明二级索引存在迁移物理估算不够准确，低于80那么就存在很严重的性能问题了

重建索引组织表：
使用alter table move操作，如果是在线不会防止其他 访问应该使用online字，但是如果是分区索引组织表，那么只能通过user_tab_partitions获得分区名在对每个分区进行move
alter table employees move online；
alter table employees move partition part1;

查看索引组织表信息：
select a.table_name,
       a.index_name,
       a.index_type,
       a.pct_threshold,
       nvl(column_name, 'NONE') include_column
  from user_indexes a
  join user_tab_columns b
    on (a.table_name = b.table_name)
   and (a.include_column = b.column_id)
 where index_type = 'IOT - TOP';