---如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

oracle iot索引組織表(二)

相關文章