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;