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