Alter table move ...general question

[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? 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.

[jhmartin]

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.

[marist89]

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.

[Bore]

Agree with the above.
MAYBE you can consider dropping indexes before move and rebuilding them afterwords using parallel, nologgin, larger PGA etc

[slimdave]

I don't think it neccessary to drop the indexes -- you can rebuild in parallel and nologging etc anyway.

[Bore]

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

[davey23uk]

yes they are unusable as the rowid's have changed in the table