Posted by 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.
