Databases Reference
In-Depth Information
Table 7-1.
(
continued
)
Table Type
Description
Typical Use
Index organized
Data stored in a B-tree (balanced tree) index
structure sorted by primary key
Table is queried mainly on primary key
columns; provides fast random access
Partitioned
A logical table that consists of separate
physical segments
Type used with large tables with millions of
rows
External
Tables that use data stored in OS files outside
the database
Type lets you efficiently access data in a file
outside the database (such as a CSV file)
Clustered
A group of tables that share the same data
blocks
Type used to reduce I/O for tables that are
often joined on the same columns
Hash clustered
A table with data that is stored and retrieved
using a hash function
Reduces the I/O for tables that are mostly static
(not growing after initially loaded)
Nested
A table with a column with a data type that is
another table
Rarely used
Object
A table with a column with a data type that is
an object type
Rarely used
This chapter focuses on the table types that are most often used, in particular heap organized, index organized,
and temporary tables. Partitioned tables are used extensively in data warehouse environments and are covered
separately, in Chapter 12. External tables are covered in Chapter 14. For details on table types not covered in this
book, see the
SQL Language Reference Guide
, which is available for download from the Oracle Technology Network
Understanding Data Types
When creating a table, you must specify the columns names and corresponding data types. As a DBA you should
understand the appropriate use of each data type. I've seen many application issues (performance and accuracy of
data) caused by the wrong choice of data type. For instance, if a character string is used when a date data type should
have been used, this causes needless conversions and headaches when attempting to do date math and reporting.
Compounding the problem, after an incorrect data type is implemented in a production environment, it can be very
difficult to modify data types, as this introduces a change that might possibly break existing code. Once you go wrong,
it's extremely tough to recant and backtrack and choose the right course. It's more likely you will end up with hack
upon hack as you attempt to find ways to force the ill-chosen data type to do the job it was never intended to do.
Having said that, Oracle supports the following groups of data types:
•
Character
•
Numeric
•
Date/Time
RAW
•
ROWID
•
•
LOB
A brief description and usage recommendation are provided in the following sections.
