oracle数据库表_笔记5:索引组织表
索引组织表
索引组织表(index organizated table,IOT)就是存储在一个索引结构中的表。IOT对信息获取、空间应用和OLAP应用特别有用。
IOT有什么意义?实际上，可以反过来问：堆组织表有什么意义?由于一般认为关系数据库中的所有表都有一个主键，堆组织表难道不是在浪费空间吗?使用堆组织表时，必须为表和表主键上的索引分别留出空间。而IOT则不存在主键的空间开销，因为索引就是数据，数据就是索引，两者已经合二为一。事实上，索引是一个复杂的数据结构，需要大量的工作来管理和维护，而且随着存储的行宽度有所增加，维护的需求也会增加。另一方面，相比之下，堆管理起来则很容易。堆组织表在某些方面的效率要比IOT高。
如果你想保证数据存储在某个位置上，或者希望数据以某种特定的顺序物理存储，IOT就是一种合适的结构。
使用一个IOT将子表信息物理地存储在同一位置上有什么作用?这一点通过一个例子说明。
下面创建并填充一个EMP表：
sys@ORCL>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.
sys@ORCL>alter table emp add constraint emp_pk primary key(empno)
2 /
Table altered.
sys@ORCL>begin
2 dbms_stats.gather_table_stats(user,'EMP',cascade=>true);
3 end;
4 /
PL/SQL procedure successfully completed.
接下来，将这个子表实现两次，一次作为传统的堆表，另一次实现为IOT：
sys@ORCL>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 )
10 /
Table created.
sys@ORCL>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.
如下填充这些表，首先为每个员工插入一个工作地址，其次插入一个家庭地址，再次是原地址，最后是一个学校地址。堆表很可能把数据放在表的最后;数据到来时，堆表只是把它增加到最后，因为此时只有数据到来，而没有数据被删除。过一段时间后，如果有地址被删除，插入就开始变得更为随机，会随机地插入到整个表中的某个位置上。不过，有一点是肯定的，堆表中员工的工作地址与其家庭地址同在一个块上的几率几乎为0。不过，对于IOT，由于键在EMPNO，ADDR_TYPE上，完全可以相信：对应一个给定EMPNO的所有地址都会放在同一个索引块上。填充这些数据的插入语句如下：
sys@ORCL>insert into heap_addresses
2 select empno,'WORK','123 main street','Washington','DC',20123
3 from emp;
72703 rows created.
sys@ORCL>insert into iot_addresses
2 select empno,'WORK','123 main street','Washington','DC',20123
3 from emp;
72703 rows created.
又插入了3次，依次将WORK分别改为HOME、PREV和SCHOOL，然后收集统计信息：
sys@ORCL>exec dbms_stats.gather_table_stats(user,'HEAP_ADDRESSES');
PL/SQL procedure successfully completed.
sys@ORCL>exec dbms_stats.gather_table_stats(user,'IOT_ADDRESSES');
PL/SQL procedure successfully completed.
通过使用AUTOTRACE，可以了解到改变有多大：
sys@ORCL>set autotrace traceonly
sys@ORCL>select *
2 from emp,heap_addresses
3 where emp.empno=heap_addresses.empno
4 and emp.empno=42;
4 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3384982862
-------------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
-------------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 4 | 356 | 8(0)|
00:00:01 |
| 1 | NESTED LOOPS | | 4 | 356 | 8(0)|
00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 43 | 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 | 184 | 6(0)|
00:00:01 |
|* 5 | INDEX RANGE SCAN | SYS_C0012319 | 4 | | 2(0)|
00:00:01 |
-------------------------------------------------------------------------------------
----------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."EMPNO"=42)
5 - access("HEAP_ADDRESSES"."EMPNO"=42)
Statistics
----------------------------------------------------------
36 recursive calls
0 db block gets
52 consistent gets
0 physical reads
0 redo size
1333 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
4 rows processed
这是一个相当常见的计划：按主键访问EMP表，得到行，然后使用这个EMPNO访问地址表，接下来使用索引找出子记录。获取这个数据执行了52次I/O。下面再运行同样的查询，不过这一次地址表实现为IOT：
1 select *
2 from emp,iot_addresses
3 where emp.empno=iot_addresses.empno
4* and emp.empno=42
sys@ORCL>/
4 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 699472546
-------------------------------------------------------------------------------------
-------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
-------------------------------------------------------------------------------------
-------------
| 0 | SELECT STATEMENT | | 4 | 356 | 4
(0)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 356 | 4
(0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 43 | 2
(0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1
(0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | SYS_IOT_TOP_77302 | 4 | 184 | 2
(0)| 00:00:01 |
-------------------------------------------------------------------------------------
-------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."EMPNO"=42)
4 - access("IOT_ADDRESSES"."EMPNO"=42)
Statistics
----------------------------------------------------------
33 recursive calls
0 db block gets
38 consistent gets
0 physical reads
0 redo size
1333 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
4 rows processed
这里少做了14次I/O。
本文来源 我爱IT技术网 http://www.52ij.com/jishu/5285.html 转载请保留链接。
- 评论列表（网友评论仅供网友表达个人看法，并不表明本站同意其观点或证实其描述）
-