DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: what's the step to reorganize a table by using CTAS?

  1. #1
    Join Date
    Jun 2001
    Posts
    193
    what's the step to reorganize a table by using
    CTAS(create table as select)?


    let's say we have table parent and child.
    we can
    create table parent_new as select * from parent.
    rename parent to parent_old;
    rename parent_new to parent.
    but do we need to lock table parent during the CTAS?
    if not, what happen if somebody insert some new record to
    parent while we copy it over to parent_new?
    some record may lost.

    give me a hint
    guru is on the way!!!!

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    That would be inconsistent creation. Instead you could lock and do this, that way you can ensure the consistency. Other wise, once after you do this move, then do the consistency check.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Don't do the table reorgs this way, particulary if you are on 8i. It is almost 100% you will forget something - you have to take care of:
    indexes,
    PKs,
    UKs,
    check constraints,
    FKs from this table,
    FKs from other tables referencing this table,
    triggers,
    grants,
    snapshot logs,
    etc etc...

    Why not simply use ALTER TABLE MOVE command - you will only have to rebuild (not recreate!) indexes, everything else will be taken care of by the system.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    That was a good point. Yes, I have forgot to think of that Jurij!! Thanx for the correction... But in 8i STD the online rebuild, coalesce are not availabe. So on such cases I personally would resort to export and import. Where I would only have to mark my triggers. ...

    Sam


    [Edited by sambavan on 08-17-2001 at 01:59 AM]
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by sambavan
    [B]But in 8i STD the online rebuild, coalesce are not availabe.
    This is news to me. Are you sure about that?

    I know STD lacks some of the EE functionality, but I was under the impression the TABLE MOVE, INDEX REBUILD and TS COALESCE are availabe in STD also....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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