oracle体系结构（六）

Overview of Segments段概述

A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. Forexample, 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 creates this data segment when youcreate the table or cluster with the CREATE statement.

在oracle中，一个数据段为下面之一情况保留所有的数据：

■非分区表和非簇表

■分区表的一个分区

■一个簇表

Oracle在你使用create语句创建表或者簇的时候创建这个数据段。 

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.

表或者簇中的存储参数决定了数据段中区的分配原则。用户可以适当的直接通过create或者alter语句设置这些存储参数。这些参数将会影响与对象（object）相关的数据段的存储与访问效率。 

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.

当用户使用create index语句创建一个索引或索引分区的时候，oracle会为这个索引创建索引段。在这条语句中，用户可以为这个索引段的区指定存储参数，并且还可以指定这个索引段所存储的表空间。（一个表的段和一个索引的段没有必要放在同一个表空间中。）存储参数的设置会直接影响存储和数据的访问效率。 

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自动分配的用于sql语句解析和执行的这个磁盘空间被称作是临时段。Oracle要求有一个临时段的数据空间用来做排序使用，这个是非常典型的。如果排序操作可以在内存中执行，或者如果oracle找到其他一些诸如使用索引等的方法执行这个操作，那么oracle就不会创建临时段了。 

Operations that Require Temporary Segments需要临时段的操作

The following statements sometimes requirethe 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 or related subqueries can require use of a temporary segment. For example, if a query contains a DISTINCT clause, a GROUP BY, 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.

默写非索引连接和子句关联执行时要求使用临时段。例如，如果一个查询语句中包涵了一个DISTNCT句子、一个a GROUP BY子句或者一个ORDER BY子句，那么oracle就需要多达两个临时段。 

Segments in Temporary Tables and Their Indexes临时表中及其索引使用的段

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

Oracle也为临时表及其创建的索引分配临时段。临时表中的数据只有在一个事务执行或会话访问的时候存在。 

How Temporary Segments Are Allocated临时段的分配原则

Oracle allocates temporary segments differently for queries and temporary tables.

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语句中增加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.

由于分配和重分配临时段的频繁发生，创建至少一个存放临时段的专用的表空间就显得非常必要了。通过创建临时表空间，用户可以分担I/O和磁盘之间的存储压力，并且也可以避免SYSTEM表空间和其他表空间因保留临时段的原因而出现大量空间碎片的情况。 

Note: When the SYSTEM tablespace is locally managed, you must define a default temporary tablespace when creating adatabase. 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.

当表创建后被插入第一条数据时，oracle会为临时表分配一个临时段。（如果通过CREATE TABLE AS SELECT创建表时，oracle为临时表分配临时段就是隐式操作的。）当首个INSERT执行时，Oracle为临时表及其索引分配临时段，创建索引的root page，同时创建所有的LOB段。

 

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

临时表的段被分配在用户创建临时表是指定的临时表空间中。

Oracle drops segments for atransaction-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）用于事务处理的临时表中的临时段。同样的，在会话结束时，oracle会删除（drop）用于会话处理的临时表中的临时段。如果这个时候有其他的事务或者会话在共享当前的这个临时表，那么被用于这些其他会话或事务的临时段的数据将会仍然保留在临时表中。