Oracle数据库-------测试索引组织表和堆组织表区别

本文链接：https://blog.csdn.net/javatotocat/article/details/123282620

本文通过实验对比了Oracle数据库中的索引组织表（IOT）和堆组织表（Heap）的区别。实验结果显示，索引组织表在读取一致性方面表现出更好的性能，但可能牺牲了插入和更新的速度，更适合查询密集型应用。堆组织表则适用于更频繁的DML操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

实验：测试索引组织表和堆组织表区别

在Oracle数据库中表的分类：以下简单介绍比较两种表的区别

# 堆组织表简称堆表也就是，排放数据无序的表，我们常用的普通表。

# 索引组织表是以索引的方式来存储表中的数据，
所以，索引叶子块中会保存相应行中所有字段的值。
若这些字段的长度累计较大时，每个叶子块可以存储的行数就会很少，
就会需要更多的叶子块来存储行记录，而叶子块越多，索引的深度（或层级）就越高，
每次访问时需要访问的分支块就越多，即一次访问需要访问的总的数据块也就越多。

1.创建表emp

SQL> create table emp 
as select object_id empno,
object_name ename,
created hiredate,
owner job from all_objects;

Table created.

SQL> select count(*) from emp;

  COUNT(*)
----------
     71748

2.创建主键约束

SQL> alter table emp add constraint emp_pk primary key(empno); 

Table altered.

3.启动一个包

SQL> begin
dbms_stats.gather_table_stats(user,'EMP',cascade=>true);
end;
/

PL/SQL procedure successfully completed.

4.创建堆组织表：

SQL> create table heap_addresses(
empno references emp(empno) on delete cascade,
addr_type varchar2(10),
street varchar2(20),
city varchar2(20),
state varchar2(2),
zip number,
primary key(empno,addr_type));

Table created.

5.创建索引组织表

SQL> create table iot_addresses(
empno references emp(empno) on delete cascade,
addr_type varchar2(10),
street varchar2(20),
city varchar2(20),
state varchar2(2),
zip number,
primary key(empno,addr_type)) organization index;

Table created.

6.插入数据到heap表

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

71750 rows created.

7.插入数据到iot表

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

71750 rows created.

8.统计信息

SQL> exec dbms_stats.gather_table_stats(user,'HEAP_ADDRESSES');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'IOT_ADDRESSES');

PL/SQL procedure successfully completed.

9.设置显示： expain+statistics(不显示数据)

SQL> SET autotrace traceonly;

10.查看堆组织表显示的结果

SQL> select * from emp,heap_addresses where emp.empno=heap_addresses.empno and emp.empno=42;

#以下是比较两个结果的读一致性

Execution Plan
----------------------------------------------------------
Plan hash value: 2734189091

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

| Id | Operation       | Name   | Rows | Bytes | Cost (%C
PU)| Time |

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

| 0 | SELECT STATEMENT   |       | 1 | 88 | 4
(0)| 00:00:01 |

| 1 | NESTED LOOPS       |       | 1 | 88 | 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 | TABLE ACCESS BY INDEX ROWID| HEAP_ADDRESSES | 1 | 45 | 2
(0)| 00:00:01 |

|* 5 | INDEX RANGE SCAN   | SYS_C0011215 | 1 | | 1
(0)| 00:00:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("EMP"."EMPNO"=42)
5 - access("HEAP_ADDRESSES"."EMPNO"=42)

Statistics
----------------------------------------------------------
   276 recursive calls
   0 db block gets
   48 consistent gets
   0 physical reads
   0 redo size
1192 bytes sent via SQL*Net to client
   523 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   5 sorts (memory)
   0 sorts (disk)
   1 rows processed

11.查看索引组织表统计的结果

SQL> select * from emp,iot_addresses where emp.empno=iot_addresses.empno and emp.empno=42;

Execution Plan
----------------------------------------------------------
Plan hash value: 1985380736

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

| Id | Operation       | Name       | Rows | Bytes | Cost
(%CPU)| Time   |

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

| 0 | SELECT STATEMENT   |           | 1 | 88 | 3
(0)| 00:00:01 |

| 1 | NESTED LOOPS       |           | 1 | 88 | 3
(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_75090 | 1 | 45 | 1
(0)| 00:00:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("EMP"."EMPNO"=42)
4 - access("IOT_ADDRESSES"."EMPNO"=42)

Statistics
----------------------------------------------------------
   216 recursive calls
   0 db block gets
   37 consistent gets
   0 physical reads
   0 redo size
1192 bytes sent via SQL*Net to client
   523 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   5 sorts (memory)
   0 sorts (disk)
   1 rows processed