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
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.