iot 堆表 与普通表

普通表与堆表测试对比

drop table heap;
create table heap 
(
  a varchar2(30),
  b varchar2(30),
  c varchar2(30),
  constraint heap_pk primary key (a, b )
);
drop table iot;
create table iot 
(
  a varchar2(30),
  b varchar2(30),
  c varchar2(30),
  constraint iot_pk primary key (a, b )
)
organization index;

create or replace procedure insert_heap
is
begin
  for i in 1 .. 100 loop
    for j in 1 .. 1000 loop
      insert into heap values ( 'a'||i, 'a'||i||j, 'cccc' );
    end loop;
  end loop;
  commit;
end;
/
show errors;
create or replace procedure insert_iot
is
begin
  for i in 1 .. 100 loop
    for j in 1 .. 1000 loop
      insert into iot values ( 'a'||i, 'a'||i||j, 'cccc' );
    end loop;
  end loop;
  commit;
end;
/
create or replace procedure select_heap
is
  l_a heap.a%type;
begin
  for i in 1 .. 100 loop
    l_a := 'a'||i;
    for x in (select * from heap where a=l_a) loop
      null;
    end loop;
  end loop;
end;
/
create or replace procedure select_iot
is
  l_a iot.a%type;
begin
  for i in 1 .. 100 loop
    l_a := 'a'||i;
    for x in (select * from iot where a=l_a) loop
      null;
    end loop;
  end loop;
end;
/
show errors;
begin
  dbms_stats.gather_table_stats( 
    ownname => 'fuyou',
    tabname => 'HEAP' );
  dbms_stats.gather_table_stats( 
    ownname => 'fuyou',
    tabname => 'IOT' );
end;
/
begin
  insert_heap;
  insert_iot;
end;
/

利于tom的runstats脚本测试：

exec runstats_pkg.rs_start
exec select_heap
exec runstats_pkg.rs_middle
exec select_iot
exec runstats_pkg.rs_stop( 50 )

结果 ：

fuyou@ORCL> exec runstats_pkg.rs_start;

PL/SQL 过程已成功完成。

fuyou@ORCL> exec select_heap

PL/SQL 过程已成功完成。

fuyou@ORCL> exec runstats_pkg.rs_middle

PL/SQL 过程已成功完成。

fuyou@ORCL> exec select_iot

PL/SQL 过程已成功完成。

fuyou@ORCL> exec runstats_pkg.rs_stop( 50 )
Run1 ran in 1127 hsecs
Run2 ran in 825 hsecs
run 1 ran in 136.61% of the time

Name                                  Run1        Run2        Diff
LATCH.enqueues                         177          92         -85
LATCH.enqueue hash chains              190          93         -97
LATCH.cache buffers chains             534         380        -154
STAT...Elapsed Time                  1,130         825        -305

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
2,747       2,296        -451    119.64%

PL/SQL 过程已成功完成。

fuyou@ORCL>