Re: Can I avoid the sequential scan?

You don't indicate what type date_field is, but I imagine that it is a DATE. If it is a DATE, then applying DATE to it does nothing. Because you are comparing the date with strings, each date is being converted to a string, and the optimizer doesn't cannot predict how the result is going to compare, so it has to check all possible dates. You need to coerce your strings into dates, using the DATE function: SELECT * FROM dates_table WHERE date_field BETWEEN DATE("050596") AND DATE("050596") This at least makes it clear what you are doing and leaves the optimizer a chance of converting the query into something which uses an index on date_field. That only leaves us with the question 'Why are you doing a range search when the end dates are the same?' I assume that is a limitation of the example rather than what normally happens. If date_field is a CHAR field after all, then you need the DATE conversion on date_field too in the general case where the end dates are not the same; otherwise, if you were testing for values between '050596' and '060696' then you'd get values like '050597' included in the range because, when looked at as a string, '050597' is greater than '050596' and smaller than '060696'. But in this case, you'd probably get a sequential scan again! Also, your date values are ambiguous, in general, and can be broken if someone has DBDATE set to a value you didn't expect. The unambiguous way of specifying a date uses MDY(mm, dd, yyyy). Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> >From: Spetc@aol.com >Date: Fri, 28 Jun 1996 13:58:29 -0400 >X-Informix-List-Id: <list.10548> > >I run the following select (Online 5.07.UC1) : > >select * >from dates_table >where date(date_field) between>"050596" and "050596" > >date_field is defined as datetime year to second and is indexed. > >I get a sequential scan, which kills performance. I'm sure this is because >of the "date" function in the select. > >Can I achieve the same result with a different select, this time encouraging >the optimiser to use the index?