Fixing chained rows - should I disable triggers ?

[ZAPPAJOE52]

I've been reading through some of the threads about taking care of chained rows. I understand the method of deleting and re-inserting the rows, but nobody seems to mention disabling triggers.

Shouldn't triggers on the table be disabled before deleting and re-inserting ?

[slimdave]

Deleting and inserting won't fixed chained rows, but it will fix migrated rows.

Yes, disable triggers and also foreign keys that reference that table.

[Mr.Hanky]

Ok, I like to gamble. Dave, I respectfully disagree.

If you...

ANALYZE TABLE HANKY.YO_MOMMA LIST CHAINED ROWS;

CREATE TABLE CHAIN_TEMP as
SELECT * from HANKY.YO_MOMMA
WHERE rowid in (SELECT head_rowid from CHAINED_ROWS
WHERE table_name = 'YO_MOMMA);

SELECT COUNT(*) FROM CHAIN_TEMP;

DELETE from HANKY.YO_MOMMA
WHERE rowid in (SELECT head_rowid from CHAINED_ROWS
WHERE table_name = 'YO_MOMMA);

INSERT into HANKY.YO_MOMMA
SELECT * from CHAIN_TEMP;

DROP table CHAIN_TEMP;

DELETE from CHAINED_ROWS
WHERE table_name = 'YO_MOMMA;

You will eliminate chained rows in this table.

Yes no?
Or did you mix up chained and migrated?

MH

[slimdave]

What happened is that Oracle mixed up chained and migrated rows ... the CHAINED_ROWS column refers to both chained and migrated rows.

Migrated = rows that were updated to be longer and didn't have room in their current block, so the data got moved and a pointer was left in the original block to show where the row migrated to.

Chained = rows that were too big to fit into any single block (eg. a row of 3kb in a table of block size 2kb). They get "chained" over multiple rows.

The only fix for chained rows is to move the table to a tablespace of larger block size. Migrated rows are the ones that get fixed through delete/insert.

[Mr.Hanky]

Wow, so you are right and Oracle is wrong huh?

Damn them!!



MH

[slimdave]

Originally posted by Mr.Hanky
Wow, so you are right and Oracle is wrong huh?

Damn them!!



MH

Well, their naming of the column is misleading, shall we say?

[nagarjuna]

Originally posted by Mr.Hanky
Wow, so you are right and Oracle is wrong huh?

Damn them!!



MH

Slimdave is right.. chained rows and migrated rows.. all these are our own definitions.. the rows that cant be fit into single block are called chained rows.. the ones that got migrated because of lack space at the time of update and can fit into single block are migrated rows..