Try oncheck -ci database:table or oncheck -cI database:table, if you think you need to rebuild an index. Run update statistics afterward. As to the original question:

I think that a program, user, etc. were doing searches on items that were not index keys, creating sequential scans and degrading performance. Your DBAs added indexes matching the way the table was being accessed and boosted performance. Remember that a unique index covering say serial_number and product_date will not help at all if you're searching by product_code or even product_date alone. Numerous sequential scans can degrade all kinds of performance, not just reads.


--EEM

Original Original message:

Hello,

we have a database table with 7 columns and approximately 10 million rows. There is a unique index on 6 columns. The customer with the 10 million rows had problems with his system because the INSERTs were very slow. Yesterday, his database administrators added 2 new indizes.. Now, the perfomance is much better.

Why??

I always thought a unique index is the best way to optimize the DB.

Thanks, Holger


-----Original Message-----
From: Ben [mailto:ben_informix@comcast.net]
Sent: Thursday, July 07, 2005 9:15 AM
To: informix-list@iiug.org
Subject: Re: Index problem

I don't think that update statistics recreates the index...better check the manual.
*
*
----- Original Message -----
From: Prateek.Jain@ril.com
To: scottishpoet ; hsteinestel@web.de
Cc: informix-list@iiug.org ; owner-informix-list@iiug.org
Sent: Thursday, July 07, 2005 8:19 AM
Subject: Re: Index problem

You can use the update statistics tablename utility of informix
that would recreate your index file.
and one thing is clear that index always slow down the insertion
so it would be better that you use update statistics once in month on that particular table

Regards,
Prateek Jain
Reliance Industries Limited
(M) - 09426387793
(O) - 079 - 30215010 (Ext - 383)

sending to informix-list