Oracle Operations that Require Temporary Segments

The following statements sometimes require the use of a temporary segment:

■ CREATE INDEX

■ SELECT ... ORDER BY

■ SELECT DISTINCT ...

■ SELECT ... GROUP BY

■ SELECT . . . UNION

■ SELECT ... INTERSECT

■ SELECT ... MINUS

Some unindexed joins and correlated subqueries can 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.