Link to home
Start Free TrialLog in
Avatar of patd1
patd1Flag for United States of America

asked on

alter column on a large table taking too long

I have a table with 80 million rows of data. I want to alter data type of one of the columns on this table (varchar to numeric), but the alter column sql has been running for an hour now, still not done, and has a lock on the table. Is there a faster way to alter column on a large table.

Thanks for your help.
ASKER CERTIFIED SOLUTION
Avatar of Kanti Prasad
Kanti Prasad

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Normally you would place the database into single user mode.
Avatar of Aneesh
If its already running, you can wait till it finishes.  Otherwise  As Kanti Prasad Mentioned, you can try creating another table with the new structure and populate the data;
Avatar of patd1

ASKER

Thanks for your suggestions.

I cancelled the query after waiting for too long, but now it is in "cancelling" mode for a long time. Is there a way i can make it cancel faster so I can work on creating new table /move data and rename as suggested.

Thank you again.
Not without compromising the data.
Cancelling the operation might have been worse than allowing it to finish. All changes made up to now need to get rolled back - a more costly operation than the operation itself usually.
Avatar of patd1

ASKER

The disk IO was only showing 5 million, while the whole table is 80 million rows. Completing it would have take a couple days at that speed.
Always difficult to tell whether you should cancel or let go,  but in that case I would have cancelled too probably. Be prepared to wait at least twice the time it took to get there for rolling back.