>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')