oracle中的索引组织表与堆组织表

 

 

创建一个emp表：

[SQL] create table emp 
    as 
    select object_id   empno, 
           object_name ename, 
           created     hiredate, 
           owner       job 
      from all_objects 
    
受影响的行: 53759
时间: 4.172ms

sql解析：查询all_object表中的object_id,object_name,created和owner字段，并将其重命名为empno,ename,hiredate和job，有查询结果组成emp表。

为上述表添加主键：

[SQL] alter table emp add constraint emp_pk primary key(empno)
    
受影响的行: 0
时间: 0.330ms

 

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

受影响的行: 1
时间: 2.495ms

 

 

[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) 
    ) 
   
受影响的行: 0
时间: 0.047ms

 

[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 
   
受影响的行: 0
时间: 0.081ms

ORGANIZATION INDEX表示创建IOT表

 

 

[SQL] insert into heap_addresses select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123 from emp
受影响的行: 53759
时间: 0.633ms

 

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

 

begin 
dbms_stats.gather_table_stats(user,'HEAP_ADDRESSES');
end;

begin 
dbms_stats.gather_table_stats(user,'IOT_ADDRESSES');
end;