Re: (Q) Chained rows/LONG & Deletes

hi,

Don't know if this will be of any help, but in oracle 8 there is the ALTER TABLE MOVE command, which should simplify the elimination of chainded rows.

bye
john

"crumedgeon" <zimsbait_at_hotmail.com> wrote in message news:9fuj0o$7uv$1_at_slb7.atl.mindspring.net...
> Hey all,
>
> I have a 7.3.4.5 database. There are 15 tables with LONG
> datatypes in them. The average size of these tables is ~40G.
> Total rowcounts in them are around 50 mill. These are OLTP
> tables with a LOT of updates going on.
>
> Fortunately, we purge out records > 30 days old. We have gotten
> some small increases in the number of inserts lately, and these tables
> are starting to grow. Of course, since LONGs are notorious for
> chaining, I have a heck of a lot of chained rows in these tables. This
> is naturally causing performance degradation and rapid growth.
>
> My question is this:
> How does Oracle move the LONG chained block information to the
> freelist? Are all the blocks for these chained rows (under the HWM)
> able to be reused in the future?
>
> I want to make sure I choose the correct way to improve performance
> in this case. Imp/Exp is one way to alleviate the problem. I could create
> a temp table and move the chained rows to the new table, delete and
> move them back. I can also fiddle with pctfree/pctused to try to get 1
> row/block. (which seems inefficient at best, and nightmarishly space
> wasting at worst)
>
> Also, does anyone have a better way (other than dba_segments,
> dba_free_space and dbms_space) to see how much space is truly
> being wasted by these chained LONGS? (the reason I say better is
> because I can't really tell how much space is being wasted by a chained
> LONG by looking at any of the standard views)
>
> TIA,
> --
> cr
>
>
Received on Sun Jun 10 2001 - 05:22:47 CDT