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.

18 Comments

  1. Bernd Eckenfels's Gravatar Bernd Eckenfels
    September 6, 2009    

    Cool :) Good to know. No more emptylob tablespaces and manual keepin track of them :)

  2. September 8, 2009    

    Cool stuff.

    But it seems weird that Oracle has determined to have this feature enabled by default, which would make many people embarassed without knowing this new feature.

  3. September 9, 2009    

    Just FYI…though the documentation illustrates the use of ALTER TABLE statement for modifying the SEGMENT CREATION clause, but apparently, it doesn’t work. It errors out stating that it’s an INVALID alter table command.

  4. September 9, 2009    

    I would join Chandra, I was trying to ALTER TABLE, but so far no luck.
    I opened a thread in OTN Forum to see if someone alread got it work
    http://forums.oracle.com/forums/thread.jspa?threadID=955170&tstart=0
    Let see.

    An other note about that feature, once you got a segment for a table, it does not look so easy to get
    rid off, I tried many different things by expdp/impdp but everytime the segment is came back, see my test
    http://gasparotto.blogspot.com/2009/09/get-rid-off-segment-of-empty-table.html

    Nicolas.

  5. September 9, 2009    

    I’ve asked Nicolas Gasparotto to test the actual use of the deferred segment allocation and its practical application with an ALTER TABLE MOVE, which is specifically documented as valid in the manual.

    Nicholas was most kind in providing the test bed. This was a follow-up from the discussion we had about it in my blog.

    Unfortunately, it doesn’t work. The software refuses the ALTER TABLE MOVE with a syntax error.

    Ah well: it was a good idea. As usual, the implementation by Oracle let it down…

    Maybe this will work properly in 11gr3?

  6. September 9, 2009    

    Thank you all for providing interesting information about this topic. Honestly, I didn’t thought to test the ALTER TABLE. What I did test however, is the TRUNCATE TABLE. In other words, I wanted to know whether a TRUNCATE removes the segments… It is not the case. What a pity!

    Cheers,
    Chris

  7. September 9, 2009    

    Well, I raise a SR to help on the doc reading about that topic.
    ALTER TABLE MOVE path does not provide SEGMENT CREATION clause :
    move_table_clause -> segment_attributes_clause -> physical_attributes_clause -> storage_clause

    The right path to reach this clause is the following :
    ALTER TABLE > column_clause > add_column_clause > column_properties > nested_table_col_properties > physical_properties > deferred_segment_creation > segment_attributes_clause
    If you read it correctly, that won’t work for standard table and “common”
    ALTER TABLE statement.
    So, no luck on the way we would expected.
    Nicolas.

  8. Stefan's Gravatar Stefan
    September 9, 2009    

    Also note that tables created in sys or system schema will have a segment associated to it.
    So once again: SYS and SYSTEM users are special!

  9. September 10, 2009    

    Nicolas/Christian,

    Looks like the ALTER TABLE command is not expected (or supposed) to change the SEGEMENT CREATION attribute. It might have just found a place in the documentation in error (again, this is just my guess based on the following finding!). Here is what I did:

    1) Created a table with deferred segment creation
    2) Another table without deferring segment creation
    3) Used DBMS_METADATA_DIFF to see the difference between the structures, here is what it says:

    SQL> Select dbms_metadata_diff.compare_alter(‘TABLE’,’APP_DATA’,’APP_DATA_TEMP’, ‘CHANDRA’,’CHANDRA’,null,’BDBE01′) DIFF from dual;

    DIFF
    ——————————————————————————–
    – Cannot ALTER to make segment creation deferred TABLE “CHANDRA”.”APP_DATA”
    ALTER TABLE “CHANDRA”.”APP_DATA” RENAME TO “APP_DATA_TEMP”

    Hope it makes sense.

    Thanks
    Chandra

  10. September 21, 2009    

    How useless … Major points:

    - doesn’t work on partitioned tables (although interval partitioning somewhat offsets this but still)
    - truncate doest not remove

    Why bother implementing it properly … I am sure it’s complicated, but still …

  11. Anthony's Gravatar Anthony
    January 22, 2010    

    Just use:

    alter table allocate extent;

    to allocate segment extents.

  12. January 25, 2010    

    Hi Anthony

    What is the purpose of doing so? Sorry, but I don’t understand… I mean, here the point is to *not* allocate extents…

    Cheers,
    Chris

  13. Paul Muller's Gravatar Paul Muller
    April 19, 2010    

    Users of deprecated tools beware! exp (the conventional data export tool) does not export tables that have not yet had their segment allocated.

  14. Giuseppe Lottini's Gravatar Giuseppe Lottini
    May 24, 2010    

    I had to downgrade from EE 11.2 to SE 11.2, I was unable to impdp the schemas from EE because of “feature not available” regarding the “deferred segment creation” which is default. Now I deleted EE Database and I can’t import schemas on the new (SE) one. I can’t figure how to complete my porting. Any suggestion??

    • May 24, 2010    

      Ciao Giuseppe

      Ufff… really weird situation… I didn’t know that it was an EE edition feature.

      To solve the problem I would try to create the tables before importing the data.
      If you do not have the scripts to create the tables, you should be able to extract the DDL from the DP file with the SQLFILE option.

      HTH
      Chris

  15. October 5, 2010    

    I just wrote a new post that shows how to get rid of empty segments. Refer to Deferred Segment Creation as of 11.2.0.2.

  16. November 23, 2010    

    Hi Avi

    You are getting this error because deferred segment creation is only available in Enterprise Edition (see http://download.oracle.com/docs/cd/E11882_01/license.112/e10594/editions.htm#CJACGHEB).

    But, to answer your question, it would be necessary to know how you get it. In fact, if you ask the question, I guess that you know this restriction and, therefore, it’s not because you specified the “segment creation deferred” option in a CREATE TABLE statement… So, let me know what you are doing.

    Cheers,
    Chris

  17. Avi's Gravatar Avi
    November 23, 2010    

    in case getting error,
    ORA-00439: feature not enabled: Deferred Segment Creation
    what can you do?

  1. Blogroll Report 28/08/2009 – 04/09/2009 « Coskan’s Approach to Oracle on September 9, 2009 at 03:17
  2. Top 10 Oracle 11gR2 New Features | Structured Data on September 9, 2009 at 22:03
  3. Log Buffer #161: a Carnival of the Vanities for DBAs | Pythian Group Blog on September 11, 2009 at 18:42
  4. Entradas de Oracle semanas 35-37 « Gruñidos sobre Oracle y SAP on September 13, 2009 at 12:42
  5. Deferred Segment Creation « Oracle and other on July 1, 2010 at 12:02
  6. Deferred Segment Creation in PeopleSoft « Ukrainian Oracle User Group on June 30, 2011 at 01:37

Leave a Reply

Your email address will not be published. Required fields are marked *