RE: Optimizing Select count distinct statement

You way want to check you release note on update statistics. When we went to 7.12 on-line I used the same update statistics as discussed below and had some problems with our application not getting queries optimized correctly. What I found in the 7.12 release notes is the following rules for update statistics. 1. update statistics medium for all fields that are not the first element in any index on the table. 2. update statistics high for all fields that are the first element in any index on the table. 3. update statistics low for all fields that are in compound indexes. After following these rules my problems with optimiziation were complete corrected. Hope this helps... Russ..... }---------- }From: staccuc@riq.qc.ca[SMTP:staccuc@riq.qc.ca] }Sent: Friday, February 14, 1997 12:00 AM }To: informix-list@rmy.emory.edu }Subject: Re: Optimizing Select count distinct statement } }On 13 Feb 1997 17:53:03 GMT, kleckab@river.it.gvsu.edu (Barry KLecka) }wrote: }No, field2 is not part of an index, but I ran the UPDATE STATISTIC on }each indexed field of that table. } }>If field2 is part of a multiple field index then run update statistics }>high on that field. I have encountered this with 7.2. It ignores }>a multiple field index when you use only one field. }> }>staccuc@riq.qc.ca wrote: }>: Anyone has an idea on how to optimize this kind of statement }> }>: select count (distinct field1) }>: from table1 }>: where field2 = 'something' }> }>: There is indexes on both field1 and field2, but Informix Online 7.2 is }>: not using them according to the explain plan. }> }>: Any idea ? }> }>: Thank you }> }>-- }>And I still use vi......... }> } }