INDEX IS NOT USED

Posted in 1999

Marichamy, Udaykumar — Fri, 23 Apr 1999 09:46:31 -0500 — source: Informix-list mailing list archive (1991-1998)

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

↪ replying to Marichamy, Udaykumar

Doug Agnew — Fri, 23 Apr 1999 15:42:36 GMT — source: Informix-list mailing list archive (1991-1998)

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....

↪ replying to Marichamy, Udaykumar

Imran Hussain — Fri, 23 Apr 1999 11:42:54 -0400 — source: Informix-list mailing list archive (1991-1998)

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

↪ replying to Marichamy, Udaykumar

Carlson@WHSmith — Fri, 23 Apr 1999 11:50:25 -0400 — source: Informix-list mailing list archive (1991-1998)

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

↪ replying to Marichamy, Udaykumar

Art S. Kagel — Fri, 23 Apr 1999 13:25:48 -0400 — source: Informix-list mailing list archive (1991-1998)

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