Database Reference
In-Depth Information
•
Sorted hash clustered tables
: This table type was introduced in Oracle 10
g
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 heap organized tables.
•
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. Temporary tables allow you to temporarily persist data
with the benefit of generating much less redo (and less undo as of Oracle 12
c
) than a regular
heap organized table (see Chapter 9 for a complete discussion on the redo and undo behavior
of temporary tables).
•
Object tables
: These tables are created based on an object type. They have special attributes
not associated with nonobject 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 (with the columns of data in the file
usually demarcated by a delimiter or position). External tables in Oracle9
i
and above give you
the ability to query a file residing outside the database as if it were a normal heap organized
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, as of Oracle 10
g
, introduced an
external table unload capability, they provide an easy way to move data between Oracle databases
without using database links. We will look at external tables in some detail in Chapter 15.
Here is some general information about tables, regardless of their type:
•
A table can have up to 1,000 columns, although I recommend against a design that does
contain the maximum number of columns, unless there is some pressing need. Tables are
most efficient with far fewer than 1,000 columns. Oracle will internally store a row with more
than 254 columns in separate row pieces that point to each other and must be reassembled to
produce the entire row image.
•
A table can have a virtually unlimited number of rows, although you will hit other limits that
prevent this from happening. For example, typically a tablespace can have at most 1,022 files
(although there are
BIGFILE
tablespaces in Oracle 10
g
and above that will get you beyond these
file size limits, too). Say you have a typical tablespace and are using files that are 32GB in size—that
is to say, 32,704GB (1,022 files times 32GB) in total size. This would be 2,143,289,344 blocks,
each of which is 16KB in size. You might be able to fit 160 rows of between 80 to 100 bytes per
block. This would give you 342,926,295,040 rows. If you partition the table, though, you can easily
multiply this number many times. For example, consider a table with 1,024 hash partitions—that
would be 1024 * 342,926,295,040 rows. There are limits, but you'll hit other practical limitations
before even coming close to having three hundred fifty-one trillion, one hundred fifty-six billion,
five hundred twenty-six million, one hundred twenty thousand, nine hundred sixty rows in a table.
