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