Database Reference
In-Depth Information
Chapter 10
Database Tables
In this chapter, we will discuss the various types of database tables and cover when you might want to use each
type (i.e., when one type of table is more appropriate than another). We will concentrate on the physical storage
characteristics of the tables: how the data is organized and stored.
Once upon a time, there was only one type of table, really: a
normal
table. It was managed in the same way a
heap
of stuff
is managed (the definition of which appears in the next section). Over time, Oracle added more sophisticated
types of tables. Now, in addition to the heap organized table, there are clustered tables (three types of those), index
organized tables, nested tables, temporary tables, external tables, and object tables. Each type of table has different
characteristics that make it suitable for use in different application areas.
Types of Tables
We will define each type of table before getting into the details. There are nine major types of tables in Oracle,
as follows:
•
Heap organized tables
: These are normal, standard database tables. Data is managed in a
heap-like fashion. As data is added, the first free space found in the segment that can fit the
data will be used. As data is removed from the table, it allows space to become available for
reuse by subsequent
INSERT
s and
UPDATE
s. This is the origin of the name “heap” as it refers to
this type of table. A
heap
is a bunch of space, and it is used in a somewhat random fashion.
•
Index organized
tables
: These tables are stored in an index structure. This imposes physical
order on the rows themselves. Whereas in a heap the data is stuffed wherever it might fit, in
index-organized tables (IOTs) the data is stored in sorted order, according to the primary key.
•
Index clustered
tables
:
Clusters
are groups of one or more tables, physically stored on the same
database blocks, with all rows that share a common cluster key value being stored physically
near each other. Two goals are achieved in this structure. First, many tables may be stored
physically joined together. Normally, you would expect data from only one table to be found
on a database block, but with clustered tables, data from many tables may be stored on the
same block. Second, all data that contains the same cluster key value, such as
DEPTNO = 10
,
will be physically stored together. The data is clustered around the cluster key value. A cluster
key is built using a B*Tree index. The advantage to index clustered tables is that disk I/O is
reduced and query performance is improved when accessing tables that are frequently joined
on the cluster key.
•
Hash clustered tables
: These tables are similar to index clustered tables, but instead of using
a B*Tree index to locate the data by cluster key, the hash cluster hashes the key to the cluster
to arrive at the database block the data should be on. In a hash cluster, the data is the index
(metaphorically speaking). These tables are appropriate for data that is read frequently via an
equality comparison on the key.
