Re: A little baffled by "chained rows"....

On Tue, 12 Aug 2003 06:11:50 GMT, Jodi Elisabeth <theheadchick_at_elisabethco.com> wrote:

> and I say "chained rows" but they aren't really, they must be migrated
> because my block size is 8K and my avg row len for this table is 700
> bytes.
>
> SO, that said...I had a theory and I ran it past oracle...My theory to
> fix
> the "chained" rows was to copy the data out to another table (create
> table
> as select OR insert into select *), drop the offending table, recreate
> the
> table (with the new pctfree and pctused params) as select from my copy.
>
> Oracle said SURE, that's one way to fix it all in one swoop without
> having
> to exp/imp.
>
> Well, I didn't work. I still have the chained rows. I was hoping to avoid
> the whole exp/imp thing...is that the only thing that will work for this?
> How is that fundamentally different than what I tried?
>
> Thanks!
> Jodi (the baffled!)
>

Hi Jodi,

You don't mention a version, so I'll just mention that in 8i and above, a much quicker fix is 'alter table blah move;'.

You also don't mention whether you re-calculated statistics on the offending table after doing all your poking around. The dba_tables view is static, and the chain_cnt column won't magically fix itself up. You need to 'analyze table blah compute statistics' to see any changes.

If the row migration doesn't get fixed when you delete the rows and re- insert them (which is what you did, and what move does in one command), and after you've re-computed statistics, then it really isn't row migration. You'll then have to be a lot more subtle to see the actual data which is causing the grief (clearly, if the average row length is just 700 bytes, then it can only be rogue rows... incidentally, it's better to do a describe of the table to see whether or not it might be migration versus chaining: if the table definition cannot possibly allow more than about 80 bytes per row, then you know straight away it can't be chaining. But if the definition includes BLOBS and CLOBS and LONGS, then although most of your rows might have such fields empty, it's possible for some of the rows to have them, and thus to chain.)

So, the truly subtle way is: log on as SYSTEM and run the utlchain.sql script in ORQACLE_HOME\rdbms\admin. That creates a new table called 'chained_rows'. Then: analyze table blah list chained rows; That causes the chained_rows table to be populated with the rowid of the chained rows. Then you can select * from blah where rowid in (select head_rowid from chained_rows); That will show you the actual data from the real table that is known to have chained (or migrated). Seeing it might make you realise that one or two rows really are humungously large.

Finally, a subtler fix for the problem: if you are trying to fix up a bit of row migration or chaining, you don't necessarily want to have to move the entire multi-gigabyte table (or copy it, delete it, and re-insert). You want, instead, just to home in on the offending rows. Therefore, having already populated the chained_rows table with the rowids of the offending rows:

create table X as select * from blah where 1=2; (copies the table definition, but no rows inserted)
insert into table X select * from blah where rowid in (select head_rowid from chained_rows); (copies the offending rows) delete from blah where rowid in (select head_rowid from chained_rows); (removes the offending rows)
insert into blah select * from X; (re-inserts the old rows, and because it's an insert, they can't possibly migrate).

Then analyze table blah compute statistics to make sure it really has worked.

Most of which you probably already knew, but just covering the bases: I suspect (because you didn't mention anyhting about it) that you just forgot to re-calculate statistics.

Regards
HJR Received on Tue Aug 12 2003 - 01:56:20 CDT