RE: INDEXING STRATEGIES

Art, The table in question is on an AIX 4.3/IDS 7.31 UC2 platform. Some of the indexes are composite, some are single. I am going to combine several single indexes to several composite indexes. I hope that will be more efficient and require less disk space. Any more tips/suggestions are welcome. Thanks, Uday -----Original Message----- From: Art S. Kagel [mailto:kagel@bloomberg.net] Sent: Wednesday, July 21, 1999 12:21 PM To: Marichamy, Udaykumar Subject: Re: INDEXING STRATEGIES Yes IDS can only use one index per table in a query unless you have the Extended Parallel Option (XPO) which can actually use multiple indexes under certain conditions. If these indexes are all single column indexes consider replacing them with several multi-column indexes that support your most common queries allowing filtering at the index level by several keys. Note versions before 7.24 will only user the first three columns of a multiple column index of four or more columns. This was a code OPTION the optimizer was given if it detected dimishing returns from further index filtering on lower priority key columns but a bug in the code, fixed in 7.24 & 7.30, causes this option to be selected for all queries. I mention this because you did not state your version or platform information. Once you have created the new indexes do not forget to update statistics based on the recommended method in the release notes and V7.3 performance guide. Art S. Kagel "Marichamy, Udaykumar" wrote: > > I have this fat table (160 byte), currently with about 2.3 million rows > (expected to grow to about 30 million rows) and having 12 indexes, some > composite, some single. There is no unique index. This table has no field or > fields which guarantee uniqueness. > > Running some SELECT queries on this table, with 'set explain on', I found > that Informix uses only one index, even if the filters are on index columns > from multiple indexes. This means that the other indexes are wasting disk > space. > > Question: Is there a significant performance gain to having multiple indexes > used? If so, how can Informix use multiple indexes? > > If not, it seems to me that instead of having so many indexes, it makes > sense to just keep the indexes with the most often used filters and delete > the other indexes. > > Any suggestions? > > Thanks, > > Uday