Wednesday, August 1, 2012

On demand segments Oracle 11gr2

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