11G 新特性之 Deferred Segment Creation 延迟段创建

11G包含的一种新的空间分配方法，当你创建非分区的对表（heap table），表的段会延迟在第一次进行insert的时候才创建，
这个特性默认是启动的，通过一个参数来控制的 DEFERRED_SEGMENT_CREATION  。

 

SQL> col name format a25
SQL> col value format a10
SQL> select name,type,value,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE
from v$parameter where NAME='deferred_segment_creation';

NAME                            TYPE VALUE      ISDEFAULT ISSES ISSYS_MOD
------------------------- ---------- ---------- --------- ----- ---------
deferred_segment_creation          1 TRUE       TRUE      TRUE  IMMEDIATE

SQL>

这个特性的优点如下：
1，大量的磁盘空间可以保存应用程序创建数百或数千个表安装完毕后,其中很多可能永远不会被填充。
2，应用程序安装的时间减少了,因为创建一个表只是一个数据字典操作。

当你插入第一条记录到表中的时候，基于基表的段，LOB段，索引段将会创建。

SQL> create table t2 (a number);

Table created.

SQL> select SEGMENT_NAME from user_segments where SEGMENT_NAME='T2';

no rows selected

SQL> select TABLE_NAME,SEGMENT_CREATED from user_tables where TABLE_NAME='T2';

TABLE_NAME                     SEG
------------------------------ ---
T2                             NO

SQL> insert into t2 values(99);

1 row created.

SQL> select SEGMENT_NAME from user_segments where SEGMENT_NAME='T2';

SEGMENT_NAME
--------------------------------------------------------------------------------
T2

SQL> select TABLE_NAME,SEGMENT_CREATED from user_tables where TABLE_NAME='T2';

TABLE_NAME                     SEG
------------------------------ ---
T2                             YES

SQL>

你也可以查询 SEGMENT_CREATED 列在 USER_TABLES, USER_INDEXES, or USER_LOBS 视图中。
对于非分区表、索引和lob,这列显示是 YES,如果段被创建。注意,当您创建一个表,递延段创建(默认),
新表出现在 *_TABLES  视图,但是没有条目它出现在 *_SEGMENTS 直到你插入第一行。

1，初始化参数 DEFERRED_SEGMENT_CREATION 可以设置 TRUE，FALSE 在session、system 级别
SQL> alter system set deferred_segment_creation=FALSE scope=both;

System altered.

SQL> create table t3 (a number);

Table created.

SQL> select TABLE_NAME,SEGMENT_CREATED from user_tables where TABLE_NAME='T3';

TABLE_NAME                     SEG
------------------------------ ---
T3                             YES

SQL> select SEGMENT_NAME from user_segments where SEGMENT_NAME='T3';

SEGMENT_NAME
--------------------------------------------------------------------------------
T3

SQL>

2，create table 语句可以使用 SEGMENT CREATION 字句

SEGMENT CREATION DEFERRED: If specified, segment creation is deferred until the first row is inserted into the table.
This is the default behavior. for the Oracle Database 11gR2.

SEGMENT CREATION IMMEDIATE: If specified, segments are materialized during table creation.
This is the default behavior. in Oracle databases prior to the Oracle Database 11gR2.

SQL> conn scott/tiger
Connected.
SQL> create table t6 (a number);

Table created.

SQL> select TABLE_NAME,SEGMENT_CREATED from user_tables where TABLE_NAME in ('T5','T6');

TABLE_NAME                     SEG
------------------------------ ---
T6                             NO

SQL> create table t5 (a number) SEGMENT CREATION IMMEDIATE;

Table created.

SQL> select TABLE_NAME,SEGMENT_CREATED from user_tables where TABLE_NAME in ('T5','T6');

TABLE_NAME                     SEG
------------------------------ ---
T5                             YES
T6                             NO

SQL>

限制条件和异常情况

启动延迟段创建, compatibility 参数必须为 '11.2.0' 或者更高.

? 在11.2.0.1 延迟段创建限制为 非分区表和非分区索引。

