We are running Oracle 8.1.7 on Solaris 8 which has 4 GB memory. One person from the group wants to index the whole database. Is this advisable? If not, what would be an appropriate answer to her? Let's suppose we did index the whole database, and the database was 10 GB in size -- how much extra HD space am I looking at in comparsion to whole DB indexed vs. non-indexed.
I'd be leery of anyone who said that they wanted me to "index the whole database". This type of statement leads me to believe that they do not understand exactly what it means to index anything in the database. You cannot index the entire database. You can only index columns of a table. So does this statement mean that they want to create an index on each and every column of each and every table in the database? That's a recipe for disaster. One should only be indexing those columns that make the application go faster. These columns should be few compared to the total number of columns in all tables in your database.
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!!