Database Reference
In-Depth Information
the IOT has to move to another block, the guess in the secondary index becomes stale. Therefore, an index on an IOT
is slightly less efficient than an index on a regular heap organized table. On a regular table, an index access typically
requires the I/O to scan the index structure and then a single read to read the table data. With an IOT, typically two
scans are performed: one on the secondary structure and the other on the IOT itself. That aside, indexes on IOTs
provide fast and efficient access to the data in the IOT using columns other than the primary key.
Index Organized Tables Wrap-up
Getting the right mix of data on the index block versus data in the overflow segment is the most critical part of the IOT
setup. Benchmark various scenarios with different overflow conditions, and see how they will affect your
INSERT
s,
UPDATE
s,
DELETE
s, and
SELECT
s. If you have a structure that is built once and read frequently, stuff as much of the
data onto the index block as you can. If you frequently modify the structure, you will have to achieve some balance
between having all of the data on the index block (great for retrieval) versus reorganizing data in the index frequently
(bad for modifications). The
FREELIST
consideration you have for heap tables applies to IOTs as well.
PCTFREE
and
PCTUSED
play two roles in an IOT.
PCTFREE
is not nearly as important for an IOT as for a heap table, and
PCTUSED
doesn't come into play normally. When considering an
OVERFLOW
segment, however,
PCTFREE
and
PCTUSED
have the
same interpretation as they do for a heap table; set them for an overflow segment using the same logic as you would
for a heap table.
Index Clustered Tables
I generally find people's understanding of what a cluster is in Oracle to be inaccurate. Many people tend to confuse
a cluster with a SQL Server or Sybase “clustered index.” They are not the same. A cluster is a way to store a group of
tables that share some common column(s) in the same database blocks and to store related data together on the same
block. A clustered index in SQL Server forces the rows to be stored in sorted order according to the index key, similar
to an IOT as just described. With a cluster, a single block of data may contain data from many tables. Conceptually,
you are storing the data “prejoined.” It can also be used with single tables where you are storing data together grouped
by some column. For example, all of the employees in department
10
will be stored on the same block (or as few
blocks as possible, if they all don't fit). It is not storing the data sorted—that is the role of the IOT. It is storing the
data clustered by some key, but in a heap. So, department
100
might be right next to department
1
, and very far away
(physically on disk) from departments
101
and
99
.
Graphically, you might think of it as shown in Figure
10-8
. On the left side of the image, we are using conventional
tables.
EMP
will be stored in its segment.
DEPT
will be stored on its own. They may be in different files and different
tablespaces, and they are definitely in separate extents. On the right side of the image, we see what would happen
if we clustered these two tables together. The square boxes represent database blocks. We now have the value
10
factored out and stored once. Then, all of the data from all of the tables in the cluster for department
10
is stored in
that block. If all of the data for department
10
does not fit on the block, then additional blocks will be chained to the
original block to contain the overflow, in the same fashion as the overflow blocks for an IOT.
