New Features of Oracle 11g R2: Deferred Segment Generation

New Feature of Oracle 11g R2: Deferred Segment Generation when you install an application that contains thousands of tables and indexes, if each table and index are 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 of letting Oracle delay 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 of the table (and its associated indexes) will be delayed until the first record is inserted in the table. Here is a simple example to illustrate this new feature: First, we 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 to verify whether the table is allocated physical storage space: SQL> selectcount (*) from user_segments where segment_name = 'test'; COUNT (*) ---------- 0 SQL> selectcount (*) from user_extents where segment_name = 'test'; COUNT (*) ---------- 0 next we insert a record to the new TEST table, check whether the table is allocated storage space after the record is inserted: SQL> insert into tes T 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, as long as an object is created, the initialization segment and the corresponding extent will be assigned to the object. Deferred segment generation is also applicable to partition tables and partition indexes. The table does not allocate the initial extent space before its initial record is inserted. After Oracle Database 11g R2, the initial segment of a non-partition heap table created in the tablespace of the local management ed will be delayed until the first record in the table is inserted. New deferred segment creation of Oracle 11g R2: no physical storage space is allocated when a table object is created 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 to FALSE to 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, reg_namevarchar2 (2000) segment creationimmediate; note that to use the segmentcreation deferred clause, you must set the COMPATIBLE parameter to 11.2.0.0.0 or later.