? IOTs and other special tables like clustered tables, global temporary tables, session-specific temporary tables, internal tables, typed tables, AQ tables, external tables are not supported.
Tables owned by SYS, SYSTEM, PUBLIC, OUTLN, and XDB are also excluded.

? Segment creation on demand is not supported for tables created in dictionary-managed tablespaces
and for clustered tables. If you try creating these tables, segments ARE created.

? Also please note: If you create a table with deferred segment creation on a locally managed tablespace,
then it has no segments. If at a later time, you migrate the tablespace to dictionary-managed,
then any attempt to create segments produces errors. In this case you must drop the table and recreate it.

Note:

In Release 11.2.0.1, deferred segment creation is not supported for partitioned tables.
This restriction is removed in release 11.2.0.2 and later.

这里补充一条异常情况：

当打开延迟段创建时，使用 exp 导出时，未创建段的对象不会被导出的。（本人在11.2.0.3 版本遇到过）。

下面的规则应用到一个表的段尚未创建：

If you specify ALTER TABLE ... ALLOCATE EXTENT before the segment is materialized,
then the segment is materialized and then an extent is allocated.
However the ALLOCATE EXTENT clause in a DDL statement on any indexes of the table will return an error.

During an EXCHANGE of a partition or subpartition with a non-partitioned table without a segment,
segments are materialized for the non-partitioned table automatically before proceeding with the EXCHANGE.

When you issue an ALTER TABLE ... MOVE statement any storage properties you specify override the storage properties specified in the CREATE TABLE statement.

In a DDL statement on the table or its LOB columns or indexes, any specification of DEALLOCATE UNUSED is silently ignored.

ONLINE operations on indexes of a table without a segment will silently be disabled; that is, they will proceed OFFLINE.

Parallel DML operations on tables with no segments are disabled.

关于这句
If you specify ALTER TABLE ... ALLOCATE EXTENT before the segment is materialized,
then the segment is materialized and then an extent is allocated.
However the ALLOCATE EXTENT clause in a DDL statement on any indexes of the table will return an error.

 

在 11.2.0.2.2 版本测试如下：

-----------------------------------

SQL> create index index_t6 on t6 (a);

Index created.

SQL> select TABLE_NAME,SEGMENT_CREATED from user_tables where TABLE_NAME in ('INDEX_T6','T5','T6');

TABLE_NAME                     SEG
------------------------------ ---
T5                             YES
T6                             NO

SQL> select SEGMENT_NAME from user_segments where SEGMENT_NAME in ('INDEX_T6','T5','T6');

SEGMENT_NAME
--------------------------------------------------------------------------------
T5

SQL>
SQL> create index index_t6 on t6 (a);

Index created.

SQL> select TABLE_NAME,SEGMENT_CREATED from user_tables where TABLE_NAME in ('INDEX_T6','T5','T6');

TABLE_NAME                     SEG
------------------------------ ---
T5                             YES
T6                             NO

SQL> select SEGMENT_NAME from user_segments where SEGMENT_NAME in ('INDEX_T6','T5','T6');

SEGMENT_NAME
--------------------------------------------------------------------------------
T5

SQL> select index_name from user_indexes where index_name='INDEX_T6';

INDEX_NAME
------------------------------
INDEX_T6

SQL>
SQL> alter table t6 ALLOCATE EXTENT;

Table altered.

SQL> select SEGMENT_NAME from user_segments where SEGMENT_NAME in ('INDEX_T6','T5','T6');

SEGMENT_NAME
--------------------------------------------------------------------------------
INDEX_T6
T5
T6

SQL>

 

总结：

 

该特性适合大量创建对象的情况开启，可以节省时间，如果你的应用不存在这种情况建议关闭该特性。 关闭只需要设置初始化参数 deferred_segment_creation 为 FALSE。

 

对于开启了延迟段创建的，使用exp 导出延迟的段时注意一下。可能会出现问题的哦

 

 

参考文档：

11.2 Database New Feature Deferred Segment Creation [Video] [ID 887962.1]

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25475140/viewspace-748252/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25475140/viewspace-748252/