Re: dates in index

On Thu, 27 Jan 2000 00:49:18 GMT, jxs_at_walaw.com (Jay Scheiner) wrote:

>I have a table with a date, where I store data with both date & time
>info (1/1/99 3:30pm). I have in the range of 50-150K records per day.
>I would like to be able to select records for just a day or range of
>days without doing a full-table search.
>
> What is the best way to index this? Should I make a separate column
>with just the date? I would like to keep a column with both the date
>& time together for queries that look at date/time in a sequence.
>Total disk space isn't a big issue at this point.
>
>So far I can do 'select * ..... where trunc(date_column) = '1/1/99';
>I am not sure if this is using the index or not, I don't have enough
>days data in there, and it is a fast machine.
>
>Also, if I was using MS Access as a front end on this, I can't do a
>query with group by date, because each time makes a different value.
>Access doesn't have 'trunc', and using VB's format(dt,"mm/dd/yy")
>takes forever because it reads a row and formats it on the client, I
>think.
>
>Thanks in advance for any help on this.

Your select will not use the index.
You need to make sure you use something like ' date_column between trunc(to_date('26-01-00')) and trunc(to_date('26-01-00') + 1 - 1/3600' (or any other construct taking the whole 24 hours into account)

Your problem with Access is a typical feature of ODBC. Any unknown function will force ODBC/Access to resolve it on the client. Note: in previous releases any function that was not identical was treated this way.

Hth,

Sybrand Bakker, Oracle DBA Received on Wed Jan 26 2000 - 15:54:26 CST