Re: Informix Optimizer Question

In article <344CB7E6.6A3C@sympatico.ca>, "james.woodger" <jwoodger@sympatico.ca> writes >I wonder if one or more people who are familiar with the Informix >Optimizer could comment on my statements below (I'm trying to generalize >these statements to apply to any of the big RDBMS's). Are these >statements essentially valid for Informix? > >Indexes that do not signficantly improve performance of important >transctions should be avoided. To help identify non essential indexes, >consider the following: >' the database will not always use an index, especially if it has a >better index available. For example, consider what happens when the Depends, Informix tends to use the first viaable index, at least SE 4.x does. But yes avoid indicies with the same leading columns where the common leading columns provide a unique or near unique key. >user provides two values for a search and both these columns are >indexed. If one of the indexed columns is near unique, the database >will probably only use the near unique column index to perform its >search. >' if the table has a small number of rows, the database will often find >it faster to simply scan the entire table and not bother using the >index. The exact number of rows where an index is unnecessary varies by >database product and the number of table columns but the cutoff is >usually somewhere in the 100-500 row range. However, if the Dynamic Hash Join method is available (Informix Online 7.x), it may deciede to read the small table into memory and build a hash table out of it. Then it would SCAN the large table, hash the join columns and look for them in the hash table. I prefer to always create indicies. Unique indicies help to enfore uniqueness and most small tables tend to be reference tables with a single column unique key and are accessed via that key. >' Indexing columns that can take on a small number of evenly distributed >values will not help the database when searching. The rule of thumb is >that an index will only help the database if it eliminates at least 90% >of the result set. An evenly distributed Yes/No flag is an example of a The exact percentage varies with row and key size but yuo get the idea. >poor index since it eliminates only 50% of the result set, on average. >The database would not use such an index because it is faster for the >database to simply scan all the rows. > >P.S. if you notice any significant omissions, I'd appreciate feedback on >that too. -- David Williams