Before 11gr2 when you created a table, index and so on there was ALWAYS allocated 1 initail extent
Example
SQL> create table aap (a number, a2 varchar2(100))
storage (initial 10m) tablespace users;
Table created.
SQL> select blocks, bytes/1024/1024, segment_name,
segment_type
from dba_segments
where segment_name = 'AAP';
BLOCKS BYTES/1024/1024 SEGMENT_NAME SEGMENT_TYPE
-------- --------------- ------------------- ----------------
1280 10 AAP TABLE
You see there is 1 segment from 10Mb
Now the same but now in Oracle 11gr2
SQL> create table aap (a number, a2 varchar2(100))
storage (initial 10m) tablespace users;
Table created.
SQL> select blocks, bytes/1024/1024, segment_name, segment_type
from dba_segments where segment_name = 'AAP';
no rows selected
As you see no initial extent is created.Only when you insert 1 or more rows the first extent is created.
SQL> insert into aap values (1234,'AAP');
1 row created.
SQL> select blocks, bytes/1024/1024, segment_name, segment_type from dba_segments where segment_name = 'AAP';
BLOCKS BYTES/1024/1024 SEGMENT_NAME SEGMENT_TYPE
------- - ------------- ---------------------- --------------
1280 10 AAP TABLE
As you see when the first row is inserted then then the fist extent is created.
It als means that the if you have an index on the table this also get its first extent.
So there is a slide overhead when the first rows are inserted.
SQL> create table noaap (a number, a2 varchar2(100))
storage (initial 10m) tablespace users; 2
Table created.
SQL> create index noaap_1 on noaap(a);
Index created.
SQL> create index noaap_2 on noaap(a2);
Index created.
SQL> select blocks, bytes/1024/1024, segment_name,
segment_type
from dba_segments
where segment_name like'NOAAP%';
no rows selected
SQL> insert into noaap values (1234,'AAP');
1 row created.
SQL> select blocks, bytes/1024/1024, segment_name,
segment_type
from dba_segments
where segment_name like'NOAAP%';
BLOCKS BYTES/1024/1024 SEGMENT_NAME SEGMENT_TYPE
---------- --------------- --------------- ---------------
8 .0625 NOAAP_2 INDEX
1280 10 NOAAP TABLE
8 .0625 NOAAP_1 INDEX
Now what is the advantage.
large applications can created a lot of segments that are not used.(SAP is one of those one's).
Tables can have many indexes that may never be used. Which can result in wasted storage.
If you don't want this then set the following parameter on false
SQL> alter system set deferred_segment_creation = FALSE;
Now does this mean when you truncate the table you don't have any extent anymore.
SQL> truncate table aap;
SQL> select blocks, bytes/1024/1024, segment_name, segment_type
from dba_segments where segment_name = 'AAP';
BLOCKS BYTES/1024/1024 SEGMENT_NAME SEGMENT_TYPE
------- - ------------- ---------------------- --------------
1280 10 AAP TABLE
The answer is no. The initial extent is kept.
That it
No comments:
Post a Comment