索引组织表（IOT）和堆组织表的性能对比测试

索引组织表（index organized table，IOT）就是存储在一个索引结构中的表，存储在堆中的表是无组织的，IOT中的数据则按照主键存储和排序。

使用堆组织表，Oracle会为表和表主键索引分别留出空间，而IOT则不存在主键的空间开销，因为索引就是数据，数据就是索引，两者已经合而为一了。

为了比较IOT和堆组织表的效率，首先创建两张表：

create table iot

( username varchar2(30),

  document_name varchar2(30),

  other_data       char(200),

  constraint iot_pk

  primary key (username,document_name)

)

organization index

/

 

create table heap

( username varchar2(30),

  document_name varchar2(30),

  other_data       char(200),

  constraint heap_pk

  primary key (username,document_name)

)

/

 

先测试插入效率，使用runstats工具：

exec runstats_pkg.rs_start

 

begin

    for i in 1 .. 100

    loop

        for x in ( select username

                     from all_users )

        loop

            insert into heap

            (username,document_name,other_data)

            values

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

        end loop;

    end loop;

    commit;

end;

/

 

exec runstats_pkg.rs_middle

 

begin

    for i in 1 .. 100

    loop

        for x in ( select username

                     from all_users )

        loop

            insert into iot

            (username,document_name,other_data)

            values

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

        end loop;

    end loop;

    commit;

end;

/

 

exec runstats_pkg.rs_stop(100)

 

测试结果如下：

Run1 ran in 57 hsecs

Run2 ran in 39 hsecs

run 1 ran in 146.15% of the time

       

Name                                  Run1        Run2        Diff

LATCH.enqueue hash chains              351         464         113

LATCH.session allocation             1,065       1,180         115

STAT...workarea memory allocat         -68          53         121

STAT...redo ordering marks              76         273         197

LATCH.cache buffers lru chain          170         381         211

STAT...commit cleanouts succes         134         347         213

STAT...commit cleanouts                138         351         213

STAT...free buffer requested           167         381         214

LATCH.simulator hash latch             938         721        -217

STAT...cleanout - number of kt          75         322         247

STAT...active txn count during          75         322         247

LATCH.simulator lru latch              908         658        -250

STAT...calls to kcmgas                 124         420         296

STAT...immediate (CURRENT) blo          48         345         297

STAT...consistent gets - exami         123         483         360

LATCH.object queue header oper         326         720         394

STAT...consistent gets from ca       5,964       6,417         453

STAT...consistent gets               5,964       6,417         453

LATCH.undo global data                 232         860         628

STAT...IMU undo allocation siz          52         880         828

STAT...session logical reads        14,041      13,015      -1,026

STAT...db block gets from cach       8,077       6,598      -1,479

STAT...db block gets                 8,077       6,598      -1,479

STAT...redo entries                  5,397       3,905      -1,492

STAT...db block changes             10,604       7,548      -3,056

LATCH.cache buffers chains          46,495      40,265      -6,230

STAT...IMU Redo allocation siz           0      11,072      11,072

STAT...session uga memory               96      65,464      65,368

STAT...session pga memory          196,608      65,536    -131,072

STAT...session uga memory max      385,404           0    -385,404

STAT...session pga memory max      589,824           0    -589,824

STAT...undo change vector size     494,448   1,130,136     635,688

STAT...redo size                 1,981,952   2,993,508   1,011,556

       

Run1 latches total versus runs -- difference and pct

        Run1        Run2        Diff       Pct

      63,861      58,553      -5,308    109.07%

 

从测试结果可以看出，使用IOT插入更快，使用的闩更少。

 

接下来测试查询的效率：

alter session set sql_trace=true;

 

declare

    type array is table of varchar2(100);

    l_array1 array;

    l_array2 array;

    l_array3 array;

begin

for i in 1 .. 10

loop

    for x in (select username from all_users)

    loop

        for y in ( select * from heap single_row

                    where username = x.username )

        loop

            null;

        end loop;

        for y in ( select * from iot single_row

                    where username = x.username )

        loop

            null;

        end loop;

        select * bulk collect

          into l_array1, l_array2, l_array2

          from heap bulk_collect

         where username = x.username;

        select * bulk collect

          into l_array1, l_array2, l_array2

          from iot bulk_collect

         where username = x.username;

    end loop;

