Re: Date fields and indexing question???

From: Naresh Ramamurti <nramamur_at_worldnet.att.net>
Date: 1996/09/06
Message-ID: <3230F2AB.3A21_at_worldnet.att.net>#1/1


Bruce Douglas wrote:
>
> Is anyone aware of any special requirement involving date fields and an
> index?
>
> I created a one-column index on a fairly large table involving a date
> field. Then I tested a multi-table join involving the table with a where
> clause that included TIME_PERIOD = '01-SEP-1996'.
>
> Although I realize the index is not especially selective (it's always the
> first day of the month), here's what surprised me. The query runs many,
> many times long with the index than without. Furthermore, the TKPROF stats
> show some huge number of rows scanned, many times the actual size of the
> table.
>
> In fact, the query runs faster if I disable all the potential indices than
> it does with the date-field index on.
>
> Any clues?
>
> Thanks in advance for your help...
>
> cheers--bruce
>
> -------------------------------------------------
> Bruce Douglas -- bruce.douglas_at_bridge.bst.bls.com
> BellSouth Telecommunications
> -------------------------------------------------

--
The problem you have encountered is rather unusual. The index on the
date field
is causing a problem for certain specific dates that have the TIME
portion zeroed out.

e.g 01-SEP-96 might be stored as 01-SEP-96:00:00:00. I might be wrong
here, but the
first day of the month is a potential candidate for dates that might
have come over 
from a conversion program, unlike other dates that were loaded as date
fields from 
an ORACLE user interface or otherwise. The problem will not occur for
dates that are
stored with a time different from 00:00:00 (hh:mi:ss). 

More often than not, queries of this nature work better without the
index. That does 
not however mean that you need to drop the index ( which might be useful
for other
programs). The best way to get around this problem will be to use the
to_char function.
Try to_char(<field_name>,'dd-MON-yy:hh:mi:ss') if the time is important.
If the time 
portion is not important you'd be better of doing a trunc(field_name).

Hope this helps,

-----------------------------------------------------------------
Naresh Ramamurti 			nramamur_at_worldnet.att.net
-----------------------------------------------------------------
Received on Fri Sep 06 1996 - 00:00:00 CEST

Original text of this message