If a rollback segment is larger than is
optimal (that is, it has too many extents), Oracle automatically deallocates one
or more extents from the rollback segment. See “How Extents Are Deallocated
from a Rollback Segment” on page 2-22 for more information.
Temporary Segments
When Oracle completes the execution of a statement requiring a temporary
segment, Oracle automatically drops the temporary segment and returns the
extents allocated for that segment to the associated tablespace. A single sort allocates its own temporary segment, in the temporary tablespace of the user
issuing the statement, and then returns the extents to the tablespace.
Multiple sorts, however, can use sort segments in a temporary tablespace
designated exclusively for sorts. These sort segments are allocated only once
for the instance, and they are not returned after the sort but remain available
for other multiple sorts. For more information, see “Temporary Segments” on
page 2-15.
Segments
A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. For example, for each table, Oracle
allocates one or more extents to form that table’s data segment; for each index, Oracle allocates one or more extents to form its index segment.
Oracle databases use four types of segments:
•
Data Segments
•
Index Segments
•
Temporary Segments
•
Temporary Segments
The following sections discuss each type of segment.
Data Segments
Every nonclustered table or partition and every cluster in an Oracle database
has a single data segment to hold all of its data. Oracle creates this data
2-14 Oracle8 Server Concepts
segment when you create the nonclustered table or cluster with the CREATE
command.
The storage parameters for a nonclustered table or cluster determine how its
data segment’s extents are allocated. You can set these storage parameters
directly with the appropriate CREATE or ALTER command. These storage
parameters affect the efficiency of data retrieval and storage for the data
segment associated with the object. For more information on the various
CREATE and ALTER commands, see the Oracle8 Server SQL Reference.
Note: Oracle creates segments for snapshots and snapshot logs in the same manner as for nonclustered and clustered tables. For more information on
snapshots and snapshot logs, see Oracle8 Server Replication.
Index Segments
Every index in an Oracle database has a single index segment to hold all of its data. Oracle creates the index segment for the index when you issue the
CREATE INDEX command. In this command, you can specify storage
parameters for the extents of the index segment and a tablespace in which to
create the index segment. (The segments of a table and an index associated
with it do not have to occupy the same tablespace.) Setting the storage
parameters directly affects the efficiency of data retrieval and storage.
Temporary Segments
When processing queries, Oracle often requires temporary workspace for
intermediate stages of SQL statement parsing and execution. Oracle
automatically allocates this disk space called a temporary segment. Typically, Oracle requires a temporary segment as a work area for sorting. Oracle does
not create a segment if the sorting operation can be done in memory or if
Oracle finds some other way to perform the operation using indexes.
Operations Requiring Temporary Segments
The following commands may require the use of a temporary segment:
•
CREATE INDEX
•
SELECT ... ORDER BY
•
SELECT DISTINCT ...
•
SELECT ... GROUP BY
•
SELECT ... UNION
Data Blocks, Extents, and Segments 2-15
•
SELECT ... INTERSECT
•
SELECT ... MINUS
Some unindexed joins and correlated subqueries may also require use of a
temporary segment. For example, if a query contains a DISTINCT clause, a
GROUP BY, and an ORDER BY, Oracle can require as many as two temporary
segments. If applications often issue commands in the list above, the database administrator may want to improve performance by adjusting the
initialization parameter SORT_AREA_SIZE. For more information on
SORT_AREA_SIZE and other initialization parameters, see the Oracle8 Server Reference Manual.
How Temporary Segments Are Allocated
Oracle allocates temporary segments as needed during a user session, in the
temporary tablespace of the user issuing the statement. You specify this
tablespace with a CREATE USER or an ALTER USER command using the
TEMPORARY TABLESPACE option. If no temporary tablespace has been
defined for the user, the default temporary tablespace is the SYSTEM
tablespace. The default storage characteristics of the containing tablespace
determine those of the extents of the temporary segment.
Oracle drops temporary segments when the statement completes.
Because allocation and deallocation of temporary segments occur frequently,
it is reasonable to create a special tablespace for temporary segments. By doing so, you can distribute I/O across disk devices, and you may avoid