INDEX IS NOT USED
Udaykumar Marichamy found a query on table TRANS with equality tests on trans_type and rpt_month did a sequential scan despite an index on (trans_type, rpt_month, fieldx); it used the index only when the select list held index columns alone. Doug Agnew, Imran Hussain and Art Kagel advised updating statistics (MEDIUM or HIGH); Doug noted many selected pages may favour a table scan. John Carlson asked for the SQL and SET EXPLAIN. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
Marichamy, Udaykumar wrote in message <7fq2tb$4jg$1@news.xmission.com>... > >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 > > 1. First, I would suspect that update statistics needs to be run ...... 2. If it has, then I would wonder if the selection clause on the index fields is causing a high percentage of the pages to be selected (based on clustering ratios). If so, then the optimizer may be recognizing that it is cheaper to read the table directly (one read per page) then to use the index (maybe 3 or 4 reads per page, depending on index depth). Just some guesses....
Hi, Try updating the statistics. Imran. =========================================== Imran Hussain MCP, MCSD imranh@imranweb.com FOR FREE Software http://www.imranweb.com/freesoft =========================================== Marichamy, Udaykumar wrote: > 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
Could you post a copy of the SQL statement and the SET EXPLAIN query plan? John Carlson Informix DBA WHSmith USA Marichamy, Udaykumar wrote: > > 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
Marichamy, Udaykumar wrote: > > 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? Have you updated statistics MEDIUM or HIGH as recommended in the release notes and the performance guide? Art S. Kagel