Re: INDEX on DATE field.

"uncle.scrooge" <uncle.scrooge_at_worldnet.att.net> wrote:
>
>I'm querying a table, searching for all fields mathching a date
 range.
>So I I have a regular index on my TRANDATE column, and my
 queries
>look like (ie all records for october 5th 2000)
>
> SELECT ...... WHERE TRANDATE >='10/05/2000 00:00:00' AND
 TRANDATE
><'10/06/2000 00:00:00'
>
>How can I avoid
> a) index range scan?
> b) a sort for this index?
>
>Is that even possible? obviously bitmap indexes don't help here
>
>any ideas?
>
>

1) Because you are potentially looking for more than one row in your result set retrieved via a non-unique index then there is no way to avoid an index range scan. Now if you are referring to the fact you only have a partial key since you do not have or seem to care about the time then one possible way to eliminate the time from the select is to store the all the data with time = '00:00:00'. In this case you can now always look for an exact match.

2) What sort are you talking about? There will be a sort required to build the index, but unless you specify a group by or order by on the select since you have an index on the date column there is no sort required to retrieve the data.

I hope this helps.

• Mark D. Powell -- The only advice that counts is the advice that you follow so follow your own advice. --

  ---

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Sun Aug 06 2000 - 00:00:00 CDT