新手学习oracle之索引组织表(IOT)

Q132284591

　　索引组织表的概述：
　　在Oracle数据库中，有一类表被称之为索引组织表，即IOT(Index-Organized Table)。顾名思义，所谓的索引组织表， 表面上看是一种表，实质上其数据是以索引的形式来存放的，也就是说IOT表不会占用表段，其占用的是索引段。
　　相比较传统的堆表( heap-organized table，即常见的普通表)而言，IOT表的数据是以已经按主键字段排好序后存放在B-tree索引中的，而堆表的数据则是无序的存放在表段的数据块中。此外，在IOT的索引叶子节点块中，既存放主键字段数据，也存放非主键字段的值。
　　二： 索引组织表(IOT Index-Organized Table)特征：
　　通过IOT表的主键字段来访问数据可以快速完成，因为IOT表的数据全部存放在B-Tree索引上，只需定位到索引上的数据即可，而无需再去像访问heap表那样进一步通过索引去定位表段上的数据；
　　对IOT表执行DML操作，只会影响到B-Tree索引； 通过IOT表的主键字段快速范围访问数据很快，因为记录已经是事先按主键排好序的；
　　IOT表可以有效的降低存储开销，因为主键字段的数据只是存放在B-Tree索引上， 并没有像heap那样，主键字段数据既存放在表段上，也存放在索引上；
　　IOT表除了像Heap表那样可以支持约束、触发器、LOB字段、对象字段、分区、并行操作、在线重定义、复制操作等，还支持主键字段压缩、提供溢出存储段(Overflow storage area )、二次索引(Secondary indexes, including bitmap indexes)
　　三 ：创建索引组织表(IOT Index-Organized Table)
　　创建IOT表时，必须包含下列2个从句：
　　1 ORGANIZATION INDEX，用来标识该表是IOT表；
　　2 在建表的同时要指定主键约束，可以是单字段主键，也可以是复合主键约束。
　　创建IOT表时，也可以同时指定下列3个从句：
　　1 OVERFLOW从句，用于标识非主键字段存放在独立的溢出存储段数据区；
　　2 PCTTHRESHOLD value，如果指定了溢出存储段的话，该值用于限定可以存放在索引数据块中的最大数据的百分比，
　　即如果IOT表中的行记录超过该值的话，剩余的字段就存放在溢出存储段数据区。也就是说，
　　IOT表中的一条记录有可能被拆分成两部分：头数据区(Head Piece)和尾数据区(Tail Piece)。将主键字段和不超过PCTTHRESHOLD限定的其它非主键字段存放在头数据区，
　　而将其它的非主键字段存放在尾数据区。因此，此时的IOT表的索引记录存放的数据就成了主键字段+满足PCTTHRESHOLD限定的其它非主键字段+指针，
　　指针指向剩余非主键字段存放的地址；PCTTHRESHOLD的取值范围是1-50，默认值是50；
　　3 INCLUDING从句，用于显示声明哪些非主键字段可以和主键字段一起存放在索引数据块中。这样，剩下的非主键字段就会存放到独立的溢出存储段数据区
　　创建一个索引表：
　　SQL> create table iot_table(owner,object_type,object_name,object_id,
　　2 constraint iot_pk primary key(object_id,object_type,object_name)
　　3 )
　　4 organization index
　　5 tablespace test
　　6 pctthreshold 50
　　7 overflow tablespace users
　　8 as
　　9 select distinct owner,object_type,object_name,object_id from all_objects;
　　查看表的信息，可以看到该表不属于任何表空间，因为没有占用数据段
　　SQL> select table_name,tablespace_name,iot_name,iot_type from user_tables where table_name='IOT_TABLE';
　　TABLE_NAME                          TABLESPACE_NAME                       IOT_NAME
　　------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
　　IOT_TYPE
　　------------------------
　　IOT_TABLE
　　IOT
　　能看到我们的表名和IOT_TYPE 类型，其他信息均没有
　　然后查看我们的索引信息
　　SQL> select index_name,index_type,table_name,tablespace_name,table_type,pct_threshold from user_indexes where table_name='IOT_TABLE';
　　INDEX_NAME                          INDEX_TYPE                       TABLE_NAME
　　------------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------------
　　TABLESPACE_NAME                       TABLE_TYPE       PCT_THRESHOLD
　　------------------------------------------------------------ ---------------------- -------------
　　IOT_PK                            IOT - TOP                       IOT_TABLE
　　TEST
　　通过这个语句，就可以看到我们占用的表空间是test，index_name是IOT_PK
　　这个就是我们文章开头说的，IOT表的数据存储在索引块中，包括主键列和非主键列
　　四：索引组织表适用的场景
　　先通过对比普通的堆表(heap)和IOT表的列子，来引用IOT表适用的场景
　　创建一张表，当做母表
　　SQL> create table emp as select object_id e_id,
　　2   object_name   ename,
　　3   created     hiredate,
　　4   owner      job
　　5 from all_objects;
　　SQL> select count(*) from emp;
　　COUNT(*)
　　----------
　　65581

