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';
,segment_name,extents,PCT_INCREASE,TABLESPACE_NAME from dba_segments where segment_name='COMP_TRAN_SUM_APP_CMP_INST_MIN';
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
,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-lReceived on Fri Oct 19 2012 - 08:20:32 CEST