RE: table reorganizations

I guess some of the folks on the list are in a "playful" mood today...

The need for a table "reorganization" depends on how it is used.

The query you cite might be illuminating if the table in question is mostly accessed by "full table scans", as it seems to identify tables with large "gaps" due to deletions.  During an FTS, these gaps would still be "traversed", resulting in what might turn out to be excessive I/O required to accomplish the task.

However, if the table in question is commonly accessed via indexed lookups or scans, then reorganizing these tables to "close these gaps" might well be a complete waste of time. The reason being that the "table access by ROWID" action that is the last step of table access via indexes does not scan emptied blocks. It directly addresses populated blocks in the table only. Thus, accessing rows in a table that is 99.99% empty takes no longer than accessing rows in a table that is 100% full, using this access method. Of course, if someone wants to throw clustering factor in, then that assertion starts to get a little squishy, but the fact remains that the effort expended in reorging the table clearly does not provide anything near an adequate "return on investment".

So, the knowledge of how the table is accessed is clearly part of the answer. This puts the equation beyond the scope of a simple query on the data dictionary, although I'm pretty sure that the V$SEGMENT_STATISTICS view in Oracle9i could provide some of the insight into the usage of the table.

So, if the table in question is typically accessed via full table scan, the query you cited is useful. If the table in question is rarely (if ever) accessed via full table scan (or shouldn't be), then the query you cited should at least be changed to indicate a much much much larger "blkdiff" constant. Indeed, table reorgs in such circumstances wouuld help so rarely that they aren't really worth worrying about.

Hope this helps...

-Tim

> 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/1
> 00))) blkdiff From dba_tables
> Where blkdiff > 100;
>
> To determine reorganization need.
>
> What sql statement is used by others?
>
> Jolene
>
> -----Original Message-----
> Sent: Wednesday, January 07, 2004 2:25 PM
> To: Multiple recipients of list ORACLE-L
>
>
> I usually recommend Gospel by Jonathan for its
> completeness and a wide range of subjects. The book you
> mentioned is great for beginner as well. As for the
> number 42, I'll continue using it until this Saturday
> (1/10/2004) when it will become 43. Inflation is not as
> big as you think. PS:
> ---
> I was born on 1/10/1961, and that makes January 10th so
> special. I don't have to work on that great day, mostly
> because it's Saturday.
>
> On 01/07/2004 03:09:53 PM, "Thater, William" wrote:
> > Mladen Gogala scribbled on the wall in glitter crayon:
> >
> > > Lemme guess: you just started on your new job as a
> > > DBA? You are another person to which can only
> wholeheartedly recommend Jonathan's
> > > book. As for your questions, the answer is "42".
> >
> > actually, if she's just starting out, i'd recommend
> > Marlene, Rachel and Jim's book first, then Jonathan's.
> >
> > and are you sure it's not "57" now due to inflation?
> >
> > --
> > Bill "Shrek" Thater ORACLE DBA
> > "I'm going to work my ticket if I can..." -- Gilwell
> > song william.thater_at_carrier.utc.com
> >
> ----------------------------------------------------------
> > ------------ --
> > Perfection of means and confusion of ends seem to
> characterize our age. -
> > Albert Einstein
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.net --
> > Author: Thater, William
> > INET: William.Thater_at_carrier.utc.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).
>
> --
> Mladen Gogala
> Oracle DBA
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net --
> Author: Mladen Gogala
> INET: mladen_at_wangtrading.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). --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net --
> Author: Shrake, Jolene
> INET: JEShrake_at_Pella.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: tim_at_sagelogix.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).