Re: SQL squential scan on an indexed table
This was posted yesterday. We had the same problem and had come to the
same conclusion - use UNION. But, since it used to work in version 6, does
anybody know if it was turned in as a bug, if/when it will be fixed, or do
we have to live with it?
Thanks, Dianne
______________________________ Forward Header __________________________________
Subject: Re: SQL squential scan on an indexed table
Author: David Williams <djw@smooth1.demon.co.uk> at INTERNET
Date: 7/22/97 4:58 PM
In article <01bc969c$60c7c260$3940a8c0@steve-gould>, Steve Gould
<sg@leaderflush.co.uk> writes
>We are running DSA V7.22 on HP-UX 10.20.
>I've found that running the following query through
>Informix SQL version 6.05 sequentially scans the ledger
>table even though there is a composite index on cust_supp, lereference.
>It only recognises the index when I take out the OR conditions in the
>WHERE clause. It ran perfectly well under ISQL V4, has anyone any
>ideas of whats wrong ?
>
>select ledger.lecurcode,
> customers.cust,
> ledger.letaxdate,
> ledger.lereference
> from customers, outer ledger
> where customers.cust = ledger.cust_supp
> and (ledger.letype in ("SI", "SC", "JS", "RE", "SD", "DS", "SE", "SR")
> or (ledger.letype = "JN" and ledger.lesource in ("CRN", "SCE"))
> or ledger.letype is NULL)
> and ledger.letaxdate <= TODAY
> order by lecurcode, cust, lereference, letaxdate>
>Thanks,
>Steve Gould
This is correct. If you have an OR in your query then indexes are not
used. Try converting the OR to a UNION of several queries.
--
David Williams