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 need help for a better approach on how to approach this.

Currently we have a table that is huge about 15 million records. We plan to create an index on this table however due to large size, we decided to delete records first and then create the index. Is this the right approach?

Badly needed an advise asap!

share|improve this question
    
I've provided an answer but some questions may help: Is this a clustered or non-clustered index? Does this table get truncated/reloaded every day anyway? Are you planning on deleting data after wards anyway? What is the problem you are trying to solve by deleting first? There is probably another way around this. Can you describe the existing indexes on the table? And really no matter what your answers are to these questions, my answer still stands (unless the delete is happening anyway). You don't need/want to do this. But based on your answers there may be additional suggestions. –  Mike Walsh Nov 17 '13 at 14:48
    
So my questions were because I misread your question. The answer is still the same (No in just about every case - unless you are deleting anyway) - I expanded that. I don't think I need the answer to the questions above any longer. Except for perhaps "What is your goal/thought process on the question?" –  Mike Walsh Nov 17 '13 at 14:56
    
I plan to create a primary key non clustered index on the table. The table gets inserts on a daily basis. deletes are done on weekly basis. and yes, the new index is needed for better querying. The plan is to delete about 5 Million records, so when i create the index, it will rebuild to 10 million records only. I am not sure if what i am doign is the best approach for this kind of situation. –  user2885241 Nov 18 '13 at 10:49
add comment

1 Answer

up vote 4 down vote accepted

Really short answer: No.

Longer Answer:

A few thoughts to help you out here:

  1. 15 Million is a decent size - but it isn't impossibly huge.
  2. I presume you are creating the index to better query it. So you care about the data inside of the table, right? So if you delete it you will have to just add the data again after the delete, now on a table with an additional index so that insert will be longer than it probably would have been.
  3. Would you get rid of all of the data? If so, I wouldn't try and do a delete statement in one big fell swoop on a table of that size. It will all be logged and it will be slow and will impact your server's performance potentially worse than an index rebuild. If you are truly removing every row - a Truncate would be better IF you were to do a delete first.

So basically - my thought process is - no don't delete first.. There are always caveats, though.

Update

I answered like I thought you asked creating an index, not rebuilding. My answer doesn't change in fact it becomes simpler - you still don't need to do this. In fact the point of an index rebuild is to reorganize the index and remove index fragmentation. If you remove all the rows in your table before hand, it sort of defeats the need for the rebuild in a sense, right? So you can make your index rebuild faster or reduce the impact.

If in Enterprise Edition you can do this as an online operation to reduce the impact to the users. Watch transaction log growth, though.

You can choose to Reorganize instead of Rebuild. There are caveats here though - you aren't fully taking care of all levels of fragmentation, it can actually take longer, but it can have less of an impact.

You can change the degree of parallelism for the actual rebuild operation to take advantage of parallelism.

You can use a script like this one from Ola Hallengren to actually see if you even need to rebuild first and then only rebuild if you need to.

But no.. You don't ever need to empty the table first - unless you plan to empty it afterwards anyway.

share|improve this answer
    
I updated my answer. I misread your question and answered the wrong question. Answer is still the same but for different reasons :-) –  Mike Walsh Nov 17 '13 at 14:55
    
You were right. It was a waste of time to delete records. The alter table with new index was faster without deleting records. –  user2885241 Nov 20 '13 at 23:27
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.