Re: SQL squential scan on an indexed table
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