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 INSERTs and UPDATEs. 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.
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.
Sorted hash clustered
tables:
This table type is new in Oracle 10g
and combines some aspects of a hash-clustered table with those of an IOT.
The concept is as follows: you have some key value that rows will be hashed by
(say, CUSTOMER_ID), and then a series of records related to that key that
arrive in sorted order (timestamp-based records) and are processed in that
sorted order. For example, a customer places orders in your order entry system,
and these orders are retrieved and processed in a first in, first out (FIFO)
manner. In such a system, a sorted hash cluster may be the right data structure
for you.
Nested tables: These are part of
the object-relational extensions to Oracle. They are simply system-generated
and maintained child tables in a parent/child relationship. They work much in
the same way as EMP and DEPT in the SCOTT schema with the EMP table being the
nested table. EMP is considered to be a child of the DEPT table, since the EMP
table has a foreign key, DEPTNO, that points to DEPT. The main difference is
that they are not stand-alone tables like EMP.
Temporary tables: These tables
store scratch data for the life of a transaction or the life of a session.
These tables allocate temporary extents, as needed, from the current
user’s temporary tablespace. Each session will see only the extents that
session allocates; it will never see any of the data created in any other
session.
Object tables: These tables are
created based on an object type. They have special attributes not associated
with non-object tables, such as a system-generated REF (object identifier) for
each row. Object tables are really special cases of heap, index organized, and
temporary tables, and they may include nested tables as part of their structure
as well.
External tables: The data in
these tables are not stored in the database itself; rather, they reside outside
of the database in ordinary operating system files. External tables in
Oracle9i and above give you the ability to query a file residing outside
the database as if it were a normal table inside the database. They are most
useful as a means of getting data into the database (they are a very powerful
data-loading tool). Furthermore, in Oracle 10g, which introduces an
external table unload capability, they provide an easy way to move data between
Oracle databases without using database links.
