6) Temporary segment (describe important features).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 database 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.The following statements sometimes require the use of a temporary segment:CREATE INDEXSELECT ... ORDER BYSELECT DISTINCT ...SELECT ... GROUP BYSELECT . . . UNIONSELECT ... INTERSECTSELECT ... MINUSSome 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.How Temporary Segments Are AllocatedOracle allocates temporary segments differently for queries and temporary tables.Allocation of Temporary Segments for Queries:Oracle allocates temporary segments as needed during a user session in one of the temporary tablespaces of the user issuing the statement. Specify these tablespaces with a CREATE USER or an ALTER USER statement using the TEMPORARY TABLESPACE clause.Allocation of Temporary Segments for Temporary Tables and IndexesOracle allocates segments for a temporary table when the first INSERT into that table is issued. (This can be an internal insert operation issued by CREATETABLE AS SELECT.) The
You've reached the end of your free preview.
Want to read all 13 pages?
- Spring '16
- Oracle Database