Wednesday, November 27, 2013

How does the 11gR2 feature "deferred segment creation" work?

Starting from 11gR2 onwards, Oracle introduced a feature called deferred segment creation. This means that Oracle will not need to create any segments for a table until you actually insert rows into it.

Oracle creates all needed metadata about the new table in data dictionary, but doesn’t actually allocate any space from the tablespace. This applies to other segment types like index and table/index partitions as well.

Syntax:

-- Without deffered segment creation
CREATE TABLE TEST(tab_id number)
SEGMENT CREATION IMMEDIATE
TABLESPACE USERS;

-- With deffered segment creation
CREATE TABLE TEST (tab_id number)
SEGMENT CREATION DEFERRED
TABLESPACE USERS;


The parameter deferred_segment_creation controls the default behavior:
SQL> show parameter deferred

NAME_COL_PLUS_SHOW_PARAM     TYPE        VALUE
---------------------------- ----------- --------
deferred_segment_creation    boolean     TRUE

No comments:

Post a Comment