Index-Organized Table（IOT）

最新推荐文章于 2021-09-27 19:23:06 发布

huang_tg

最新推荐文章于 2021-09-27 19:23:06 发布

阅读量1.2k

点赞数

分类专栏： ORACLE CONCEPTS 文章标签： table 数据结构 oracle sql library access

本文链接：https://blog.csdn.net/huang_tg/article/details/5731594

版权

ORACLE 同时被 2 个专栏收录

67 篇文章 0 订阅

订阅专栏

CONCEPTS

8 篇文章 0 订阅

订阅专栏

本文主要介绍oracle索引组织表（IOT）的一些特性和使用方法。

索引组织表（IOT）基本是一个存储在索引中的表。他本质上类似于一个B*树群，其中数据与一个键值物理的存储在一起，但他有以下的不同：

u 存在一个数据结构，一个索引结构，而B*树群有一个索引和一个数据段。

存储的数据按键排序，与B* 树群不一样，B*树群中数据按键值组织，但存储的键值本身并不排序。

u 设置IOT的尺寸要比设置群的尺寸稍微容易一些。不需要象散列群那样估计键的最大数目，而且对于如何设置按键存储的数据量的尺寸具有更大的灵活性。

u IOT在两种实现方面非常有用，一种是作为关联表，这些表用语多对多的关系中，另一种是表中数据的物理放置非常重要，但不能预知数据插入的顺序，或者数据不能以群随时保持数据集中放置的次序到达。

一.使用IOT替代关联表以节省空间

关联表一般由两个列或者两个键组成，用来将两个表关联到一起。在oracle数据字典中，可把 DBA_TAB_PRIVS想象为DBA_USERS和DBA_OBJECTS之间的一个关联对象。单个用户在给定的对象上可能具有一个或者多个权限：该给定对象可能具有在其上有权限的一个或者多个用户。

对于关联表，一般应该建立如下的结构：

create table association

(primary_key_table1,

primary_key_table2,

<possibly some columns pertaining to the relationship>);

create index association_idx1

on association (primary_key_table1,primary_key_table2);

create index association_idx2

on association (primary_key_table2,primary_key_table1);

这样，将有三个结构，分别是：一个表和两个索引。这样两个索引允许从TABLE1遍历所有相关的TABLE2的行，或者从TABLE2遍历所有相关的TABLE1的行。在大多数现实中，甚至从不访问该表本身，他是一种荣誉的数据结构，被视为一种必不可少的麻烦，因为他们只会浪费空间。在一些相对较少的情况下，他包含专门针对关系的额外数据，不过这些数据的量一般很小。

可以使用IOT获得相同的效果：

create table association

(primary_key_table1,

primary_key_table2,

<possibly some columns pertaining to the relationship>,

primary key(primary_key_table1,primary_table2))

organization index;

create index association_idx

on association(primary_key_table2);

我们已经消除了对表的需求，不仅这样，而且如果需要检索关于TABLE1和TABLE2中两行之间关系的信息时谋害取消了TABLE ACCESS BY ROWID步骤，请注意，ASSOICATION表上的第二个索引未包含两个列。这是使用IOT的附带效果，用于IOT的逻辑ROWID位于索引结构中，且primary_key_table1的值已经存在。可以用下面的小例子看到这一点：

SQL> create table test

2 (a int,

3 b int,

4 primary key (a,b)

5 )

6 organization index;

表已创建。

SQL> create index test_idx on test(b);

索引已创建。

SQL> set autotrace traceonly explain

SQL> select a,b from test where b=55;

执行计划

--------------------------------------------------------------------------------------------------------------------

Plan hash value: 2882402178

-------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0) | 00:00:01 |

| * 1 | INDEX RANGE SCAN | TEST_IDX | 1 | 26 | 1 (0) | 00:00:01 |

--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

-------------------------------------------------------------------------------------------------------------------

1 - access("B"=55)

Note

-----

- dynamic sampling used for this statement

