asked
Can you have too many indexes on a table if it will never be updated?
Hello all, I have an old audit table that has recorded all changes made to a database. It is hundreds of millions of rows. I no longer need to update the database, so I will never UPDATE, DELETE or INSERT into this table, but I will need to run SELECT statements on it. There 14 columns, but only 7 of these I would ever want to search on. To speed up the SELECT statements I am planning on adding an index for each of the 7 columns and then adding lots of combination indexes involving the columns. Apart from it taking up some index space, is there anything wrong with doing this? Thanks for your help
This question's really captured my imagination. What has happened that has resulted in a table that will NEVER get updated? Especially once it's got to 100 million rows? I used to come across tables that would only change once a month. That was just the ETL granularity at the time. Can you tell us a bit about what the table measures and why it got so big and yet will NEVER be updated again.
Well I suppose my question isn't technically true. This is an audit table that holds every pre-updated and post-updated value that our administrators make to a record. The data on this table will be moved to another database and then the table will be truncated and then new data will be inserted again into it. So this table will be updated again as new data will be inserted into it. But the new table (where the original data has been moved to) will never be updated again.
If you are certain you will never update the database, the only issue would be size. Adding over a dozen indexes to hundreds of millions of rows will mean potentially doubling (or more) the total storage of the table. I definitely recommend compressing the table and indexes. I generally recommend it anyway, but it's a no-brainer for a read-only table. Finally, on that topic, if the database will never need updates, you can set it to be read only. Just be sure you are ready before doing that: compress tables, create indexes, rebuild existing ones, set recovery model to simple, etc.
FYI, you can't shrink tables. Shrink only applies to database files.
Compression is very different from shrinking. It compresses the data in the table/index. This (slightly) increases the CPU overhead, but it decreases the I/O overhead. Needless to say, sacrificing CPU for I/O is almost always a win.
19 People are following this question.
