RE: How to decide whether to create indexes

It seems a strange concept from where I come, not to put indexes on. But it also does save ever running update statistics. See below: Murray Wood -----Original Message----- From: Vijith Gunawardhana [SMTP:vijith.gunawardhana@wcom.com] Sent: Thursday, September 16, 1999 8:47 AM To: informix-list@iiug.org Subject: How to decide whether to create indexes 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? [Murray Wood] Indexes will most likely help these. Otherwise Informix needs to do a select into temporary tables then sort. 2. Is there a minimum no of rows for a table if not shouldn't create an index? [Murray Wood] The optimiser makes these decisions. It depends upon the row length, Informix version amongst other factors. Less than 100 rows probably no index is best. More than 1000 then an index. 3. Don't you think this environment need to consider indexing? [Murray Wood] Yes 4. Is there a special deviation for indexes in Dynamic Server 8.2/SP2 environment? [Murray Wood] Dont believe so. 5. What are the exact factors need decide whether to create indexes or not? [Murray Wood] What sqls are you running against the table in question. Each select should theoretically be designed to use an index on weach table, or create an index for the sql. Thanks a lot in advance.