Index organization table and heap organization table in Oracle

 

 

Create an EMP table:

[SQL] Create Table EMP as select object_id empno, object_name ename, created hiredate, owner job from all_objects affected rows: 53759 time: 4.172 Ms

SQL parsing: query the object_id, object_name, created, and owner fields in the all_object table and rename them to empno, ename, hiredate, and job. The EMP table consists of query results.

Add a primary key for the preceding table:

[SQL] ALTER TABLE EMP add constraint emp_pk primary key (empno) affected rows: 0 time: 0.330 Ms

 

[SQL] Begin dbms_stats.gather_table_stats (user, 'emp', cascade => true); end; affected rows: 1 time: 2.495 Ms

 

 

[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) affected rows: 0 time: 0.047 Ms

 

[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) rows affected by organization index: 0 time: 0.081 Ms

Organization index indicates creating an Iot table

 

 

[SQL] insert into heap_addresses select empno, 'work', '2014 Main Street ', 'Washington', 'DC ', 123 affected rows from EMP: 20123 time: 53759 Ms

 

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;