4 索引组织表(IOT Index-Organized Table)适用什么场景
在上一篇文章里，提到了IOT表的基本概念和创建的一些注意事项，接下来讨论一下关于IOT表和Heap使用的一个测试对比，用以说明IOT表适用于什么场景。
1 构建一个主表emp表：
[oracle@oracle11g arch_new]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 15 10:15:19 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
SQL> conn hr/hr
Connected.
SQL> create table emp
2 as
3 select object_id empno,
4 object_name ename,
5 created hiredate,
6 owner job
7 from all_objects
8 ;
Table created.
SQL> select count(*) from emp;
COUNT(*)
----------
55636
SQL> alter table emp add constraint emp_pk primary key(empno);
Table altered.
SQL> exec dbms_stats.gather_table_stats(user,'EMP',cascade=>true);
PL/SQL procedure successfully completed.
SQL>
2 构建一张子表，子表分别通过Heap和IOT两种方式来实现：
构建Heap类型子表：
SQL> create table heap_addresses 2 ( empno references emp(empno) on delete cascade, 3 addr_type varchar2(10), 4 street varchar2(20), 5 city varchar2(20), 6 state varchar2(2), 7 zip number, 8 primary key (empno,addr_type) 9 ); Table created. SQL>
构建IOT类型子表：
SQL> create table iot_addresses 2 ( empno references emp(empno) on delete cascade, 3 addr_type varchar2(10), 4 street varchar2(20), 5 city varchar2(20), 6 state varchar2(2), 7 zip number, 8 primary key (empno,addr_type) 9 ) 10 ORGANIZATION INDEX 11 ; Table created. SQL>
3 分别向两种子表里构造数据，插入WORK，HOME，ORIGINAL，SCHOOL地址：
Heap类型子表插入：
SQL> insert into heap_addresses
2 select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123
3 from emp;
55636 rows created.
SQL> insert into heap_addresses
2 select empno, 'HOME', '123 main street', 'Washington', 'DC', 20123
3 from emp;
55636 rows created.
SQL> insert into heap_addresses
2 select empno, 'ORIGINAL', '123 main street', 'Washington', 'DC', 20123
3 from emp;
55636 rows created.
SQL> insert into heap_addresses
2 select empno, 'SCHOOL', '123 main street', 'Washington', 'DC', 20123
3 from emp;
55636 rows created.
SQL> select count(*) from heap_addresses;
COUNT(*)
----------
222544
SQL>
IOT类型子表插入：
SQL> insert into iot_addresses
2 select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123
3 from emp;
55636 rows created.
SQL> insert into iot_addresses
2 select empno, 'HOME', '123 main street', 'Washington', 'DC', 20123
3 from emp;
55636 rows created.
SQL> insert into iot_addresses
2 select empno, 'ORIGINAL', '123 main street', 'Washington', 'DC', 20123
3 from emp;
55636 rows created.
SQL> insert into iot_addresses
2 select empno, 'SCHOOL', '123 main street', 'Washington', 'DC', 20123
3 from emp;
55636 rows created.
SQL> select count(*) from iot_addresses;
COUNT(*)
----------
222544
SQL>
4 分别收集两种子表的统计信息：
SQL> exec dbms_stats.gather_table_stats(user,'HEAP_ADDRESSES'); PL/SQL procedure successfully completed. SQL> exec dbms_stats.gather_table_stats(user,'IOT_ADDRESSES'); PL/SQL procedure successfully completed. SQL>
5 对比使用不同类型的子表，SQL的执行计划：
SQL> set line 200
SQL> set autot trace
SQL> select *
2 from emp, heap_addresses
3 where emp.empno = heap_addresses.empno
4 and emp.empno = 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 541875893
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 368 | 8 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 368 | 8 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 45 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| HEAP_ADDRESSES | 4 | 188 | 6 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SYS_C00138141 | 4 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."EMPNO"=100)
5 - access("HEAP_ADDRESSES"."EMPNO"=100)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
1351 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
SQL>
SQL> select *
2 from emp, iot_addresses
3 WHERE emp.empno = iot_addresses.empno
4 and emp.empno = 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1475200359
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 368 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 368 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 45 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | SYS_IOT_TOP_184800 | 4 | 188 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."EMPNO"=100)
4 - access("IOT_ADDRESSES"."EMPNO"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1351 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
SQL>
6 小结：通过分析上述2条“几乎”完全相同的SQL语句的执行计划，我们可以看到：
对于第1个SQL(子表通过Heap来实现)，首先通过EMP_PK这个主键来访问主表EMP，得到主表的行记录，然后通过得到的主键EMPNO字段来访问子表HEAP_ADDRESS,通过子表HEAP_ADDRESS的索引SYS_C00138141来获取子表记录。整个SQL耗费了11次内存读，COST为8。
而对于第2个SQL(子表通过IOT来实现)，首先通过EMP_PK这个主键来访问主表EMP，得到主表的行记录，然后直接通过EMPNO字段来访问子表IOT_ADDRESS，而EMPNO字段同时也是子表IOT_ADDRESS的主键字段，这样通过IOT表的主键字段来访问数据就非常快了。整个SQL耗费8次内存读，COST为4，是第1条SQL的1/2。
IOT表适用的场景：
① 应用中有完全由主键字段构成的表；
② 应用中有只会通过主键来访问数据的表；
③ 希望数据以某种特定顺序存储的表；
IOT表不适用于使用非主键字段来查询数据。
.png)
.png)
