Re: SELECT on part of a date field

On 30 Jan 2002 11:12:06 GMT, Zorglub <no_at_mail> wrote:
>Hello, here is my select statement :
>SELECT * FROM ACTUALITE WHERE TO_CHAR(DateCrea,'MM')=1 AND TO_CHAR
>(DateCrea,'YYYY') = 2002 ORDER By Categorie, DateCrea DESC
>
>DATE_CREA is a field of the table ;-)
>
>Is it the best way to search for the part of a date (I mean, the fastest !)
>
>Thanks

Any time I'm compressing a field with a large number of values (i.e. all possible dates in January, or 2002) into a small number of values (i.e. Jan, Feb, Mar..., 2000, 2001, 2002), I look at function-based indexes. You could make one on the 'MM' calculation and another on the 'YYYY' calculation. If this is a copy of your data used just for analysis (rather than being the real table handling the transactions), you could even make function-based bitmap indexes which would make multiple AND conditions on these columns very fast.

9i also has the EXTRACT function whose purpose is to pull out just part of a date.

I haven't timed these techniques specifically for date calculations, but since my table where I do BETWEEN, GROUP BY, etc. on things like TRUNC(date_field) is getting up near 10**7 rows, I might be interested in the same answer soon.

John

--
Got an Oracle database question?
Try the search engine for the database docs at:
http://tahiti.oracle.com/