Creating Tables Without Segments - Oracle 11g Release 2 New Feature

Understand Deferred Segment Creation

Beginning with Oracle Database 11g Release 2, when you create heap-organized tables in a locally managed tablespace, the database defers table segment creation until the first row is inserted.
In addition, segment creation 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.
In Release, deferred segment creation is not supported for partitioned tables. This restriction is removed in release and later.
The advantages of this space allocation method are the following:
  • It saves a significant amount of disk space in applications that create hundreds or thousands of tables upon installation, many of which might never be populated.
  • It reduces application installation time.
There is a small performance penalty when the first row is inserted, because the new segment must be created at that time.
To enable deferred segment creation, compatibility must be set to '11.2.0' or higher.
The new clauses for the CREATE TABLE statement are:
These clauses override the default setting of the DEFERRED_SEGMENT_CREATION initialization parameter, TRUE, which defers segment creation. To disable deferred segment creation, set this parameter to FALSE.
Note that when you create a table with deferred segment creation, the new table appears in the *_TABLES views, but no entry for it appears in the *_SEGMENTS views until you insert the first row.
You can verify deferred segment creation by viewing the SEGMENT_CREATED column in *_TABLES, *_INDEXES, and *_LOBS views for non-partitioned tables, and in *_TAB_PARTITIONS, *_IND_PARTITIONS, and *_LOB_PARTITIONS views for partitioned tables.

Reference Source - docs.oracle.com 

Most Trending