Re: Can I avoid the sequential scan?

Spetc@aol.com wrote: > > 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? Did you try: select * from dates_table where date_field between EXTEND("1996-05-05 00:00:00", YEAR TO SECOND) AND EXTNED("1996-05-05 23:59:59", YEAR TO SECOND) You should get same output, and the optimiser might be able to use the index. This is untested, if you haven't guessed. //////////////// ======================================================= ////////// // Dennis J. Pimple Informix Software, Inc. ////// / /// Principal Consultant 5299 DTC Blvd Suite 740 ///// // //// dennisp@informix.com Englewood CO 80111 //// // ///// /// // ////// recept: 303-850-0210 // // /////// direct: 303-740-5611 Opinions expressed are mine, / /////////// fax: 303-843-6408 and do not necessarily //////////////// http://www.informix.com reflect those of my employer