V$TEMPSEG_USAGE 中SEGTYPE的理解

本文主要是介绍V$TEMPSEG_USAGE 中SEGTYPE的理解，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

今天用到视图V$TEMPSEG_USAGE，看到有好多对象类型，发现有如下几种临时sort segment type:

SEGTYPE

VARCHAR2(9)

Type of sort segment:

SORT 排序
HASH hash join（unindexed joins）
DATA temporary tables
INDEX temporary indexs
LOB_DATA
LOB_INDEX

去官方文档查询segment 创建：

https://docs.oracle.com/cd/B28359_01/server.111/b28318/logical.htm#BEGIN

2 Data Blocks, Extents, and 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 Database can require as many as two temporary segments.

Segments in Temporary Tables and Their Indexes

Oracle Database can also allocate temporary segments for temporary tables and indexes created on temporary tables. Temporary tables hold data that exists only for the duration of a transaction or session.

Allocation of Temporary Segments for Queries

Oracle Database 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.

If no temporary tablespace is defined for the user, then 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 Database drops temporary segments when the statement completes.

Because allocation and deallocation of temporary segments occur frequently, create at least one special tablespace for temporary segments. By doing so, you can distribute I/O across disk devices, and you can avoid fragmentation of the SYSTEM and other tablespaces that otherwise hold temporary segments.

Note:

When the SYSTEM tablespace is locally managed, you must define a default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.

Entries for changes to temporary segments used for sort operations are not stored in the redo log, except for space management operations on the temporary segment.

Allocation of Temporary Segments for Temporary Tables and Indexes

Oracle Database allocates segments for a temporary table when the first INSERT into that table is issued. (This can be an internal insert operation issued by CREATE TABLE AS SELECT.) The first INSERT into a temporary table allocates the segments for the table and its indexes, creates the root page for the indexes, and allocates any LOB segments.

Segments for a temporary table are allocated in a temporary tablespace of the user who created the temporary table.

Oracle Database drops segments for a transaction-specific temporary table at the end of the transaction and drops segments for a session-specific temporary table at the end of the session. If other transactions or sessions share the use of that temporary table, the segments containing their data remain in the table.

V$TEMPSEG_USAGE其实是v$sort_usage的同义词：