What Is Deferred Segment Creation?
Did you know that deferred segment creation was introduced in 11.2? Some DBAs might not be familiar with this concept at all, even with the newer versions of Oracle. Prior to 11.2, when you created a table or an index, the segment was also created automatically. When the segment was created, then at least one extent was allocated to the segment.
Let me do a quick recap on extents! Segments are made up of extents. An extents is made up of a contiguous set of Oracle data blocks within a data file. This means that an extent cannot span multiple data files, however a segment can span multiple data files.
Starting with 11.2, the behaviour of segment creation has changed! Whether or not the segment is created when the table or index is created, is controlled by the DEFERRED_SEGMENT_CREATION parameter, and this will default to TRUE!
What does this mean?
When you create a table or an index, the segment is not automatically created, it is deferred. You can prove this by querying dba_tables or dba_indexes, the table will show up in the data dictionary. Then
querying dba_segments, and the segments for that same table or index will not be listed there right after creation. The segment will be created with the first row that gets inserted into the table.
Why is this a good thing?
There are some applications that you install in your database that create lots of tables and indexes. However, depending on the options that you install, you might never use these tables and indexes. They just sit there empty, unused. If segments are also created automatically, they use unnecessary space in your database. And remember, you are also backing up these tables…Lots of work that doesn’t need to happen. Deferring segment creation is a good thing for this scenario! The segment gets created when the first row is inserted!
How does this affect you?
The only way I see this affecting you in a negative way, is, if you confirm table or index creation by querying dba_segments instead of dba_tables or dba_indexes. You might not get accurate results! But now you know, and you can adjust your scripts!
Let’s see an example to better understand the behaviour:
show parameter deferred_segment_creation
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
create table test1
(id number,
constraint test1_pk primary key(id) );
select table_name from dba_tables where table_name='TEST1';
TABLE_NAME
------------------------------
TEST1
select index_name from dba_indexes where table_name='TEST1';
INDEX_NAME
------------------------------
TEST1_PK
select segment_name, segment_type , count(*)
from dba_segments where segment_name in ('TEST1','TEST1_PK')
group by segment_name, segment_type;
no rows selected
insert into test1
values (1);
commit;
select segment_name, segment_type , count(*)
from dba_segments where segment_name in ('TEST1','TEST1_PK')
group by segment_name, segment_type;
SEGMENT_NAME SEGMENT_TYPE COUNT(*)
------------------------------- ------------------ ----------
TEST1 TABLE 1
TEST1_PK INDEX 1
If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!
If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get Today‘s Special, only $13.99 CAD !
