Fixing chained rows - should I disable triggers ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Fixing chained rows - should I disable triggers ?

  1. #1
    Join Date
    May 2001
    Location
    Wayne, NJ
    Posts
    5

    Fixing chained rows - should I disable triggers ?

    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 ?

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131
    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
    I remember when this place was cool.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jan 2001
    Posts
    3,131
    Wow, so you are right and Oracle is wrong huh?

    Damn them!!



    MH
    I remember when this place was cool.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    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..
    -nagarjuna

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width