Liron Amitzi Oracle ACE

Oded Raz Oracle ACE Director

Oracle 11gR2 New Feature – Deferred Segment Creation

Posted by Roni Vered on Dec 6th, 2010 and filed under Administration, Oracle, Oracle - Latest Articles, Videos. You can follow any responses to this entry through the RSS 2.0. You can leave a response or trackback to this entry

In this DBSNAPS tutorial video we will present you a new feature of Oracle database 11gR2: Deferred segment creation.

The script for the demonstration from the tutorial can be downloaded here:seg_demo

Until 11gR2, if you create a table, Oracle database allocates space for initial segment.

The new feature allows you to defer creation of this initial segment until the first row of data is inserted into the table.

In addition to deferring the table’s segment, it also defers the segment creation for LOB columns of the table, indexes created implicitly as part of table creation, and indexes subsequently explicitly created on the table.

By using this feature, large amount of disk space can be saved in case of many unpopulated tables.

A new parameter DEFERRED_SEGMENT_CREATION is created to control the feature. It can be enabled/disabled it at the session or system level (doesn’t require Instance restart).

Alter session set deferred_segment_creation=true;

Alter system set deferred_segment_creation=true;
  

Deferred segment creation is enabled by default. It’s also possible to enable/disable it for a single table by specifying the deferred segment creation clause.

CREATE TABLE.... SEGMENT CREATION IMMEDIATE

CREATE TABLE... SEGMENT CREATION DEFERRED
 

* A table with deferred segment won’t exist in the *_EXTENTS System view before inserting into it data (as no extent is created)
* A new column was added to the *_TABLES,  *_INDEXES and *_LOBS System views – SEGMENT_CREATED, whichindicates whether the initial segment was created or not. Its values are ‘YES’ / ’NO’.

One important note – 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

Following is a short demo that demonstrates the new features.

SQL> @c:\seg_demo
====================================================================
Creating a user for the demonstration and granting it privileges:
====================================================================

SQL> CREATE USER seg_demo identified by seg_demo
User created.
SQL> GRANT RESOURCE , CONNECT TO seg_demo
Grant succeeded.
SQL> GRANT SELECT ON V_$PARAMETER TO seg_demo
Grant succeeded.
SQL> CONNECT seg_demo/seg_demo
Connected.

====================================================================
Displaying relevant parameters
====================================================================

SQL> SHOW PARAMETER def
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL> SHOW PARAMETER compatible
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.0.0

=========================================================================================
Creating a table. The table will be created without segments for all its related objects
=========================================================================================

SQL> CREATE TABLE test_seg (id number CONSTRAINT id_pk primary key,
2				email VARCHAR2(50)  CONSTRAINT  email_uk unique,
3				name VARCHAR2(30),
4				data CLOB)
5			lob(data) STORE AS TEST_SEG_DATA_LOB
6			(index test_seg_data_lob_ix)
Table created.

=========================================================================================
Displaying relevant information FROM the System views.
=========================================================================================

SQL> SELECT segment_name ,SEGMENT_TYPE
2	FROM user_segments
3 	WHERE segment_name = 'TEST_SEG'

no rows selected

SQL> SELECT segment_name, extent_id, bytes
2 	FROM user_extents

no rows selected

SQL> SELECT table_name, SEGMENT_CREATED
2 	FROM user_tables

TABLE_NAME                     SEGMENT_CREATED
------------------------------ ---------------
TEST_SEG                       NO

SQL> SELECT table_name, index_name, SEGMENT_CREATED
2 	FROM user_indexes

TABLE_NAME                     INDEX_NAME                     SEGMENT_CREATED
------------------------------ ------------------------------ ---------------
TEST_SEG                       ID_PK                          NO
TEST_SEG                       TEST_SEG_DATA_LOB_IX           NO
TEST_SEG                       EMAIL_UK                       NO

SQL> SELECT  TABLE_NAME ,segment_name, SEGMENT_CREATED
2 	FROM user_lobs

TABLE_NAME                     SEGMENT_NAME         SEGMENT_CREATED
------------------------------ -------------------- ---------------
TEST_SEG                       TEST_SEG_DATA_LOB    NO

=========================================================================================
Inserting the first row to the table.
=========================================================================================

SQL> INSERT INTO TEST_SEG VALUES (1,'Roni@@dbsnaps.com','DBSNAPS','Deferred Segment Creation')

1 row created.

SQL> commit

Commit complete.

=========================================================================================
All the relevant segments will be created.
Displaying relevant information FROM the System views.
=========================================================================================

SQL> SELECT segment_name ,SEGMENT_TYPE
2 FROM user_segments

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
TEST_SEG             TABLE
TEST_SEG_DATA_LOB_IX LOBINDEX
ID_PK                INDEX
EMAIL_UK             INDEX
TEST_SEG_DATA_LOB    LOBSEGMENT

SQL> SELECT segment_name, extent_id, bytes
2 	FROM user_extents

SEGMENT_NAME          EXTENT_ID      BYTES
-------------------- ---------- ----------
TEST_SEG                      0      65536
TEST_SEG_DATA_LOB_IX          0      65536
ID_PK                         0      65536
EMAIL_UK                      0      65536
TEST_SEG_DATA_LOB             0      65536
SQL> SELECT table_name, SEGMENT_CREATED
2 	FROM user_tables

TABLE_NAME                     SEGMENT_CREATED
------------------------------ ---------------
TEST_SEG                       YES

