Deferred Segment Creation / Segment Creation On Demand

“Deferred Segment Creation” is a new feature introduced in 11g. Where oracle will create tables, but will not allocate the initial storage immediately. Storage is allocated when first row is added in the table.

A small example –

SQL> create table test (c1 char);
Table created.

— Table created


SQL> select table_name, owner from dba_tables 
where table_name='TEST';
TABLE_NAME OWNER
----------- --------
TEST SCOTT

— No entries in dba_segments

SQL> select segment_name, bytes from dba_segments
 where segment_name='TEST';
no rows selected

— A new column segment_created added in dba_tables

SQL> select segment_created from dba_tables 
where table_name='TEST';
SEG
---
NO

— Add a row in table

SQL> insert into test values ('X');
1 row created.
SQL> commit;
Commit complete.

— Now we can see storage allocated for this table

SQL> select segment_name, bytes from dba_segments 
where segment_name='TEST';
SEGMENT_NAME BYTES
------------------------------ ----------
TEST 65536

— Check segment_created from dba_tables

SQL> select segment_created from dba_tables 
where table_name='TEST';
SEG
---
YES

This feature is driven by the parameter “deferred_segment_creation“, which is by default TRUE.

SQL> show parameter deferr
NAME TYPE VALUE
-------------------- ----------- --------
deferred_segment_creation boolean TRUE

We can override this seeting at session / system level

SQL> alter session set deferred_segment_creation=false;
Session altered.
SQL> show parameter deferr
NAME TYPE VALUE
---------------- ----------- ----------
deferred_segment_creation boolean FALSE

— Create another table for test

SQL> create table test (c1 number);
Table created.

— Storage is allocated without adding data (This was the default behaviour prior to 11gR2)

SQL> select segment_created from dba_tables 
where table_name='TEST';
SEG
---
YES

We can also override this setting by specifying “segment creation” clause at table creation time.

 
SEGMENT CREATION IMMEDIATE
— This will allocate storage irrespective of deferred_segment_creation settings

SQL> create table test (c1 number) segment creation immediate;
Table created.

SEGMENT CREATION DEFERRED
— This will not create initial storage, if deferred_segment_creation is set to FALSE

SQL> create table test (c1 number) segment creation deferred;
Table created.
Advertisements
This entry was posted in Oracle 11.2.0.x, Oracle 11g Enhancements and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s