Deferred Segment Creation

Starting with Oracle Database 11gR2 when you create an empty heap organized table in locally managed tablespace segment creation is deferred until the time in which the first row is inserted. Segment creation is also deferred on indexes created implicitly when the table is created and indexes explicitly created after the table was created. If the table contains any LOB columns, the segment creation is also deferred.

Deferred Segment creation is default behavior for heap organized tables in locally managed tablespaces. Segment creation is not deferred for partitioned tables, index organized tables, clustered tables, global temporary tables, session specific temporary tables, internal tables, typed tables, AQ tables, partitioned indexes, bitmap join indexes, domain indexes and tables owned by SYS, SYSTEM, PUBLIC, OUTLN or XDB.

The column SEGMENT_CREATED has been added to the *_TABLES, *_INDEXES and *_LOBS that can be used to verify segment creation.

SQL> create table employee (
  2     empid      number(6),
  3     fname      varchar2(20),
  4     lname      varchar2(20),
  5     ssn        varchar2(9),
  6     hire_date  date,
  7     jobid      number(6),
  8     constraint emp_pk primary key(empid)
  9     );

Table created.

SQL> create unique index emp_ssn_idx on employee(ssn); 
                                                      
Index created.                                        
                                                      
SQL> select segment_created from user_tables where table_name = 'EMPLOYEE';

SEG
---
NO

SQL> select index_name, segment_created from user_indexes where table_name = 'EMPLOYEE';

INDEX_NAME                     SEG
------------------------------ ---
EMP_SSN_IDX                    NO
EMP_PK                         NO

SQL>

Above a simple table was created along with indexes to demonstrate the deferred segment feature. Notice how neither the table nor the indexes have segments created. You can further validate by looking in USER_SEGMENTS.

SQL> select bytes, blocks, extents from user_segments where segment_name in ('EMPLOYEE', 'EMP_SSN', 'EMP_PK');

no rows selected

SQL> 

Until an attempt to add a row is made neither object will have a segment so they will not show up in *_SEGMENTS. Notice the phrase “attempt to add a row”. If an insert statement is executed against the table the segment will be created even if the row is rolled back.

SQL> insert into employee values(1234, 'Eric', 'Jenkinson', '123456789', sysdate, 4321);

1 row created.

SQL> select segment_created from user_tables where table_name = 'EMPLOYEE';

SEG
---
YES

SQL> rollback;

Rollback complete.

SQL> select segment_created from user_tables where table_name = 'EMPLOYEE';

SEG
---
YES

SQL> select bytes, blocks from user_segments where segment_name = 'EMPLOYEE';

     BYTES     BLOCKS
---------- ----------
     65536          8

SQL>

You can disable deferred segment creation by setting the parameter DEFERRED_SEGMENT_CREATION to FALSE. This parameter is true by default. If you are upgrading to 11g R2 keep in mind that the database compatibility must be set to 11.2.0 or higher in order to enable deferred segment creation.

Two new clauses, SEGMENT CREATION DEFERRED and SEGMENT CREATION IMMEDIATE have been added to the CREATE TABLE statement. The clauses both override the DEFERRED_SEGMENT_CREATION parameter.

Deferred segment creation can save space for cases where many tables are created to support an application but may never get used depending on the customer’s application usage. Deferred segment creation also speeds up the installation in such cases as the segments are not allocated until the tables contain data.