Alter table move ...general question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Alter table move ...general question

  1. #1
    Join Date
    Mar 2001
    Posts
    149

    Alter table move ...general question

    Hi everyone,
    I'm trying to reclaim the space in a table that used to have 40 millions rows by using ALTER TABLE MOVE command ( the table has 2 mil rows now). I tried this command on a table that is a tenth of the size of the table above and the ALTER TABLE MOVE command took 30 minutes. Would it fair to say that the 40+ millions table will take around 300 minutes to complete? Is there a way to project this run? How can I improve the performance of alter table move ? Would create temp table/truncate/re-insert method be a better way to do this? When this ALTER command is in progress can we have DMLs command on this table. Thanks for your help.

  2. #2
    If you use alter table move then the table will be locked for the operation. If you use the DBMS_REDEFINITION package then DML can continue.

    Assuming that the indexes and constraints are equivalent, a larger table move should scale linearly.

    If you wanted to go the temp table route, it would be better to create table as select ... nologging , drop FK constraints against old table, drop the old table, rename the new table to the old table name, recreate FK constraints (novalidate if you need speed, but they should be validated at some point for completeness), and of course perform a backup.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by newbie
    Hi everyone,
    I'm trying to reclaim the space in a table that used to have 40 millions rows by using ALTER TABLE MOVE command ( the table has 2 mil rows now). I tried this command on a table that is a tenth of the size of the table above and the ALTER TABLE MOVE command took 30 minutes. Would it fair to say that the 40+ millions table will take around 300 minutes to complete?
    probably pretty close.
    Is there a way to project this run?
    v$session_longops
    search for several examples of how to use.

    How can I improve the performance of alter table move ?
    You want to make sure you're spreading the I/O over multiple spindles (if possible) and that you're not introducing contention from reading vs. writing. Also, investigate NOLOGGING and PARALLEL if you have the available CPU. In my environment, a 2M row table would take about 1 minute to move. I don't say this to boast (although I do have some rockin' hardware), but to indicate 2M rows ain't really that much.

    Would create temp table/truncate/re-insert method be a better way to do this?
    IMHO, plain and simple NO.
    When this ALTER command is in progress can we have DMLs command on this table.
    depends on version. dbms_redefinition can help. I think 10g has online table moves. 8i you're screwed.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Agree with the above.
    MAYBE you can consider dropping indexes before move and rebuilding them afterwords using parallel, nologgin, larger PGA etc

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I don't think it neccessary to drop the indexes -- you can rebuild in parallel and nologging etc anyway.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #6
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Dave, you are maybe right.I do not exatly remember if move maintains indexes or makes them unusable, but now when you mentioned that I remembered that seems it makes them unusable right? so... agree with you

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    yes they are unusable as the rowid's have changed in the table

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