Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2002

    Unanswered: Chained Rows issue


    We have chained rows on a bunch of tables. Oracle
    We already know to re-create the tables with a higher PCTFREE physical attribute. The tables have about 20 columns and half a million rows each. A lot of inserts and updates occur.

    I was thinking of recreating the tables using CTAS (create table as ...), dropping the tables with the chained rows, recreating them with both PCTFREE and PCTUSED at 40%, and then re-inserting the data.
    Anything wrong with those settings?, any caviats to look out for?

  2. #2
    Join Date
    Mar 2002
    Reading, UK
    You can also do alter table move followed by alter index rebuild (as the indexes become invalidated) or better still use dbms_redefinition as you can then do it without any downtime


  3. #3
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    If you set PCTFREE=40, prior to the reload, you'll end up with "wasted space" for those rows which have already been "completely" updated.
    At my previous job, part of the application supported Accounts Recievables.
    The an AR record was inserted at the time the order was shipped & got updated later with payment & similar information.
    After the invoice was closed, the AR record would NEVER again be changed.
    The AR_TABLE contained 13 rolling months worth of history, but the majority of the records older than about 60 days never changed again.

    One possible tradeoff is increase the PCTFREE to 40% and only move out & back the actual chained rows.

    Be careful if triggers exist on this table being discussed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Apr 2003
    Greenville, SC (USA)
    I have used both methods and as anacedent says, it depends on the data
    and how it will be processed ... The alter table move works well as does
    the redefinition, but as AlanP stated, your indexes need to be rebuild (move).

    If you have data like anacedent's ex: then delete chained rows and reinsert
    is the way to go... Here's a little script that asks for the table_name with
    the chain rows. It will create a temp table with only the chain rows in
    it, remove the rows from the prod table, then reinsert the rows ...
    turn off any triggers on the table FIRST !!!

    alter table table_name disable all triggers;

    drop table chained_rows;
    start C:\oracle\ora81\RDBMS\ADMIN\utlchain
    set ECHO OFF

    analyze table &chaintabl list chained rows into chained_rows;

    create table chaintemp as
    select * from &chaintabl
    where rowid in (select head_rowid from chained_rows);

    delete from &chaintabl
    where rowid in (select head_rowid from chained_rows);

    insert into &chaintabl
    select * from chaintemp;

    drop table chaintemp;

    alter table table_name enable all triggers;


  5. #5
    Join Date
    Oct 2004
    Only word of caution in method identified in previous posting.

    If you have long and/or blob data type, insert into using select * will not work. You will loose data.

    Ensure your table definition does not have long data type. If chaining is cause by this data type, you may have to live with the chaining.

  6. #6
    Join Date
    Nov 2002
    Desk, slightly south of keyboard

    Just wondering...

    Quote Originally Posted by JimYoo
    Ensure your table definition does not have long data type. If chaining is cause by this data type, you may have to live with the chaining.
    Would moving the long/lob storage away from the table (out of line with it's own specific storage parameters, as opposed to in-line using your table storage parameters) alleviate chaining as a result of the lob(s)?

    I don't know to be honest, haven't tested it, but my initial thoughts are that if lobs are your problem, it's worth checking.

    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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