New Features of Oracle 11g R2: Deferred segment generation

When you install an application that contains thousands of tables and indexes, if each table and index is initially allocated a 10 MB initial
Extent. You will soon realize that if each object needs to allocate 10 MB of disk space during creation, it will take a lot of time to deploy the DDL. You may think
Oracle delays the initial extent allocation of each object until the actual insert records in the table.

The only way to delay the generation of the object's initial segment is to use
Oracle Database 11g R2. After Oracle Database 11g R2, by default, the physical
Extent allocation will be delayed until the first record is inserted in the table.

The following is a simple example to illustrate this new feature:

 

First, create a new table (without inserting any records ):

SQL> Create tabletest as select * from EMP where 1 = 2;

Table created.

 

Then, we query user_segments and
User_extents:

 

SQL> selectcount (*) from user_segments where segment_name = 'test ';

Count (*)

----------

0

 

SQL> selectcount (*) from user_extents where segment_name = 'test ';

Count (*)

----------

0

 

Next, we will insert a record to the new test table to verify whether the table is allocated storage space after the record is inserted:

 

 

SQL> insert into test select * from EMP where rownum <2;

1 row created.

 

SQL> selectcount (*) from user_extents where segment_name = 'test ';

Count (*)

----------

1

 

SQL> selectcount (*) from user_segments where segment_name = 'test ';

Count (*)

----------

1

 

The above behavior is very different from the previous Oracle version. In earlier versions of Oracle, only one object is created, and Initialization is assigned to the object.
Segment and the corresponding extent. Deferred segment generation is also applicable to partition tables and partition indexes. The table does not allocate an initial record before it is inserted.
Extent space.

 

After Oracle Database 11g R2
The initial segment of a non-partition heap table created in the tablespace of local managed is delayed until the first record in the table is inserted.

 

Oracle 11g
New deferred segment creation of R2:

 

When a table object is created, no physical storage space is allocated until the table is inserted with the first record. This makes it slower to insert the first record in a table than in the previous Oracle version. In most scenarios, the performance impact is negligible.

 

Set the deferred_segment_creation initialization parameter
False: Disable the deferred segment creation feature. The default value of this parameter is true. We can also specify the following clause (new feature) when creating a table to control the deferredsegment.
Creation

Behavior:

Segment creation immediate and segment creation deferred

 

Example:

 

Create Table f_regs (

Reg_id number

, Regi_namevarchar2 (2000 ))

Segment creationimmediate;

 

Use segmentcreation
Deferred clause. You must set the compatible parameter to 11.2.0.0.0 or later.

For reprinting, please indicate the author's source and original article links; otherwise, you will be held legally responsible:

Author: xiangsir

Link: http://blog.csdn.net/xiangsir/article/details/9063657

QQ: 444367417

MSN: xiangsir@hotmail.com