Re: Date fields and indexing question???
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