end loop;

end;

/

 

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    240      0.00       0.04          0          2          0           0

Fetch      480      0.04       0.06          0      19190          0       24000

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

total      721      0.04       0.11          0      19192          0       24000

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 5     (recursive depth: 1)

 

Rows     Row Source Operation

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

  24000  TABLE ACCESS FULL HEAP (cr=19190 pr=0 pw=0 time=130794 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    240      0.00       0.00          0          2          0           0

Fetch      480      0.03       0.03          0       1672          0       24000

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

total      721      0.03       0.03          0       1674          0       24000

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 5     (recursive depth: 1)

 

Rows     Row Source Operation

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

  24000  INDEX RANGE SCAN IOT_PK (cr=1672 pr=0 pw=0 time=102198 us)(object id 53339)

 

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

 

对于单行获取：Oracle为heap表选择了全表扫描，而对于IOT则使用了索引范围扫描。逻辑I/O的次数相差很多倍，对于heap表，使用了19190个逻辑I/O，而IOT只使用了1672个逻辑I/O。

 

对于批量获取：

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    240      0.00       0.00          0          2          0           0

Fetch      240      0.10       0.11          0      18960          0       24000

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

total      481      0.10       0.12          0      18962          0       24000

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 5     (recursive depth: 1)

 

Rows     Row Source Operation

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

  24000  TABLE ACCESS FULL HEAP (cr=18960 pr=0 pw=0 time=128099 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    240      0.00       0.00          0          2          0           0

Fetch      240      0.07       0.08          0       1540          0       24000

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

total      481      0.07       0.09          0       1542          0       24000

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 5     (recursive depth: 1)

 

Rows     Row Source Operation

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

  24000  INDEX RANGE SCAN IOT_PK (cr=1540 pr=0 pw=0 time=95013 us)(object id 53339)

 

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

 

对于批量获取：heap表用了18960个逻辑I/O，IOT用了1540个了逻辑I/O。

 

这个测试结果显示heap表没有使用索引，我原以为没有使用索引是因为没有对表进行分析导致的，后来经测试，发现不是，即使对表进行了分析，并收集了统计信息：

analyze table heap compute statistics

for table

for all indexes

for all indexed columns;

 

begin

  dbms_stats.gather_table_stats( user, 'heap', cascade=>true );

end;

/

 

Oracle依然会对heap表选择全表扫描 。

 

测试结论：不管是插入，单行查询还是批量获取，IOT都要优于heap表。

 

最后我们看一下，空间使用情况：

analyze table heap compute statistics

for table

for all indexes

for all indexed columns;

 

analyze index HEAP_PK validate structure;

select height,btree_space,used_space,opt_cmpr_count,opt_cmpr_pctsave from index_stats;

 

    HEIGHT BTREE_SPACE USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

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

         2      128032      68713              1               26

 

analyze table iot compute statistics

for table

for all indexes

for all indexed columns;

 

analyze index IOT_PK validate structure;

select height,btree_space,used_space,opt_cmpr_count,opt_cmpr_pctsave from index_stats;

 

    HEIGHT BTREE_SPACE USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

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

         2      864032     545892              1                3

 

IOT因为索引包含数据，所以空间一定比heap的索引空间占用大。

 

select segment_name,sum(bytes) from dba_extents

where segment_name ='HEAP' group by segment_name；

 

SEGMENT_NAME                   SUM(BYTES)

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

HEAP                               655360

 

select segment_name,sum(bytes) from dba_extents

where segment_name ='HEAP_PK' group by segment_name

 

SEGMENT_NAME                   SUM(BYTES)

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

HEAP_PK                            196608

 

select segment_name,sum(bytes) from dba_extents

where segment_name ='IOT_PK' group by segment_name

 

SEGMENT_NAME                   SUM(BYTES)

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

IOT_PK                             917504

 

从结果来看，还是IOT占用空间大一点，不过可能情况没有这么简单，这里只是计算使用的区段的大小总和，但这些区段不一定会被完全使用，因此这样算得的结果不一定准确。

按照《Oracle高效设计》的说法，IOT会节约空间。

 

参考文献：

《Oracle9i&10g编程艺术》

《Oracle高效设计》