索引组织表
程序员文章站
2022-05-24 12:35:49
...
索引组织表 对于索引组织表必须有主键，问题它没有rowid，且只有逻辑rowid猜测到溢出部分。索引组织表可以进行分区，但只能是范围分区、散列分区、列表分区，但不能为复合分区。另外索引组织 表其实就是根据表的主键按照一定的顺序将数据表和索引进行了整合
索引组织表
对于索引组织表必须有主键，问题它没有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不准确，需要重新调整索引：
重建索引组织表：
使用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';