Overview of Segments【每日一译】--2012-10-24

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, and for each index, Oracle allocates one or

more extents to form its index segment

一个段是多个区块的集合，在一个表空间里它包含了所有的数据在一个指定的逻辑存储

结构。比如，对于任何一个表，ORACLE分配一个或多个区块去组成表的数据段，并且

对于任何一个索引，ORACLE分配一个或多个区块去组成它的索引段。

Introduction to Data Segments
A single data segment in an Oracle database holds all of the data for one of the
following:
■ A table that is not partitioned or clustered
■ A partition of a partitioned table
■ A cluster of tables

介绍数据段

一个单独数据段在ORACLE数据库保留所有的数据对于以下的情况之一：

#一个非分区或聚簇的表

#分区表中的一个分区

#表中的聚簇

Oracle creates this data segment when you create the table or cluster with the CREATE
statement.
The storage parameters for a 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 statement. These storage parameters affect the efficiency of data
retrieval and storage for the data segment associated with the object.

ORACLE创建这些数据段当你创建表或者聚簇表时使用CREATE语句。

你可以直接设置这些参数采用恰当的CREATE或ALTER语句。这些参数影响数据检索有效

性和与此对象的相关的数据段存储。

Note: Oracle creates segments for materialized views and
materialized view logs in the same manner as for tables and clusters.

备注：ORACLE创建物视图和物化视图日志和表和聚簇使用同样的方法。

Introduction to Index Segments
Every nonpartitioned index in an Oracle database has a single index segment to hold
all of its data. For a partitioned index, every partition has a single index segment to
hold its data.

索引段的介绍

每 一个非分区索引在ORACLE数据库中拥有一个单独的索引段去保留所有它的数据。对

于一个分区索引，每个分区拥有一个单独 的索引段去保留它的数据。

Oracle creates the index segment for an index or an index partition when you issue the
CREATE INDEX statement. In this statement, 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.

ORACLE对于索引或者索引分区创建索引段当你输入CREATE INDEX语句。在这个语句，

你可以指定存储参数对于索引段的区块和在里面创建索引段的表空间。（表段和与它相关的

索引不必占据同一个表空间）直接设置存储参数影响数据检索的有效性和存储。

Introduction to 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
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.

临时段的介绍

当处理查询时，ORACLE经常要求临时工作区对于中间阶段的SQL语句的解析和执行。ORACLE

自动分配这些磁盘叫做一个临时段。常见的，ORACLE要求一个临时段作为数据库区的排序。

当一个操作可以在内存中排序或者如果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

操作需要临时段

以下语句有时需要使用临时段

■ 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.

一些非索引的连接和相关联的子查询可能需要一个临时段。比如，一个查询包含DISTINCT

语句，一个GROUP BY ,和一个ORDER BY，ORACLE可能需要多达两个临时段。

Segments in Temporary Tables and Their Indexes
Oracle 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.
How Temporary Segments Are Allocated
Oracle allocates temporary segments differently for queries and temporary tables.

在临时表和索引的段

ORACLE也可以分配临时段对于临时表的创建和在临时表上的索引。临时表保存的数据存在于

一个事务或会话时期。

临时段是如何分配的？

ORACLE分配临时段对于查询和使用临时表的临时段采用不同的算法。

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.

临时段的分配对于查询ORACLE分配临时段在一个用户需要时在用户操作语句的一个临时

表空间中。指定这些表空间使用一个CREATE USER或者一个ALTER USER语句。指定这

些表空间使用一个CREATE USER或者一个ALTER USER语句使用“THE TEMPORARY

TABLESPACE 语句。

Note: You cannot assign a permanent tablespace as a user's
temporary tablespace.

备注：你不能指定一个永久表空间作为用户的临时表空间。

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
drops temporary segments when the statement completes.

如果没有临时表空间被指定给用户，那么默认的临时表空间是SYSTEM表空间。默认的

存储特性包含的表空间决定了临时段的区块。ORACLE在DROP临时段当语句完成时。

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.

因为分配和御载临时段的时有发生，创建到少一个指定表空间的临时段，这样做，你可以

分散IO在使用磁盘设备时，并且你可以避免SYSTEM表空间和其它意外保留临时段的表空间

的碎片产生。

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.

备注：当SYSTEM表空间是本地管理时，你必须批定一个默认的临时表空间，当

你在创建一个数据库时，一个本地管理的SYSTEM表空间不能用于默认

的临时存储。

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

对于使用排序操作在临时段上的条目的改变并不存在于REDO LOG，除非空间管理操作在

对临时表和索引在临时段上的分配。ORACLE分配段对于一个临时表当表被第一次被插

入时。（这个可以是一个内部操作像CREATE TABLE AS SELECT 语句。）对于表和它的索引

分配的段在它第一次被插入到临时表，对于索引创建根页，并且分配任何LOB段。

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

段对于临时表的分配在创建此临时表上的用户所在的临时表空间上。
Oracle 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.

ORACLE DROP一个指定事务的临时表的段在事务结束时和DROP一个指定会话的表在会话

结束时。如果其它事务或者会话共享那个临时表的使用，那个段将包含他们的数据在表中。