Re: table containing the days for a week

On Sat, 23 Apr 2005 04:36:44 +0200, "yves ludwig" <yve_at_free.fr> wrote:

>In one of my application, I need to have a table containing all the days of
>a week.
>
>Therefore, I created a table myCalendar containing one row / day for the
>last 10 years and the next 30 years and perform the following select
>
> SELECT myDate from myCalendar where TO_CHAR(myDate,"IW")="16" and
>TO_CHAR(myDate,"IYYY")="2005"
>
>that returns
>
> 18/04/2005
> 19/04/2005
> ...
> 24/04/2005
>
>
>I have two questions
>
>(1) How could this select be improved ?
> I think that Oracle (or any DBMS) needs to generate the strings for my
>40 years and search the "16" and "2005" values.
>In my application, I currently have added in the myCalendar table two
>columns : myYear and myWeek containing the numeric values that are
>calculated only one time : during the creation of the table. Therefore my
>select is currently
>SELECT myDate from myCalendar where myWeek=16 and myYear=2005
>
>(2) Is it possible to work without any 'myCalendar' table to obtain the
>result ?
>
>
>I remember I read an article concerning this topic in an old Oracle Magazine
>a few years ago (during winter 2002 or 2001)... {one should really keep
>everything).
>
>
>
>
>Thanks for help & best regards, Yves
>
>

1 Put a function based index on mydate
create index .... on mycalendar(to_char(mydate,'IYYYIW')

make sure init.ora query_rewrite_enabled=true and query_rewrite_trusted (from memory) = enforced

2 Do away with the workaround, and put function based indexes on the affected tables directly.
You don't NEED the auxiliary table.   

--
Sybrand Bakker, Senior Oracle DBA