Re: Datetime column index access

"June C. Hunt" <june_c_hunt@hotmail.com> wrote in message news:<bumrhn$jpsjn$1@ID-209514.news.uni-berlin.de>... > 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. Warning the folowing is meant to be helpful and is not intended to be insulting. I don't think this does what you want it to do and that is why the cost is higher. It basically scans the index evaluating the function for each node to determine if it belongs in the set. I have a table that I created with 300,000 datetimes and it runs dog slow but the other way see Art's answer and then my follow up run instantly with a cost of 5. While the forced index has a cost of 7301. I know cost doesn't always mean anything but in this case it seems to be correct.