Datetime column index access

Hi everybody, I have a table with a datetime column (year to second) and I created a index on that column. When I try the next sentence: Select * from table1 where extend(date_a, year to day) = today (or any date, datetime(year to day) value) The access is sequential. How can I do to access that column in indexed path? Of course, if I alter the column (year to day) the index will be used, but in this case table structure cannot be modify Thanks! Octavio G'mez sending to informix-list

Octavio G'mez wrote: > I have a table with a datetime column (year to second) and I created a index > on that column. > > When I try the next sentence: > > Select * from table1 where extend(date_a, year to day) = today (or > any date, datetime(year to day) value)> > The access is sequential. > > How can I do to access that column in indexed path? > > Of course, if I alter the column (year to day) the index will be used, but > in this case table structure cannot be modify If you are using IDS 7.3, or IDS 9.2 or higher (I think I got those version numbers right), you could specify an optimizer directive with your SELECT statement. For example: SELECT {+INDEX (TABLE1 <index_name>)} * FROM TABLE1 WHERE EXTEND(date_a, YEAR TO DAY) = TODAY The Guide to SQL: Syntax manuals describe the optimizer directives. Just a side note - look at the explain plans. In a very small test that I just ran, the estimated cost was higher when I forced the use of the index. The test was too small to make a difference in the actual run time, but... it might be worth keeping track. -- June Hunt

On Wed, 21 Jan 2004 14:21:27 -0500, Octavio G'''mez wrote: try: Select * from table1 where date_a between extend(today, year to second) and extend((today+1), year to second); Art S. Kagel > Hi everybody, > > I have a table with a datetime column (year to second) and I created a index > on that column. > > When I try the next sentence: > > Select * from table1 where extend(date_a, year to day) = today (or > any date, datetime(year to day) value)> > The access is sequential. > > How can I do to access that column in indexed path? > > Of course, if I alter the column (year to day) the index will be used, but > in this case table structure cannot be modify > > Thanks! > > Octavio G'''mez > sending to informix-list

Octavio G'mez <sogomez@hotmail.com> wrote in message news:<bumkj2$kaj$1@terabinaries.xmission.com>... > Hi everybody, > > I have a table with a datetime column (year to second) and I created a index > on that column. > > When I try the next sentence: > > Select * from table1 where extend(date_a, year to day) = today (or > any date, datetime(year to day) value)> > The access is sequential. > > How can I do to access that column in indexed path? > > Of course, if I alter the column (year to day) the index will be used, but > in this case table structure cannot be modify > > Thanks! > > Octavio G'mez > sending to informix-list You could use between: select * from table1 where date_a between datetime(midnight of day) datetime(11:59:59 pm of same day)