Re: Datetime column index access

Curtis Crowson wrote: > >"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. Not to worry. It was not taken as an insult. >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. It does exactly what I told it to do, and it does it poorly - hence my note about the explain plan. I gave the possible solution that I did based on the original poster's example and multiple references to 'year to day'. In retrospect, a look at the bigger picture would have been better as evidenced by the solutions provided by Mark, Art, and yourself. (I do sometimes look at things too narrowly. I'm working on that. :-) >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. I would expect this particular optimizer directive solution to be painfully slow - but it was a guess. Thank you for providing the details of your tests. -- June Hunt _________________________________________________________________ Find high-speed 'net deals ' comparison-shop your local providers here. https://broadband.msn.com sending to informix-list