Re: Very Strange Migrating Rows experience (Chained Rows)

What I do is follow the Oracle8 Tuning book on how to remove migrating rows. What I had noticed was that the numbers were growing day after day. In fact for four consecutive days the numbers were exactly the same.

What I have been doing was to stop at step 3c and commit the work. Then look carefully at the intermediate table. The figure I gave in my last posting came from that intermediate table. So, this means that this is a NEWly create table. The contents are from fresh inserts with no updates. There are no other rows in the table and there are 140 of them with 140 reported as Chained. I gain this information from 'analyze table intermediate_table compute statistics'.

Answer to you second question is Copy out, delete, copy back and then commit.

No the rows not only do not go back to the same ROWIDs per PK but normally appear to get a completely new set of ROWIDs. This should mean brand new storage allocation, yes?

140 is the total row population then analyzed - as mentioned above.

Version of Oracle is 8.0.5.2.1 on Alpha Tru64UNIX

Regards
--
Kevin A Lewis (BOCM PAULS LTD) - Animal Feed Manufacturer - Ipswich United Kingdom)

                        <Kevin_A_Lewis_at_Hotmail.com>

The views expressed herein by the author of this document are not necessarily those of BOCM PAULS Ltd. Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in message news:948489909.26311.1.nnrp-07.9e984b29_at_news.demon.co.uk...
>
> Can you be a little more precise about how you
> copy and delete them ?
>
> e.g. copy out into a different table, then commit,
> delete then commit. Or copy out, delete copy back
> commit etc.
>
>
> Do the same PKs end up with their original rowids,
> or do you get completely new rowids, or the same
> set of rowids with PKs in different places.
>
> Are these rows the ONLY 140 rows in the table,
> AVG_ROW_LEN applies to the whole table, how
> have you determined the actually lengths of
> these rows ?
>
> Which version of Oracle ?
>
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Kevin A Lewis wrote in message
> <8a%h4.2331$hr4.54539_at_newreader.ukcore.bt.net>...
> >I have been trying to remove migrating rows from some tables in a
> production
> >database and the problem appears to be getting worse rather than better.
> >
> >I have 140 rows with an AVG_ROW_LEN of 226 in a database of 2k block size
> >the table has a PCTFREE of 10 and PCTUSED of 40 (the defaults). All 140
are
> >recorded by ANALYZE as CHAINED ROWS. I copy them, delete the original
rows
> >and write them back; yet on reANALYZE they are recorded as still CHAINED
> >ROWS.
> >
> >Surely they cannot be real chained rows, they are too short. And yet
surely
> >they cannot be migrated rows as they have just been inserted and suffered
> no
> >updates at all.
> >
> >Confused of Ipswich England
> >
> >Regards
> >
> >
> >
> >--
> >Kevin A Lewis (BOCM PAULS LTD) - Animal Feed Manufacturer - Ipswich
United
> >Kingdom)
> > <Kevin_A_Lewis_at_Hotmail.com>
> >
> >The views expressed herein by the author of this document
> >are not necessarily those of BOCM PAULS Ltd.
> >
> >
>
>
>
>
Received on Mon Jan 24 2000 - 03:26:39 CST