Oracle中的iot_type,oracle iot索引组织表(二)

---如iot分区,overflow segment也基于主键进行对应分区

If table is partitioned, then the database equipartitions the overflow data segments with the primary key index segments.

ORA-25175: no PRIMARY KEY constraint found

SQL> create table t_test(a int primary key) organization index mapping table;

create table t_test(a int primary key) organization index mapping table

ORA-00955: name is already used by an existing object

SQL> drop table t_test purge;

Table dropped

SQL> create table t_test(a int primary key) organization index mapping table;

Table created

SQL> drop table t_test purge;

Table dropped

SQL> create table t_test(a int primary key) organization index nomapping;

Table created

SQL> drop table t_test purge;

Table dropped

SQL> create table t_test(a int primary key) organization index mapping table pctthreshold 2;

Table created

SQL> drop table t_test purge;

Table dropped

SQL> create table t_test(a int primary key) organization index mapping table pctthreshold 2 including a overflow tablespace users;

Table created

SQL> drop table t_test purge;

Table dropped

SQL> create table t_test(a int primary key) organization index mapping table pctthreshold 2 including a overflow tablespace users compress 2;

create table t_test(a int primary key) organization index mapping table pctthreshold 2 including a overflow tablespace users compress 2

ORA-00922: missing or invalid option

SQL> create table t_test(a int primary key) organization index mapping table pctthreshold 2  compress 2 including a overflow tablespace users;

create table t_test(a int primary key) organization index mapping table pctthreshold 2  compress 2 including a overflow tablespace users

ORA-25193: cannot use COMPRESS option for a single column key

SQL> create table t_test(a int primary key,b int) organization index mapping table pctthreshold 2  compress 2 including a overflow tablespace users;

create table t_test(a int primary key,b int) organization index mapping table pctthreshold 2  compress 2 including a overflow tablespace users

ORA-25193: cannot use COMPRESS option for a single column key

SQL> create table t_test(a int primary key,b int) organization index mapping table pctthreshold 2  nocompress including a overflow tablespace users;

Table created

SQL> drop table t_test purge;

Table dropped

SQL> create table t_test(a int primary key,b int) organization index mapping table pctthreshold 2  compress 2 including a overflow tablespace users;

create table t_test(a int primary key,b int) organization index mapping table pctthreshold 2  compress 2 including a overflow tablespace users

ORA-25193: cannot use COMPRESS option for a single column key

SQL> create table t_test(a int primary key,b int) organization index mapping table pctthreshold 2  b compress 2 including a overflow tablespace users;

create table t_test(a int primary key,b int) organization index mapping table pctthreshold 2  b compress 2 including a overflow tablespace users

ORA-00922: missing or invalid option

---compress的级别为主键列-1

SQL> create table t_test(a int ,b int,c int,primary key(a,b)) organization index mapping table pctthreshold  2 compress 1 including c overflow tablespace users;

Table created

----iot的操作限制:

Restrictions on Index-Organized Tables Index-organized tables are subject to the following restrictions:

----iot仅存储逻辑rowid而非物理rowid,最好用urowid;

The ROWID pseudocolumn of an index-organized table returns logical rowids instead of physical rowids. A column that you create of type

ROWID cannot store the logical rowids of the IOT. The only data you can store in a ROWID column is rowids from heap-organized tables.

If you want to store the logical rowids of an IOT, then create a column of type UROWID instead. A column of type UROWID can store both

physical and logical rowids.

--iot不能定义虚拟列

You cannot define a virtual column for an index-organized table.

--iot不能定义组合范围,哈希,列表分区子句

You cannot specify the composite_range_partitions, composite_hash_partitions, or composite_list_partitions clauses for an index-organized table.

----如iot是nested tqble or varray,则不能指定表分区语句

If the index-organized table is a nested table or varray, then you cannot specify table_partitioning_clauses.

---iot应用场景

----适用于基于主键访问表,可考虑替换为iot

Index-organized tables are therefore best suited for primary key-based access and manipulation. An index-organized table is an alternative to:

----如一个非集表创建了create index主键

A noncluster table indexed on the primary key by using the CREATE INDEX statement

---存储在索引cluster的cluster table用cleate cluster,表的主键映射到cluster key

A cluster table stored in an indexed cluster that has been created using the CREATE CLUSTER statement that maps the primary key for the table to the cluster key