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