Re: INFORMIX SE AND INDEXING

->From: harry@boi.hp.com () ->Subject: INFORMIX SE AND INDEXING ->Date: Tue, 29 Mar 1994 17:11:36 GMT ->Reply-To: harry@boi.hp.com () ->Organization: Hewlett-Packard / Boise, Idaho -> -> With Informix SE, over the course of the last few months, we have ->been experiencing substantial performance degradation. One thing that ->has been done is adding more indexes to tables. -> -> What are the effects of adding indexes? How does this change the ->performance? Is the order of index generation important? -> -> It is the reporting aspect that has been hit the hardest. ->Unfortunately, all of the indexes are used to aid in reporting and all ->of the indexes are used. -> -> Online is not an option. Any help would be appreciated. -> ->Harry Lynch ->harry@hpdmm43.boi.hp.com Multiple indexes do impose an overhead, but I would have expected it to be most noticeable on updates, rather than during reporting. Here are a few tricks that I have found: 1. If you need index 1 on columns a, b, c, and index 2 on columns a, b, then just have index 1. With both, you pay the cost of maintaining index 2, but it will seldom or never be used. This is because Informix can use the LEADING columns of a multi-column index as tho' they were a separate index. 2. If you have one index that is the most frequent path thru your data, then you should run the SQL statement ALTER INDEX idx_name TO CLUSTER;This will physically reorder the data to follow the index order, which will in turn maximize the benefits you get from any bulk read look-ahead that your O/S will allow. The disadvantages of this technique are: a. You need enough free disk space for a second copy of the table being clustered (and its indexes?), since Informix will build a new table from the old one, and then delete the old and rename the new. This also changes the tabid number of the table. b. You need to rerun the process periodically, after records are added and deleted. The clustering is a one-time thing and is not maintained over time. The rebuild is a two-step process: ALTER INDEX idx_name TO NOT CLUSTER; { no physical change yet } ALTER INDEX idx_name TO CLUSTER; { this physically changes the table } 3. An alternative to the ALTER INDEX approach would be to unload the table to a file ordered by the index, delete the table contents, drop the indexes, load the table from the file, and rebuild the indexes. I am sure that this would be less efficient than ALTER INDEX, but it has the dubious benefit of preserving the table's tabid number. 4. Dropping and recreating indexes seems to improve performance a little. Apparently this improves the contiguity of the indexes (subject to O/S free disk space limits), but at less cost than completely restructuring the table as in 2 or 3 above. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\