How to decide whether to create indexes
Posted in 1999
Hi Database/Informix experts, I'm working for a data warehouse running on Informix dynamic server 8.2 in an SP2 environment. There are several hundred tables and among them about 35 are above 1mln and max is about 45 mln recs. One thing I couldn't believe in this environment is they are not using indexes at all. Not even primary key indexes. The reason is "No use of indexes. Bcos, in most of the data requirements, we use all of the rows in a table" (This is also not always true). During the day time while the data is being retrieved for various data requirements the machines become very low responsive. I know it is difficult to answer my questions without really looking at the usage of the data. But I appreciate if some one can generally answer the following questions I have. 1. Even if all the rows of a table are selected, will not indexes have any effect on "order by" or "group by" clauses? 2. Is there a minimum no of rows for a table if not shouldn't create an index? 3. Don't you think this environment need to consider indexing? 4. Is there a special deviation for indexes in Dynamic Server 8.2/SP2 environment? 5. What are the exact factors need decide whether to create indexes or not? Thanks a lot in advance.