Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
How large is the table (in GB), what is the average row size, how many NC indexes and is the column you're changing indexed? –  Mark Storey-Smith Oct 28 '11 at 11:52
    
Edited the questions with answers –  NRS Nov 2 '11 at 6:25
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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