SQL> SELECT table_name, index_name, SEGMENT_CREATED
2 	FROM user_indexes
TABLE_NAME                     INDEX_NAME                     SEGMENT_CREATED
------------------------------ ------------------------------ ---------------
TEST_SEG                       ID_PK                          YES
TEST_SEG                       TEST_SEG_DATA_LOB_IX           YES
TEST_SEG                       EMAIL_UK                       YES
SQL> SELECT  TABLE_NAME ,segment_name, SEGMENT_CREATED
2 	FROM user_lobs

TABLE_NAME                     SEGMENT_NAME         SEGMENT_CREATED
------------------------------ -------------------- ---------------
TEST_SEG                       TEST_SEG_DATA_LOB    YES

=========================================================================================
The parameter can be changed in the System/Session level.
Changing the session settings to deferred_segment_creation=FALSE.
=========================================================================================

ALTER SESSION SET deferred_segment_creation=FALSE

Session altered.

=========================================================================================
While this parameter is set to FALSE, tables will be created with all their segments.
=========================================================================================
prompt
SQL> CREATE TABLE test_seg2 (id number CONSTRAINT id2_pk primary key,
2    			name VARCHAR2(30))

Table created.

=========================================================================================
Displaying relevant information FROM the System views.
=========================================================================================

SQL> SELECT segment_name ,SEGMENT_TYPE
2 	FROM user_segments
3 	WHERE segment_name = 'TEST_SEG2'

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
TEST_SEG2            TABLE
SQL> SELECT segment_name, extent_id, bytes
2 	FROM user_extents
3 	WHERE segment_name = 'TEST_SEG2'

SEGMENT_NAME          EXTENT_ID      BYTES
-------------------- ---------- ----------
TEST_SEG2                     0      65536

SQL> SELECT table_name, SEGMENT_CREATED
2 	FROM user_tables
3 	WHERE table_name = 'TEST_SEG2'

TABLE_NAME                     SEGMENT_CREATED
------------------------------ ---------------
TEST_SEG2                      YES

SQL> SELECT table_name, index_name, SEGMENT_CREATED
2 	FROM user_indexes
3 	WHERE table_name = 'TEST_SEG2'

TABLE_NAME                     INDEX_NAME                     SEGMENT_CREATED
------------------------------ ------------------------------ ---------------
TEST_SEG2                      ID2_PK                         YES

=========================================================================================
In addition, the feature can be enabled/Disabled for a Single table.
While the parameter is set to TRUE, we will disable the deferred segment creation for a single table .
=========================================================================================

ALTER SESSION SET deferred_segment_creation=TRUE

Session altered.

SQL> CREATE TABLE test_seg3 (id number CONSTRAINT id3_pk primary key,
2	    			name VARCHAR2(30))
3				SEGMENT CREATION IMMEDIATE

Table created.

=========================================================================================
Displaying relevant information FROM the System views.
=========================================================================================

SQL> SELECT segment_name ,SEGMENT_TYPE
2 	FROM user_segments
3 	WHERE segment_name = 'TEST_SEG3'

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
TEST_SEG3            TABLE

SQL> SELECT segment_name, extent_id, bytes
2 	FROM user_extents
3	WHERE segment_name = 'TEST_SEG3'

SEGMENT_NAME          EXTENT_ID      BYTES
-------------------- ---------- ----------
TEST_SEG3                     0      65536

SQL> SELECT table_name, SEGMENT_CREATED
2 	FROM user_tables
3 	WHERE table_name = 'TEST_SEG3'

TABLE_NAME                     SEGMENT_CREATED
------------------------------ ---------------
TEST_SEG3                      YES

SQL> SELECT table_name, index_name, SEGMENT_CREATED
2 	FROM user_indexes
3 	WHERE table_name = 'TEST_SEG3'

TABLE_NAME                     INDEX_NAME                     SEGMENT_CREATED
------------------------------ ------------------------------ ---------------
TEST_SEG3                      ID3_PK                         YES

=========================================================================================
While the parameter is set to FALSE, we will Enable the deferred segment creation for a single table .
=========================================================================================

SQL> ALTER SESSION SET deferred_segment_creation=FALSE

Session altered.

SQL> CREATE TABLE test_seg4 (id number CONSTRAINT id4_pk primary key,
2         			name VARCHAR2(30))
3  	SEGMENT CREATION DEFERRED

Table created.

=========================================================================================
Displaying relevant information FROM the System views.
=========================================================================================

SQL> SELECT segment_name ,SEGMENT_TYPE
2	FROM user_segments
3 	WHERE segment_name = 'TEST_SEG4'

no rows selected

SQL> SELECT segment_name, extent_id, bytes
2 	FROM user_extents
3 	WHERE segment_name = 'TEST_SEG4'

no rows selected

SQL> SELECT table_name, SEGMENT_CREATED
2 FROM user_tables
3 WHERE table_name = 'TEST_SEG4'

TABLE_NAME                     SEGMENT_CREATED
------------------------------ ---------------
TEST_SEG4                      NO

SQL> SELECT table_name, index_name, SEGMENT_CREATED
2 	FROM user_indexes
3 	WHERE table_name = 'TEST_SEG4'

TABLE_NAME                     INDEX_NAME                     SEGMENT_CREATED
------------------------------ ------------------------------ ---------------
TEST_SEG4                      ID4_PK                         NO

=========================================================================================
The END. Dropping the demonstration user
=========================================================================================

SQL> CONNECT / as sysdba

Connected.

DROP USER seg_demo CASCADE

User dropped.

Leave a Reply

 
Log in / Advanced NewsPaper by Gabfire Themes