Database Reference
In-Depth Information
Next, we'll implement the child table two times, once as a conventional heap table and again as an IOT:
EODA@ORA12CR1> 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@ORA12CR1> 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.
I populated these tables by inserting into them a work address for each employee, then a home address, then
a previous address, and finally a school address. A heap table would tend to place the data at the end of the table;
as the data arrives, the heap table would simply add it to the end, due to the fact that the data is just arriving and no
data is being deleted. Over time, if addresses are deleted, the inserts would become more random throughout the
table. Suffice it to say, the chance an employee's work address would be on the same block as his home address in the
heap table is near zero. For the IOT, however, since the key is on
EMPNO, ADDR_TYPE
, we'll be pretty sure that all of the
addresses for a given
EMPNO
are located on one or maybe two index blocks together. The inserts used to populate this
data were:
EODA@ORA12CR1> insert into heap_addresses
2 select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123
3 from emp;
72075 rows created.
EODA@ORA12CR1> insert into iot_addresses
2 select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123
3 from emp;
72075 rows created.
I did that three more times, changing
WORK
to
HOME
,
PREV
, and
SCHOOL
in turn. Then I gathered statistics:
EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'HEAP_ADDRESSES' );
PL/SQL procedure successfully completed.