　　增加一个主键：SQL>>　　对表进行分析，使用dbms的包，获取的结果要比使用analyze分析的要准确
　　SQL> exec dbms_stats.gather_table_stats(user,'EMP',cascade=>true);
　　PL/SQL procedure successfully completed.
　　Cascade 是分析表的键和列信息
　　基于emp，创建一张heap的子表和一张iot的子表
　　创建一个普通表
　　create table heap_table (
　　2   empno references emp(e_id) on delete cascade,
　　3   add_type    varchar2(15),
　　4   street     varchar2(15),
　　5   city      varchar2(10),
　　6   state      varchar2(5),
　　7   zip       number,
　　8   primary key (empno,add_type)
　　9   );
　　SQL> create table iot_address (
　　2   empno references emp(e_id) on delete cascade,
　　3   add_type    varchar2(15),
　　4   street     varchar2(15),
　　5   city      varchar2(10),
　　6       state      varchar2(5),
　　7   zip       number,
　　8   primary key (empno,add_type)
　　9  )
　　10   organization index;
　　往两个表中插入数据，数据都是杂乱的，主要是为了获取更多的记录
　　SQL> insert into heap_table select e_id,'work','123 mian street','shanghai','CC','1001' from emp;
　　65581 rows created.
　　SQL> insert into heap_table select e_id,'home','123 mian street','shanghai','CC','1001' from emp;
　　SQL> select count(*) from heap_table;
　　COUNT(*)
　　----------
　　262324
　　最后大概有三四十W条数据就行了，
　　SQL> insert into iot_address select e_id,'bee','123 mian street','shanghai','CC','1001' from emp;
　　SQL> select count(*) from iot_address;
　　COUNT(*)
　　----------
　　262324
　　开启跟踪的命令： set autotrace traceonly;
　　select * from biao.emp e,biao.heap_table b where e.e_id=b.empno and e.e_id=200;
　　|  0 | SELECT STATEMENT       |       |   4 |  396 |   4  (0
　　)| 00:00:01 |
　　|  1 | NESTED LOOPS        |       |   4 |  396 |   4  (0
　　)| 00:00:01 |
　　|  2 |  TABLE ACCESS BY INDEX ROWID| EMP     |   1 |  44 |   2  (0
　　)| 00:00:01 |
　　|* 3 |  INDEX UNIQUE SCAN     | EMP_PK    |   1 |    |   1  (0
　　)| 00:00:01 |
　　|  4 |  TABLE ACCESS BY INDEX ROWID| HEAP_TABLE  |   4 |  220 |   2  (0
　　)| 00:00:01 |
　　|* 5 |  INDEX RANGE SCAN     | SYS_C0010824 |   4 |    |   1  (0
　　)| 00:00:01 |
　　--------------------------------------------------------------------------------
　　-------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　3 - access("E"."E_ID"=200)
　　5 - access("B"."EMPNO"=200)
　　Note
　　-----
　　- dynamic sampling used for this statement (level=2)
　　Statistics
　　----------------------------------------------------------
　　0 recursive calls
　　0 db block gets
　　11 consistent gets
　　0 physical reads

