Re: Could you elaborate on your comment about the timepart?

Hi Peter,

Here's the story:
Years ago I was working on a particular application. In this application there were a few large tables where for some records time was important and for some records time was unimportant.
In an ordinary select, assuming on every day you have records with and without time the following problem will show up: I have select * from table where date_column = '<date-value>' 'Date-value' implicitly equals to 'date_value'||'00:00'. This meant in this particular situation,
if you didn't either wrote 'trunc(date_column) or wrote 'date_column between 'date_value' and to_date(date_value)+ 1- 1/3600 /* 1 sec before midnight ! */ your query results would be always incorrect, because you were selecting the midnight records of that day only, and there were midnight records (app was tracking amongst others radio and television commercials) almost always! This is way I said: either make sure there's no (random) time (you must be sure your frontend doesn't derive the time form the sysdate), or take into account there is a time.
For indexes on date columns all 7 bytes of a date column are being indexed, so including the time portion.

Hth,

Sybrand Bakker, Oracle DBA

Peter Laursen < ptl_at_edbgruppen.dk> wrote in message news:01bed1db$7c31e400$2c289a0a_at_apollo...
> Hi Sybrand
>
> I didnt understand this bit, but it sounds interesting:
> > General note: Make sure there is either no time in your date field, or
in
> > all your where clauses take into account there is a date in your field.
> This
> > results in
> > between ... and to_date(date_variable2) + 1 - 1/3600
>
> Well actually the four above lines dont make sense to me at all.
> Could you elaborate on them?
> How are indexes on datefields organized? Values are almost unique, as in
> very few are equal to the last fraction.
> Indexes ofcourse take into account the hole date/time part and not only
the
> date?
>
> Thanks
> Peter
>
>
>
>
Received on Mon Jul 19 1999 - 08:17:03 CDT