Deferred Segment Creation

As you know Oracle Database 11g Release 2 was just released. As a result, it is time to start a series of post about some new features. I’ll start with one that is not really related to performance… In fact, this post is a kind of follow-up to a comment written by Bernd Eckenfels about one of my previous posts: System Managed Extent Size – 11g Improvements. The following is an excerpt of Bernd’s comment:

One thing about uniform size and initial segments I hate is, that an empty segment (lob column) always uses up one extend. This wastes a lot of storage if the col is unused. And as a software vendor you never know when a customer will use the segment.

I agree with Bernd, this might be a real problem when you have a lot of unused segments. Oracle also recognized this issue and, in Oracle Database 11g Release 2, provides a partial (yes, only partial…) solution: deferred segment creation. The idea of deferred segment creation is very simple. The segments related to a table or an index are not immediately created when the CREATE TABLE or CREATE INDEX statement is executed, but only when the first row is inserted into the table. Let’s have a look to an example:

SQL> CREATE TABLE t (n number);

Table created.

SQL> SELECT segment_created
  2  FROM user_tables
  3  WHERE table_name = 'T';

SEGMENT_CREATED
---------------
NO

SQL> SELECT segment_name
  2  FROM user_segments
  3  WHERE segment_name = 'T';

no rows selected

SQL> INSERT INTO t VALUES (1);

1 row created.

SQL> SELECT segment_created
  2  FROM user_tables
  3  WHERE table_name = 'T';

SEGMENT_CREATED
---------------
YES

SQL> SELECT segment_name
  2  FROM user_segments
  3  WHERE segment_name = 'T';

SEGMENT_NAME
------------------------------
T

It is important to point out that all segments related to a table are created when the first row is inserted into it. And that, even if they are not used to store data. For example, the segments for an index or a lob are created even if they are not used. This is the reason why I wrote that this is only a partial solution to the problem mentioned by Bernd. The following example illustrates this for a lob:

SQL> CREATE TABLE t (n number, c clob, b blob);

Table created.

SQL> SELECT column_name, segment_name
  2  FROM user_lobs JOIN user_segments USING (segment_name)
  3  WHERE table_name = 'T';

no rows selected

SQL> INSERT INTO t (n) VALUES (1);

1 row created.

SQL> SELECT column_name, segment_name
  2  FROM user_lobs JOIN user_segments USING (segment_name)
  3  WHERE table_name = 'T';

COLUMN_NAME  SEGMENT_NAME
------------ ------------------------------
C            SYS_LOB0000073904C00002$$
B            SYS_LOB0000073904C00003$$

In addition, the feature doesn’t work in all situations (yet). In other words, there are some restrictions. The most important, in my opinion, is that only non-partitioned table can take advantage of it. A full list of restrictions is obviously available in the documentation.

Note that deferred segment creation is enabled by default. To enable/disable it at the session or system level, you can change the initialization parameter DEFERRED_SEGMENT_CREATION. In addition, it’s also possible to enable/disable it for a single table by specifying the deferred segment creation clause.