　　0 redo>　　1352 bytes sent via SQL*Net to client
　　523 bytes received via SQL*Net from client
　　2 SQL*Net roundtrips to/from client
　　0 sorts (memory)
　　0 sorts (disk)
　　4 rows processed
　　然后看emp和iot_address的结合读
　　select * from biao.emp e,biao.iot_address b where e.e_id=b.empno and e.e_id=200;
　　|  0 | SELECT STATEMENT       |          |  46 | 4554 |  19
　　(0)| 00:00:01 |
　　|  1 | NESTED LOOPS        |          |  46 | 4554 |  19
　　(0)| 00:00:01 |
　　|  2 |  TABLE ACCESS BY INDEX ROWID| EMP        |   1 |  44 |   2
　　(0)| 00:00:01 |
　　|* 3 |  INDEX UNIQUE SCAN     | EMP_PK      |   1 |    |   1
　　(0)| 00:00:01 |
　　|* 4 |  INDEX RANGE SCAN      | SYS_IOT_TOP_73584 |  46 | 2530 |  17
　　(0)| 00:00:01 |
　　--------------------------------------------------------------------------------
　　------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　3 - access("E"."E_ID"=200)
　　4 - access("B"."EMPNO"=200)
　　Note
　　-----
　　- dynamic sampling used for this statement (level=2)
　　Statistics
　　----------------------------------------------------------
　　0 recursive calls
　　0 db block gets
　　7 consistent gets
　　0 physical reads

　　0 redo>　　1352 bytes sent via SQL*Net to client
　　523 bytes received via SQL*Net from client
　　2 SQL*Net roundtrips to/from client
　　0 sorts (memory)
　　0 sorts (disk)
　　4 rows processed
　　关于统计信息的解释：
　　db block gets ： 从buffer cache中读取的block的数量
　　consistent gets： 从buffer cache中读取的undo数据的block的数量
　　physical reads： 从磁盘读取的block的数量

