Re: INDEX IS NOT USED

Uday The first is probably because the optimizer finds it cheaper to scan the table sequentially. That is probably because there are relatively few trans_type and rpt_month (12?) values in the index and the cardinality is not high enough. When you put all the 3 fields of the index into the select list, it finds it cheaper to do a key-only scan meaning it simply reads the index without reading the table, since it finds all information in the index itself. HTH Sujit "Marichamy, Udaykumar" <U_Maric@PRNINC.com> on 04/23/99 07:46:31 AM To: informix-list@iiug.org cc: (bcc: Sujit Pal) Subject: INDEX IS NOT USED Hi All, Can anyone help me with this index issue.The table TRANS has a duplicate index on (trans_type, rpt_month, fieldx). The where clause has equality searches on fields trans_type and rpt_month. So, the query should use the index right? Wrong. It does a sequential scan of the TRANS table, except when the SELECT list is modified to include only the fields from the index. THEN it does an index scan. What is going on here? Uday