Database Reference
In-Depth Information
Here I had a table that consisted solely of columns of the primary key. I had
over
100 percent overhead; the size of
my table and primary key index were comparable (actually, the primary key index was larger since it physically stored
the rowid of the row it pointed to, whereas a rowid is not stored in the table—it is inferred). I only used this table with
a
WHERE
clause on the
WORD
or
WORD
and
POSITION
columns. That is, I never used the table—I used only the index on
the table. The table itself was no more than overhead. I wanted to find all documents containing a given word (or
near another word, and so on). The
KEYWORDS
heap table was useless, and it just slowed down the application during
maintenance of the
KEYWORDS
table and doubled the storage requirements. This is a perfect application for an IOT.
Another implementation that begs for an IOT is a code lookup table. Here you might have
ZIP_CODE
to
STATE
lookup, for example. You can now do away with the heap table and just use an IOT itself. Anytime you have a table that
you access via its primary key exclusively, it is a possible candidate for an IOT.
When you want to enforce co-location of data or you want data to be physically stored in a specific order, the
IOT is the structure for you. For users of Sybase and SQL Server, this is where you would have used a clustered index,
but IOTs go one better. A clustered index in those databases may have up to a 110 percent overhead (similar to the
previous
KEYWORDS
table example). Here, we have a 0 percent overhead since the data is stored only once. A classic
example of when you might want this physically co-located data would be in a parent/child relationship. Let's say the
EMP
table had a child table containing addresses. You might have a home address entered into the system when the
employee is initially sent an offer letter for a job. Later, he adds his work address. Over time, he moves and changes
the home address to a previous address and adds a new home address. Then he has a school address he added when
he went back for a degree, and so on. That is, the employee has three or four (or more) detail records, but these details
arrive randomly over time. In a normal heap based table, they just go anywhere. The odds that two or more of the
address records would be on the same database block in the heap table are very near zero. However, when you query
an employee's information, you always pull the address detail records as well. The rows that arrive over time are
always retrieved together. To make the retrieval more efficient, you can use an IOT for the child table to put all of the
records for a given employee near each other upon insertion, so when you retrieve them over and over again, you do
less work.
An example will easily show the effects of using an IOT to physically co-locate the child table information. Let's
create and populate an
EMP
table:
EODA@ORA12CR1> create table emp
2 as
3 select object_id empno,
4 object_name ename,
5 created hiredate,
6 owner job
7 from all_objects
8 /
Table created.
EODA@ORA12CR1> alter table emp add constraint emp_pk primary key(empno);
Table altered.
EODA@ORA12CR1> begin
2 dbms_stats.gather_table_stats( user, 'EMP', cascade=>true );
3 end;
4 /
PL/SQL procedure successfully completed.
