RE: INDEX IS NOT USED

Probably your server has in the ONCONFIG the value OPTCOMPIND=2 or 1 You can set this variable in your own session --> export OPTCOMPIND=0 therefore you are telling the optimizer to use indexes when it can If you have Ver. 7.30 or later you can use hints in the query and also influence the way the optimizer behaves. The reason why it uses the index when you modify the select list is that the optimizer (which is not so dumb) thinks it is a good path to read the index and get all the data it needs on one step (it does not read the table). > -----Original Message----- > From: Marichamy, Udaykumar [SMTP:U_Maric@PRNINC.com] > Sent: Friday, April 23, 1999 11:47 AM > To: informix-list@iiug.org > 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 >