观察索引组织表（IOT）和传统堆表性能差异

最新推荐文章于 2021-04-12 11:46:01 发布

blockheading

最新推荐文章于 2021-04-12 11:46:01 发布

阅读量542

点赞数

分类专栏： Oracle Basic Principle 文章标签：索引 IOT 堆表

本文链接：https://blog.csdn.net/u013169075/article/details/52917251

版权

本文探讨了索引组织表（IOT）与传统堆表在数据存储和查询性能上的差异，分析了不同场景下选择IOT或堆表的优势，并提供了实际案例来展示其性能表现。

摘要由CSDN通过智能技术生成

--配置实验环境
EODA@PROD1> 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.

EODA@PROD1> 
EODA@PROD1> alter table emp add constraint emp_pk primary key(empno);

Table altered.

EODA@PROD1> 
EODA@PROD1> begin
  2    dbms_stats.gather_table_stats( user, 'EMP', cascade=>true );
  3  end;
  4  /

PL/SQL procedure successfully completed.

EODA@PROD1> 
EODA@PROD1> 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.

EODA@PROD1> 
EODA@PROD1> 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.

EODA@PROD1> insert into heap_addresses select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123 from emp;

72830 rows created.

EODA@PROD1> insert into iot_addresses select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123 from emp;

72830 rows created.

EODA@PROD1> insert into heap_addresses select empno, 'HOME', '123 main street', 'Washington', 'DC', 20123 from emp;

72830 rows created.

EODA@PROD1> insert into iot_addresses select empno, 'HOME', '123 main street', 'Washington', 'DC', 20123   from emp;

72830 rows created.

EODA@PROD1> insert into heap_addresses select empno, 'PREV', '123 main street', 'Washington', 'DC', 20123 from emp;

72830 rows created.

EODA@PROD1> insert into iot_addresses select empno, 'PREV', '123 main street', 'Washington', 'DC', 20123 from emp;

72830 rows created.

EODA@PROD1> insert into heap_addresses select empno, 'SCHOOL', '123 main s