Sunday, October 3, 2010

Oracle 11gR2 Deferred Segment Creation

Oracle Deferred Segment Creation new feature in Oracle Database 11g Release 2.
Creating a non-partitioned heap-organized table in a locally managed tablespace, the table segment creation is deferred until the first row is inserted. In addition, creation of segments is deferred for any LOB columns of the table, any indexes created implicitly as part of table creation, and any indexes subsequently explicitly created on the table.

Advantages of this space allocation method:

  • A significant amount of disk space can be saved for applications that create hundreds or thousands of tables upon installation, many of which might never be populated.
  • The application installation time is reduced.
  • There is a small performance penalty when the first row is inserted, because the new segment must be created at that time.

Important

The new created tables with deferred segment creation (the default), appears in the *_TABLES views, but no entry for it appears in the *_SEGMENTS views until you insert the first row. There is a new SEGMENT_CREATED column in *_TABLES, *_INDEXES, and *_LOBS that can be used to verify deferred segment creation.
With this new allocation method, it is essential that you do proper capacity planning so that the database has enough disk space to handle segment creation when tables are populated.

For more information see: