索引组织表
对于索引组织表必须有主键，问题它没有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 TAB