Re: Table fragmented

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Fri, 5 Dec 2008 07:08:21 -0800 (PST)
Message-ID: <8816ad85-37c5-4a3a-9a6e-820899b72b4f@f3g2000yqf.googlegroups.com>


On Dec 4, 4:13 pm, "astalavista" <nob..._at_nowhere.com> wrote:
> bad direction
> sorry
>
> "astalavista" <nob..._at_nowhere.com> a écrit dans le message de news:
> 4938458a$0$14393$426a7..._at_news.free.fr...
>
>
>
> > Table size (with fragmentation)
>
> > SQL> select table_name,round((blocks*8),2)||'kb' "size"
> > 2 from user_tables
> > 3 where table_name = 'BIG1';
>
> > TABLE_NAME size
> > ------------------------------ ------------------------------------------
> > BIG1 72952kb
>
> > Actual data in table:
>
> > SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
> > 2 from user_tables
> > 3 where table_name = 'BIG1';
>
> > TABLE_NAME                     size
> > ------------------------------ ------------------------------------------
> > BIG1                           30604.2kb
>
> > Note = 72952 - 30604 = 42348 Kb is wasted space in table
>
> > The difference between two values is 60% and Pctfree 10% (default) - so,
> > the table has 50% extra space which is wasted because there is no data.- Hide quoted text -
>
> - Show quoted text -

astalavista, you probably noticed that your calculation fails to allow for block overhead requirements.

What DBA's must also consider is the tablespace extent allocation method in use for the object because in a uniform extent tablespace using 1M extents a table with only 3 rows is going to take 1M. In the case of an autoallocate tablespace it will probably take 64K but if the number of rows grows enough the extent size might change.

Space wasteage is one of the considerations you have to take into account when you determine your tablespace extent allocation method and set the object initial extent size which is used in determing how many extents of what size to allocate within a locally managed tablespace.

HTH -- Mark D Powell -- Received on Fri Dec 05 2008 - 09:08:21 CST

Original text of this message