RE: Best bet for table defragmentation

 
Rather than "assume" the table is highly fragmented why not use the
table statistics and the row count to calculate the necessary table size
and then compare that to what the table is using.  It would be a shame
to waste a lot of time reorganizing the table only to have it take only
a couple of megabytes less than what it is using now.
 
Here is something I have posted a couple of times in the past
 
Oracle Table Sizing Estimation Formula
 
Abbreviations
 
AVIL = Available space in block to hold rows
OBS  = Oracle block size
RS   = Row size
Ovhd = Fixed plus variable block overhead
TBR  = Total blocks required
 
Expected size = (( RS * number of rows) / AVIL ) * OBS) / K or M
where K = 1024 and M = 1048576   
 
Figure RS as
 for varchar2 expected number of characters for column
 for number where p = number of digits and s = 0 for positive and 1 for
negative 
     round((( length((p) + s) / 2)) + 1
 for date use 7
 + 1 byte per column in row
 + 3 byte row overhead per row
[Or use the dba_tables.avg_row_len value]
 
Figure number of bytes for block as 
pctfree = decimal value of pctfree parameter * OBS
 
The variable area is mostly made up of 23 bytes per initran area and 2
bytes per row for the row table entry.  For 1 to 4 initrans I have
calculated row overhead of 86 to 156 bytes so I just use a constant for
this value.  Try 113 to start.
 
Figure AVIL as OBS - ovhd - pctfree 
 
Total bytes = number of expected rows * RS 
TBR = Total Bytes / AVIL
Expected Size = TBR * OBS / 1024 [for K]
 
This is one way and it is fairly quick and works pretty well.  The
formula can be improved by adjusting the variable area size for the
number of initrans and for the number of expected rows in the block, but
using a constant works well for us.


-- Mark D Powell -- 
Phone (313) 592-5148 

 



________________________________

        From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Khan, Muhammad S
        Sent: Friday, August 17, 2007 11:56 AM
        To: oracle-l@xxxxxxxxxxxxx
        Subject: Best bet for table defragmentation
        
        

        Its Oracle 9.2.0.7 on Solaris, we have a transaction table with
approximately 68 million rows. It is consisting of about 100GB space in
the tablespace and it is assumed that it is highly fragmented. We
definitely need some space at that tablespace and for that we were
discussing the methods for defragmenting it and get some space back. One
suggested method was export/import but that would take atleast 6 hours
while another one was moving the table to another tablespace and rebuild
the indexes which would not require any downtime. 

         

        Does any guru have any other suggestion regarding that? Or your
input about the above methods as per knowledge and experience???

         

        Thanks,

Other related posts: