Re: FRAGMANTATION on a table

From: AJ Allen <aallen20_at_ford.com>
Date: Thu, 10 Feb 2000 12:48:30 -0500
Message-ID: <38A2F9EE.71CA284E@ford.com>

Michael Puente wrote:
>
> run the utlchain.sql in the /$ORACLE_HOME/rdbms/admin directory
>
> then issue this command:
> analyze table table_name list chained rows into chained_rows.
>
> query the chained_rows table as such:
> select count(*) from chained_rows where table_name='TABLE_NAME';
> compare this to the actual number of rows in the table.
>
> If the ratio of chained_rows to num_rows is high rebuild your table.
>
> atta707_at_my-deja.com wrote:
>
> > hi all the oracle gurus,
> >
> > i'm a newbie oracle dba.
> >
> > we've got a table with lotta frequent inserts and deletes. more often
> > the highwater mark, and number of extents allocated for this table is
> > way above what it should be.
> > could you please suggest a way to find out how bad is the fragmentation
> > and, more importantly, if it is the appropriate time to drop and re-
> > create this table to prevent the hog.
> >
> > thanks in anticipation.
> >
> > ATTA
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

If you need to eliminate the chained rows it is not necessary to rebuild the table. What
you can do is to unchain the chained rows. Like this: 1. back up the tablespace or database.
2. create a table that contains just the chained rows. Since the table chained_rows has

   the ROWIDs of the chained rows this is not be very difficult.     CREATE

     TABLE chained_taba
        AS
    SELECT *
      FROM taba
     WHERE rowid IN (SELECT head_rowid
                       FROM chained_rows
                      WHERE <qualify if necessary>)

    ;
    COMMIT;
3. delete the chained rows from the problem table. Again, since you know the ROWIDs of

   the chained rows this is pretty straight forward.     DELETE

      FROM taba
     WHERE rowid IN (SELECT head_rowid
                       FROM chained_rows
                      WHERE <qualify if necessary>)

    ;
    <<check results>>
    COMMIT;
4. insert the saved chained rows back into the problem table.

   INSERT
     INTO taba
   SELECT *
     FROM chained_taba
   ;
    <<check results>>
5. drop the temporary table
CAVEAT: you may have constraints that could make the process a little more complicated to

        get going. Once you have overcome these the rest is pretty straight forward.
ALSO: check your results each step along the way.

--

*------------------------------*----------------------------*
| Andrew Allen                 | Against fools, the gods    |
| Process Leadership / PSL DBA | themselves contend in vain |
| Dearborn, MI USA             | -- author unknown          |
*------------------------------*----------------------------*

Received on Thu Feb 10 2000 - 11:48:30 CST