This article discusses Deferred Segment Creation, a space saving feature of Oracle Database 11gR2, which is also known as segment creation on demand.

Problem
When we create a table, Oracle will immediately create related segments, like table segment, implicit index segment and LOB segment. And if there are lots of empty tables in the database, then they will occupy disk space before they are even used.

Solution
To handle this issue Oracle introduces Deferred Segment Creation feature using SEGMENT CREATION { IMMEDIATE | DEFERRED } clause. If you use SEGMENT CREATION IMMEDIATE clause with CREATE TABLE statement then all associated segments will be created immediately, but if you use SEGMENT CREATION DEFERRED clause with CREATE TABLE statement then all associated segments will be created only when rows are inserted in the table. So empty tables will not occupy any disk space. To use this feature you need to set DEFERRED_SEGMENT_CREATION initialization parameter, which is TRUE by default.

Example
We will turn off this parameter and create a table using regular create table statement.

SQL> ALTER SESSION SET DEFERRED_SEGMENT_CREATION = FALSE;

Session altered.

SQL> CREATE TABLE deferred_test (
  2     anumber NUMBER,
  3     adate   DATE,
  4     aclob   CLOB,
  5     CONSTRAINT deferred_test_pk PRIMARY KEY (anumber)
  6  ) LOB(aclob) STORE AS SECUREFILE deferred_test_aclob tablespace USERS;

Table created.

SQL> COLUMN SEGMENT_NAME FORMAT A30

SQL> SELECT segment_name, segment_type, bytes
  2    FROM user_segments;

SEGMENT_NAME                   SEGMENT_TYPE            BYTES
------------------------------ ------------------ ----------
DEFERRED_TEST                  TABLE                   65536
SYS_IL0000074605C00003$$       LOBINDEX                65536
DEFERRED_TEST_PK               INDEX                   65536
DEFERRED_TEST_ACLOB            LOBSEGMENT             131072

4 rows selected.

You can note that 4 segments are created and they are also wasting some bytes. Now lets drop this table and set DEFERRED_SEGMENT_CREATION to TRUE to use this feature.

SQL> ALTER SESSION SET DEFERRED_SEGMENT_CREATION = TRUE;

Session altered.

SQL> DROP TABLE deferred_test PURGE;

Table dropped.

SQL> CREATE TABLE deferred_test (
  2     anumber NUMBER,
  3     adate   DATE,
  4     aclob   CLOB,
  5     CONSTRAINT deferred_test_pk PRIMARY KEY (anumber)
  6  ) LOB(aclob) STORE AS SECUREFILE deferred_test_aclob tablespace USERS;

Table created.

SQL> SELECT segment_name, segment_type, bytes
  2    FROM user_segments;

no rows selected.

Lets insert a row in this table.

SQL> INSERT INTO deferred_test VALUES (1, sysdate, 'a clob');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT segment_name, segment_type, bytes
  2    FROM user_segments;

SEGMENT_NAME                   SEGMENT_TYPE            BYTES
------------------------------ ------------------ ----------
DEFERRED_TEST                  TABLE                   65536
SYS_IL0000074609C00003$$       LOBINDEX                65536
DEFERRED_TEST_PK               INDEX                   65536
DEFERRED_TEST_ACLOB            LOBSEGMENT             131072

4 rows selected.

SQL> DROP TABLE deferred_test PURGE;

Table dropped.

We can also use SEGMENT CREATION { IMMEDIATE | DEFERRED } clause at table level to satisfy our need at table level without disturbing DEFERRED_SEGMENT_CREATION parameter. Now we will set DEFERRED_SEGMENT_CREATION to TRUE and create 2 tables, one with each option to show the difference.

SQL> ALTER SESSION SET DEFERRED_SEGMENT_CREATION=TRUE;

Session altered.

SQL> CREATE TABLE immediate_segment_test (
  2     anumber NUMBER,
  3     adate   DATE,
  4     aclob   CLOB,
  5     CONSTRAINT immediate_segment_test_pk PRIMARY KEY (anumber)
  6  ) SEGMENT CREATION IMMEDIATE
  7    LOB(aclob) STORE AS SECUREFILE immediate_segment_test_aclob 
  8    TABLESPACE USERS;

Table created.

SQL> CREATE TABLE deferred_segment_test (
  2     anumber NUMBER,
  3     adate   DATE,
  4     aclob   CLOB,
  5     CONSTRAINT deferred_segment_test_pk PRIMARY KEY (anumber)
  6  ) SEGMENT CREATION DEFERRED
  7    LOB(aclob) STORE AS SECUREFILE deferred_segment_test_aclob 
  8    TABLESPACE USERS;

Table created.

SQL> SELECT segment_name, segment_type, bytes
  2  FROM   user_segments;

SEGMENT_NAME                   SEGMENT_TYPE            BYTES
------------------------------ ------------------ ----------
IMMEDIATE_SEGMENT_TEST         TABLE                   65536
SYS_IL0000074613C00003$$       LOBINDEX                65536
IMMEDIATE_SEGMENT_TEST_PK      INDEX                   65536
IMMEDIATE_SEGMENT_TEST_ACLOB   LOBSEGMENT             131072

4 rows selected.

You can see that segments immediate_segment_test are created where as segments for deferred_segment_test are not created at this point.

Link to script that contains the examples in this post.

Comment

2012-12 – Deferred Segment Creation

HEADQUARTERS