其中并没有TABLE ACCESS BY ROWID步骤。即使查询中需要列A，oracle也知道可以从索引结构中的逻辑ROWID取得列A的值，从而不需要到表中取他。利用这个事实可以使得在创建IOT上的第二个索引的时候节省大量的磁盘空间。

二.利用IOT集中放置随机插入的数据

除了作为一种节省空间的手段外，通过消除对某些冗余表的需求，IOT在为快速访问而把相关的信息物理的放置在一起的能力方面具有优势。群的缺点之一是要对数据的访问进行控制，以便优化其物理放置。IOT不需要满足这个条件，因为他们结构性地对自身进行调整以适应插入的数据。

考虑一个经常检索某个给定用户拥有的文献表的应用程序。在现实世界中，用户不会在一个会话中插入他曾经拥有的所有文献，此文献表的尺寸是不可预知的，他将随着用户添加删除文献而变动。因此，在传统的堆表中，代表此用户的文献的行将散布在各处。如果执行以下查询：

select * from document table where username=:bind_variable

会出现oracle将利用一个索引来从整个表中读取许多块。如果我们利用IOT物理的将数据集群在一起，就不会发生这种情况。可利用一个简单的模拟以及AUTOTRACE来观察这一点。例如，建立两个表：一个使用IOT，另一个使用基于堆实现：

SQL> create table iot

2 (username varchar2(30),

3 document_name varchar2(30),

4 other_data char(100),

5 constraint iot_pk primary key (username,document_name)

6 )

7 organization index

8 /

表已创建。

SQL> create table heap

2 (username varchar2(30),

3 document_name varchar2(30),

4 other_data char(100),

5 constraint heap_pk primary key (username,document_name)

6 )

7 /

表已创建。

表中使用CHAR(100)，恰好使表中的行平均宽度大约为130个字节。两个表的唯一差别在ORGANIZATION INDEX子句。此子句指示oracle在索引段而不是表段中存储表数据，所以相应表的数据将以索引结构存储。

下面使用样例数据填充这两个表，构造一个循环，给ALL_USERS表中每个用户添加100个文献。在这里使用模拟现实的方式做这件事情，其中给定用户文献不是一次添加，而是随着其他用户添加了许多文献时逐步添加。

SQL> begin

2 for i in 1 .. 100

3 loop

4 for x in (select username from all_users)

5 loop

6 insert into heap

7 (username,document_name,other_data)

8 values

9 (x.username,x.username || '_' || i,'x');

10 insert into iot

11 (username,document_name,other_data)

12 values

13 (x.username,x.username || '_' || i,'x');

14 end loop;

15 end loop;

16 commit;

17 end;

18 /

PL/SQL 过程已成功完成。

这样将从表中读取所有数据，也就是说，对于USER1，将读出对应于他的所有行，然后再读出对应于USER2的所有行，如此往下。此外，将以两种方式读取，一种是利用BULK COLLECT，另一种是利用单行取，目的是了解数组处理对性能和可伸缩性有什么差别，了解IOT与堆表有多大的不同。相应的基准测试例程如下：

SQL> alter session set sql_trace=true;

会话已更改。

SQL> declare

2 type array is table of varchar2(100);

3 l_array1 array;

4 l_array2 array;

5 l_array3 array;

6 begin

7 for i in 1 .. 10

8 loop

9 for x in (select username from all_users)

10 loop

11 for y in (select * from heap single_row

12 where username=x.username)

13 loop

14 null;

15 end loop;

16 for y in (select * from iot single_row

17 where username=x.username)

18 loop

19 null;

20 end loop;

21 select * bulk collect

22 into l_array1,l_array2,l_array3

23 from heap bulk_collect

24 where username=x.username;

25 select * bulk collect

26 into l_array1,l_array2,l_array3

27 from iot bulk_collect

28 where username=x.username;

29 end loop;

30 end loop;

31 end;

32 /

PL/SQL 过程已成功完成。

SQL> alter session set sql_trace=false;

