Oracle7 Server Concepts

Temporary Segments

When processing queries, Oracle often requires temporary workspace for intermediate stages of SQL statement processing. 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

SELECT ... INTERSECT

SELECT ... MINUS

unindexed joins

certain correlated subqueries

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 statements 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 Oracle7 Server Reference.

How Temporary Segments Are Allocated

Oracle allocates temporary segments as needed during a user session. For example, a user might issue a query that requires three temporary segments. Oracle drops temporary segments when the statement completes. The default storage characteristics of the containing tablespace determine those of the extents of the temporary segment.

Oracle creates temporary segments 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. Otherwise, the default temporary tablespace is the SYSTEM tablespace. For more information about assigning a user's temporary segment tablespace, see Chapter 17, "Database Access".

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 fragmentation of the SYSTEM and other tablespaces that otherwise would hold temporary segments.

The redo log does not contain entries for changes to temporary segments used for sort operations.