By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Best way to index a mostly static database

P: 6

I am extracting data from an operational system in order to maintain a mirror that can be used for reporting.

I have two means of filling this database:

1) A stored procedure which truncates all tables and rebuilds everything from scratch. This takes about an hour and will be run periodically.

2) A stored procedure that updates my database with the previous days activity which will be run daily. This will DELETE and INSERT but not UPDATE records.

All other interactions with the database are SELECTS.

First off, I am not currently using primary keys in this database as the data, once imported is static.
All rows SHOULD be unique, but I cannot guarantee this and do not want to risk my import failing due to any such constraints.

What sort of index should I be using here and what is the best way of maintaining it? For instance, when doing a complete rebuild, can I leave rebuilding any index until after all the data has been imported?

Hope the above makes sense. I have done a fair bit of searching on index types but it seems to get very complex very quickly!
Aug 26 '09 #1
Share this Question
Share on Google+
1 Reply

Expert 2.5K+
P: 2,878
What's wrong with implementing number 1?

If your main database gets updated, and you only do delete and insert on your mirror, you'll have data inconsistency?

If you truncate the table, all indexes are still there and will be used as you update the tables. You can either drop the index or drop the table and rebuild them later.

Either way, you need to rebuild your index to make sure the optimizer know the statistics of your table.

Good luck!!

--- CK
Aug 26 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.