Database Reference
In-Depth Information
•
A table can have as many indexes as there are permutations of columns (and permutations
of functions on those columns and permutations of any unique expression you can dream
of ). With the advent of function-based indexes, the true number of indexes you could create
theoretically becomes infinite! Once again, however, practical restrictions, such as overall
performance (every index you add will add overhead to an
INSERT
into that table) will limit the
actual number of indexes you will create and maintain.
•
There is no limit to the number of tables you may have, even within a single database. Yet
again, practical limits will keep this number within reasonable bounds. You will not have
millions of tables (as this many is impractical to create and manage), but you may have
thousands of tables.
In the next section, we'll look at some of the parameters and terminology relevant to tables. After that, we'll jump
into a discussion of the basic heap-organized table and then move on to examine the other types.
Terminology
In this section, we will cover the various storage parameters and terminology associated with tables. Not all
parameters are used for every table type. For example, the
PCTUSED
parameter is not meaningful in the context of
an IOT (the reason for this will become obvious in Chapter 11). We'll cover the relevant parameters as part of the
discussion of each individual table type. The goal is to introduce the terms and define them. As appropriate, more
information on using specific parameters is covered in subsequent sections.
Segment
A
segment
in Oracle is an object that consumes storage on disk. While there are many segment types, the most popular
are as follows:
•
Cluster
: This segment type is capable of storing tables. There are two types of clusters: B*Tree
and hash. Clusters are commonly used to store related data from multiple tables prejoined
on the same database block and to store related information from a single table together.
The name “cluster” refers to this segment's ability to cluster related information physically
together.
•
Table
: A table segment holds data for a database table and is perhaps the most common
segment type used in conjunction with an index segment.
•
Table partition or subpartition
: This segment type is used in partitioning and is very similar to
a table segment. A table partition or subpartition segment holds just a slice of the data from a
table. A partitioned table is made up of one or more table partition segments, and a composite
partitioned table is made up of one or more table subpartition segments.
•
Index
: This segment type holds an index structure.
•
Index partition
: Similar to a table partition, this segment type contains some slice of an index.
A partitioned index consists of one or more index partition segments.
•
Lob partition, lob subpartition, lobindex, and lobsegment
: The lobindex and lobsegment
segments hold the structure of a
large object
, or
LOB
. When a table containing a LOB is
partitioned, the lobsegment will be partitioned as well—the lob partition segment is used for
that. It is interesting to note that there is not a lobindex partition segment type—for whatever
reason, Oracle marks the partitioned lob index as an index partition (one wonders why a
lobindex is given a special name). LOBs are discussed in full detail in Chapter 12.
