3

I want to modify a column on a huge table containing more than 25 million records. I am using sybase 12.5.4. I have read through these posts

What is the most efficient way to alter a column definition in a table with millions of rows

and

ALTER TABLE on a large table with an indexed column

My current strategy is

  • Drop all non clustered index
  • Drop clustered index
  • Alter table modifcy column varchar (32) -- column is char(20) presently
  • Recreate clustered index
  • recreate non clustered index

Does it make sense. Is there a faster method do it ?

Edit :

  • Table Data Size : 16.7 GB
  • Total rows : 25835155
  • avg row size : .64 KB
  • 8 Non Clustered Index
  • Yes,column I am changing is the second column in a 2 column NC index
1

You should only need to drop and recreate the affected NC index.

Saying that, on a test server, you can see the differences in doing this compared to your strategy of dropping all indexes above.

I reckon dropping/creating the single index would be quicker overall because the data will be shifted around twice otherwise: once char to varchar, another to build the clustered index. Then you have the NC creation overhead.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.