Re: Can I avoid the sequential scan?

Sometimes even the greate and fantastic J.L. isn't quite right. I am quite sure it's meant entirely correct, that it's a question of explaining it, so here goes: johnl@informix.com (Jonathan Leffler) wrote: :You don't indicate what type date_field is, but I imagine that it is a :DATE. He did. They are datetime year to second, but: : 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 isn't quite the issue. Even if they where dates and he did: SELECT * FROM dates_table WHERE date_field BETWEEN "050596" AND "050596" Informix databases has allways been able to use indexes. The literals are probably converted to dates once before the reading of data starts. The issue in the original where clause: where date(date_field) between "050596" and "050596" is the use of a function call on the "left" side of the comparison, that is on the column in the database. As Informix stores only literal values in any index there is no way the engine can use an index. You are asking it to execute a function using the column value as a parameter. The engine correspondingly has to do this for every value in that column, so every row must be read and compared to your literals. The right way is as Dennis J. Pimple wrote: select * from dates_table where date_field between EXTEND("1996-05-05 00:00:00", YEAR TO SECOND) AND EXTEND("1996-05-05 23:59:59", YEAR TO SECOND) or simply: select * from dates_table where date_field between "1996-05-05 00:00:00" AND "1996-05-05 23:59:59" This is an often misunderstood issue as seen from all postings here in c.d.i asking why performance is low in similar cases. Moral: Avoid (if you can) the use of functions in where clauses in such a way that they have to be executed for every row. I usualy think of this as not using functions on the "left" side of comparisons, but truly you should allways be careful in the use of functions on any value read from the database. (PS: Be aware that when Universal Server is released this whole issue *may* be turned upside down. May be you will have to use functions to make use of some smart new indexs.) :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. Because they are datetime year to second. :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? Nils.Myklebust@ccmail.telemax.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company