oracle 索引创建索引表,ORACLE创建IOT（索引组织表）

IOT (索引组织表) IOT和堆表的不同 堆表就是我们的那些普通表，在堆表中，他们的数据存放是无序的，数据的insert也是无序的。oracle随机的把新行insert到可用数据块中~~ 但是如果说我想按照某种顺序插入数据，那么这个时候就需要使用我们IOT表 必须有主键 测试一下： SQL> create table t_iot (object_id int primary key,object_name varchar2(60)) organization index; Table created. --往里面插入数据 insert into t_iot select object_id,object_name from dba_objects; --测试性能 select * from t_iot where object_id<100; Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OBJECT_ID"<100) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 9 consistent gets 0 physical reads 0 redo size 2574 bytes sent via SQL*Net to client 485 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 98 rows processed --使用堆表进行测试 SQL> create table t_heap (object_id int primary key,object_name varchar2(60)); Table created select * from t_heap where object_id<100; Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 17 consistent gets 0 physical reads 0 redo size 2910 bytes sent via SQL*Net to client 485 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 98 rows processed 性能差异的原因： 1、IOT表是有序存储的，你的条件object_id<=2，由于你是顺序存储，那么我只需要读到2就好 2、堆表是无序的，所以我必须要扫描更多的数据。 从数据字典上看看他们的差异 select TABLE_NAME,TABLESPACE_NAME,IOT_TYPE,blocks,NUM_ROWS from dba_tables where table_name in ('T_IOT','T_HEAP'); select segment_name, blocks, extents from DBA_segments where segment_name in ('T_IOT','T_HEAP'); 从这两个视图可以看到，oracle并没有吧IOT表当成一个segment，那么那些数据在哪里呢？？ 创建主键会默认创建index select owner,index_name,index_type from dba_indexes where table_name in ('T_IOT','T_HEAP'); OWNER INDEX_NAME INDEX_TYPE ------------------------------ ------------------------------ --------------------------- SYS SYS_C008112 NORMAL SYS SYS_IOT_TOP_83382 IOT - TOP select owner,segment_name,bytes/1024/1024 MB from dba_segments where segment_name in ('SYS_C0011373','SYS_IOT_TOP_73872'); OWNER SEGMENT_NAME MB ------------------------------ --------------------------------------------------------------------------------- ---------- SYS SYS_IOT_TOP_83382 4 SYS SYS_C008112 2