Re: table reorganizations

> I'm surprised at these responses. I'm asking what sql statement most
> people use to identify tables that need reorganization because of
> "holes".
>
> We had an Oracle consultant here and he uses
>
> Select table_name,
> blocks-((num_rows*avg_row_len/<block_size>)*(1+(pct_free/100))) blkdiff
> From dba_tables
> Where blkdiff > 100;
>
> To determine reorganization need.
>

Hi Jolene

You already received a number of replies why there are issues with using a general formula as above. IF a table is commonly accessed via a FTS AND, IF sufficient deletes without subsequent re-inserts (permanent table shrinkage, ouch, it's a male thing ;) or IF you've set a shocking PCTUSED which prevents inserts reclaiming deleted space, or IF you've set a shocking PCTFREE with no subsequent row size increase (etc) AND FTS access performance causes notable performance issues, you might have a case for a table re-org. The above conditions are not particularly common (perhaps a table containing future bookings for sleepovers at Michael Jackson's place ? ;) but if they do, consider the clustering factor of your most significant index access while you're at it, assuming there is one.

The point I'll like to make are a couple of issues with your formula above.

Firstly, it doesn't consider general block overhead details which means for largish tables with a sum of 100 block or more of overhead, the (rather expensive) re-org would achieve nothing.

Secondly, it doesn't consider blocks above the HWM which could quite easily exceed the 100 mark depending on extent size. Again the re-org would result in a somewhat disappointing outcome.

The formula above will potentially call for the re-org of *all* your larger tables for absolutely no benefit.

Glad you asked the question ....

Cheers

Richard

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Richard Foote
  INET: richard.foote_at_bigpond.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).