On Demand Segment creation (deferred segment creation)

 Applies to: Database Management Software Oracle Server - Enterprise Edition - Version: 11.0.1 to 11.2.0.2

Consider an environment, In which you are installing a new schema with thousands of tables, each table creation will take time and space because initial extant are allocated at time of table creations. It might be possible that, few tables out of these will never be populated with data which cause a waste of space.

To overcome these two challenges time and space, Oracle 11g introduce DEFERRED_SEGMENT_CREATION' parameter with default value 'TRUE'. This setting will delay segment creation till first row of table is inserted. When you insert the first row in table segments are created on the base table, LOB columns and it's indexes.

How it works:

In Database Management Software Oracle 11g, this parameter is true be default. Let's make a table to see it's functionality.

SQL> show parameter segment

NAME                                            TYPE        VALUE
------------------------------------        ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
rollback_segments                          string
transactions_per_rollback_segment    integer     5

SQL> CREATE TABLE tab1 (id NUMBER,  NAME VARCHAR2(50),  CONSTRAINT tab1_pk PRIMARY KEY (id));

Table created.

SQL> COLUMN segment_name FORMAT A30

SQL> SELECT segment_name, segment_type FROM user_segments where segment_name like 'TAB1'; 

no rows selected

As shown from above query no segment is allocated at time of table creation.

SQL> INSERT INTO tab1 VALUES (1, ' data for varchar2');

1 row created.

SQL> commit;

Commit complete.

Below result shows segment is allocated at time of first row insertion into table.

SQL> SELECT segment_name,segment_type FROM user_segments where segment_name like 'TAB1';  

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------
TAB1                           TABLE


Changing DEFERRED_SEGMENT_CREATION Behavior:

There are four ways to change DEFERRED_SEGMENT_CREATION

1. initialization file (not recommended).
2. Alter session command.
3. Alter system command.
4. Segment creation clause in table creation script (immediate, deferred)


2. Change behavior for a session only: deferred_segment_creation is true and we will change this for current session only.

SQL> alter session set deferred_segment_creation=false;

Session altered.

SQL> show parameter deferred_segment_creation;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     FALSE

SQL> CREATE TABLE tab2 (  id NUMBER, NAME VARCHAR2(50),  CONSTRAINT tab1_pk2 PRIMARY KEY (id))  ;

Table created.

SQL> SELECT segment_name,segment_type FROM user_segments where segment_name like 'TAB2'; 

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------
TAB2                           TABLE

Since, Parameter is false segment is created at table creation time.

3. Change parameter for whole system: Use below steps to change behavior for whole system by change in spfile.

SQL> show parameter deferred_segment_creation

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

SQL> alter system set deferred_segment_creation=false;

System altered.

SQL> show parameter deferred_segment_creation

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     FALSE

4. Segment creation clause in table creation script: Irrespective of parameter at system level, you can also control segment creation behavior at table creation script level.

segment creation immediate:

SQL> show parameter deferred

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

"segment creation immediate" clause in create table script will create segments at table creation time immediately.

SQL> CREATE TABLE tab3 ( id NUMBER, NAME VARCHAR2(50),  CONSTRAINT tab1_pk3 PRIMARY KEY (id)) segment creation immediate ;

Table created.

SQL> COLUMN segment_name FORMAT A30;

SQL> SELECT segment_name,segment_type FROM user_segments where segment_name like 'TAB3';

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------
TAB3                           TABLE

segment creation deferred:

SQL> show parameter deferred_segment_creation

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     FALSE

"segment creation deferred" clause in create table script will defer segments till first row insertion.

SQL> CREATE TABLE tab4 ( id NUMBER, NAME VARCHAR2(50),  CONSTRAINT tab1_pk4 PRIMARY KEY (id)) segment creation deferred ;

Table created.

SQL> SELECT segment_name,segment_type FROM user_segments where segment_name like 'TAB4';

no rows selected

Limitation of On Demand Segment creation (deferred segment creation)


1. Non Partitioned tables, non partitioned indexes, special tables and IOTs are not supported by On Demand Segment creation.
2. Dictionary managed tablespace do not support on Demand Segment Creation.
3. Segment creation on demand is not supported for clustered tables, global/session specific temp tables,internal tables, typed tables, external tables, bitmap join indexes, table owned by SYSTEM, PUBLIC, OUTLN and XDB.

No comments:

Post a Comment