Table fragmentation when using AUTOALLOCATE compared to UNIFORM tablespace extent allocation type.

From: Yasser Khan <yasser8_at_gmail.com>
Date: Fri, 19 Oct 2012 11:50:32 +0530
Message-ID: <CALPjTecGnukoxiEyeHRLJWRA4A=Q-QUYFn-m2B0Kj0ZOAfjO0Q_at_mail.gmail.com>

We got 2 database which are similar except ALLOCATION_TYPE of a tablespace. Due to this one of the table has huge difference in size when compared between these two databases.
SQL> select bytes/1024/1024 MB
,segment_name,extents,PCT_INCREASE,TABLESPACE_NAME from dba_segments where segment_name='COMP_TRAN_SUM_APP_CMP_INST_MIN';

        MB SEGMENT_NAME
                    EXTENTS PCT_INCREASE TABLESPACE_NAME

---

---

• ------------ ------------------------------ 1916 T_MIN 479 0 DATA

SQL> select bytes/1024/1024 MB
,segment_name,extents,PCT_INCREASE,TABLESPACE_NAME from dba_segments where segment_name='COMP_TRAN_SUM_APP_CMP_INST_MIN';

          MB SEGMENT_NAME
                        EXTENTS PCT_INCREASE TABLESPACE_NAME


------------

---------------------------------------------------------------------------------

------------ ------------ ------------------------------

       15359 T_MIN
                                                     366              DATA

SQL> select count(*) from T_MIN;

    COUNT(*)

---

    27201237

SQL> select count(*) from T_MIN;

    COUNT(*)

---

    25394238

Is it true that AUTOALLOCATE will cause fragmentation if application performs inserts and deletes heavily on the table?

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 19 2012 - 08:20:32 CEST