Adding indexes to every column has two unintentional side-effects which will be noticed by the application end users. First, some of your queries can actually slow down! It may be faster to not have an index in place and let Oracle perform a full table scan. It is a myth that indexes will automatically improve performance. In many cases, an index decreases performance. So which columns should you index? A thorough analysis of your application's SQL statements against your table's data is required to be able to answer that question. If your company does not have the expertise to perform this work, then you may look at hiring a consultant.
Second, every INSERT or DELETE statement on a table needs to modify every single index on that table. An UPDATE statement will only need to modify those indexes that pertain to the columns that were changed. By indexing every column of every table, you will be asking the Oracle database to perform more work just to satisfy your INSERT, UPDATE, and DELETE statements. Surely, performance will decrease.
Hopefully, I've left you with the impression that indexing the entire
database is not a good idea. So I won't talk about how much extra space
this may require. If you still need further information, feel free to
submit to Ask The Expert again, or hire a consultant, or hire me!!
This was first published in November 2003