会话已更改。

单行取的TKPROF报表显示如下的统计数据：

*******************************************************************************

SELECT *

FROM

HEAP SINGLE_ROW WHERE USERNAME=:B1

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 210 0.00 0.02 0 2 0 0

Fetch 420 0.06 0.08 0 9010 0 21000

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 631 0.06 0.11 0 9012 0 21000

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation

------- ------------------------------------------------------------------------------------------------------------

21000 TABLE ACCESS FULL HEAP (cr=9010 pr=0 pw=0 time=132018 us)

*******************************************************************************

SELECT *

FROM

IOT SINGLE_ROW WHERE USERNAME=:B1

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 210 0.00 0.00 0 2 0 0

Fetch 420 0.07 0.04 0 1254 0 21000

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 631 0.07 0.05 0 1256 0 21000

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation

------- ------------------------------------------------------------------------------------------------------------

21000 INDEX RANGE SCAN IOT_PK (cr=1254 pr=0 pw=0 time=109616 us)(object id 51377)

*******************************************************************************

堆表读索引然后读表的方法将逐行进行两次I/O。他必须读取索引块，然后再读取表块。另一方面，IOT读取索引就结束了。

利用AUTOTRACE说明了不同的数组尺寸(oracle响应每个取请求所取的行数)给应用程序带来的效果。可以看到，如果一次取许多行而不是之取一行，能够极大的减少查询所执行的I/O。我们可以确切的说明在这一点上IOT大大优于堆表。进一步查看TKPROF报表，可以看到：

*******************************************************************************

SELECT *

FROM

HEAP BULK_COLLECT WHERE USERNAME=:B1

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 210 0.01 0.00 0 2 0 0

Fetch 210 0.09 0.07 0 8820 0 21000

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 421 0.10 0.08 0 8822 0 21000

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation

------- ------------------------------------------------------------------------------------------------------------

21000 TABLE ACCESS FULL HEAP (cr=8820 pr=0 pw=0 time=132173 us)

*******************************************************************************

SELECT *

FROM

IOT BULK_COLLECT WHERE USERNAME=:B1

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 210 0.00 0.00 0 2 0 0

Fetch 210 0.04 0.04 0 1150 0 21000

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 421 0.04 0.04 0 1152 0 21000

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation

------- ------------------------------------------------------------------------------------------------------------

21000 INDEX RANGE SCAN IOT_PK (cr=1150 pr=0 pw=0 time=107672 us)(object id 51377)

*******************************************************************************

采用批量取的IOT的查询I/O不采用相同批量集合的堆表的查询I/O小87%。如果用runstats而不是TKPROF进行这个测试，发现堆表方法使用了600%还要多的栓锁。这是非常重要的差别。

三.IOT小结

与群非常类似，IOT是以减少访问时间的目的，在磁盘上实际组织数据的很有用的数据结构。IOT具有与群相同的良好特性：

u 物理的集中存储数据。

u 提高缓冲区高速缓存效率。

u 降低逻辑I/O。

u 减少对索引的需求，表就是索引。

IOT还具有群没有的一些优点：

u 数据按主键排序存储，这可能很有好处，因为索引是相当复杂的数据结构，他具有到处移动行的能力（群不能做的事），允许更好的将相关数据集中在一起，插入顺序是群要考虑的主要因素，而IOT不需要考虑。如果需要，IOT可以联机重建（重组）。

不过IOT也存在一些限制。除了不能进行直接路径装载以外，IOT还具有其他一些缺点：

u 与群表一样，IOT表的插入比传统表要慢，因为数据必须装在特定的位置。可能需要考虑溢出段。

对于非常宽（行很长）的表，IOT结构可能存在问题。IOT一般最适合长的表，但如果预做安排，也可以用于宽表。

huang_tg

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Index-Organized Table（IOT）

本文主要介绍oracle索引组织表Index-Organized Table（IOT）的一些特性和使用方法。
复制链接

扫一扫

专栏目录