　　redo>　　sorts (memory) ：在内存执行的排序量
　　sorts (disk) ：在磁盘上执行的排序量
　　Physical Reads通常是我们最关心的，如果这个值很高，
　　说明要从磁盘请求大量的数据到Buffer Cache里，通常意味着系统里存在大量全表扫描的SQL语句，这会影响到数据库的性能，
　　因此尽量避免语句做全表扫描，对于全表扫描的SQL语句，建议增 加相关的索引，优化SQL语句来解决。
　　通过上面两个完全相同的SQL语句，对比两个的执行计划，发现
　　对于子表是普通表的heap_table，需要经过五个步骤，首先通过EMP_PK 主键先找到主表emp中E_ID只为200的行，然后通过主表字段e_id 来访问子表heap_table，再通过子表SYS_C0010824 获取子表记录，整个过程需要11次内存读
　　对于子表是IOT的iot_adress EMP_PK这个主键来访问主表EMP，得到主表的行记录，然后直接通过EMPNO字段来访问子表IOT_ADDRESS，
　　而EMPNO字段同时也是子表IOT_ADDRESS的主键字段，这样通过IOT表的主键字段来访问数据就非常快了。整个SQL耗费7次内存读
　　刚才的两个SQL是对比二者的读取速度来的，接着看下占用的空间
　　首先对两个表进行解析：
　　SQL> exec dbms_stats.gather_table_stats('BIAO','HEAP_TABLE');
　　PL/SQL procedure successfully completed.
　　SQL> exec dbms_stats.gather_table_stats('BIAO','IOT_ADDRESS');
　　PL/SQL procedure successfully completed.
　　SQL> select num_rows,blocks,empty_blocks as empty,avg_space,avg_row_len from dba_tables where table_name='HEAP_TABLE';
　　NUM_ROWS   BLOCKS   EMPTY AVG_SPACE AVG_ROW_LEN
　　---------- ---------- ---------- ---------- -----------
　　262324    1756     0     0     42
　　占用1756个块，一个块的大小是8k，1756 * 8 有14M之多
　　因为IOT表是不占用空间段的，是存放在索引块里面的，通过上面的命令是查找不出来的
　　exec dbms_stats.gather_index_stats(OWNER,INDEX_NAME)
　　而IOT的索引是表的主键约束名，如果你指定了约束名，就可以直接analyze分析主键约束名就可以了：
　　analyze index SYS_IOT_TOP_73584（主键约束名） validate structure;
　　而像我们的列子，是没有指定约束名的，就只有通过以下语句进行查看约束名：
　　查看基于表和OWNER的约束名：
　　SELECT constraint_name, table_name, r_owner, r_constraint_name
　　FROM all_constraints where table_name='IOT_ADDRESS' and owner='BIAO';
　　SQL> select lf_blks, br_blks, used_space/1024/1024 MB,opt_cmpr_count, opt_cmpr_pctsave from index_stats;
　　LF_BLKS  BR_BLKS     MB OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
　　---------- ---------- ---------- -------------- ----------------
　　4013     12 12.3336878       1        4
　　LF_BLKS 是叶块，BR_BLKS 是枝块 占用的空间是12M多，OPT_CMPR_COUNT是代表可以被压缩的值
　　总结下：IOT表和堆表的优势：
　　快速的主键访问数据（如果不是依据主键查询数据，不适合IOT表）
　　节省存储空间
　　有以下的限制：
　　必须有一个主键，而且主键作为查询的条件(where)子句中
　　不能使用唯一性约束
　　不能使用簇
　　结合以上两点，IOT使用的环境
　　应用中完全有 由主键构成的表
　　应用中有只有 由主键来访问数据中的表
　　希望数据以某种特定顺序存储的
　　参考资料：http://blog.csdn.net/tianlesoftware/article/details/5827245
　　URL 可以更多的了解和学习统计信息
　　五：IOT表压缩
　　刚才我们有通过analyze 对IOT进行分析，OPT_CMPR_COUINT表示最优压缩数，使用compress 1 来重建IOT表

　　SQL>>
　　Table>　　select lf_blks, br_blks, used_space/1024/1024 MB,opt_cmpr_count, opt_cmpr_pctsave from index_stats;
　　无数据，再次进行分析
　　SQL> analyze index SYS_IOT_TOP_73584 validate structure;  约束名没有变的
　　select lf_blks, br_blks, used_space/1024/1024 MB,opt_cmpr_count, opt_cmpr_pctsave from index_stats;
　　LF_BLKS  BR_BLKS     MB OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
　　---------- ---------- ---------- -------------- ----------------
　　1725     4 11.7874393       1        0
　　现在发现叶子块有1725个，枝块4个，占用空间11.78MB
　　因为分析出来的最优压缩为1，如果你想指定压缩数为2
　　会提示你：

　　SQL>>　　alter table iot_address move compress 2
　　*
　　ERROR at line 1:
　　ORA-25194: invalid COMPRESS prefix length value
　　说明：
　　使用不同的压缩级别，索引的占用的空间大小逐渐变小
　　OPT_CMPR_COUNT字段表示最优压缩数，对于该索引来说为1是最优的，超过这个值，会提示你ORA-25194 error message
　　OPT_CMPR_PCTSAVE字段值则表示最优压缩节省的空间百分比，也就是说使用压缩值为1后，大概能节约4%的空间
　　关于IOT表的学习，先到这，后续会再学习相关的知识，以上总结大都来自网络，oracle QQ交流群：329638713 新手学习，互帮互助！