"Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.
You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.
In this article we will demonstrate a few methods to generate the row-source without creating any additional table or PL/SQL procedural code and create meaningful series of dates which can be used in the application. CUBE takes a specified set of grouping columns and creates subtotals for all of their possible combinations?? If n columns are specified for a CUBE, there will be 2 to the n combinations of subtotals returned.? So, for the months having 30 days or 28 days or 29 days, the sequential values added will exceed the last day of current month and will generate rows for next month as well. Also note that Oracle Date functions (and not exactly our logic) will now take care of the number of days in a month.
is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. With the above usage in our mind, we can easily conclude that if we pass 1 as ? Even if the year is a leap year, Oracle will determine the appropriate number of days.
I have a SQL query that takes a date parameter (if I were to throw it into a function) and I need to run it on every day of the last year.
How to generate a list of the last 365 days, so I can use straight-up SQL to do this?
Otherwise, the result has the same day component as ? Also, we need to modify t he ADD_MONTHS and TRUNC functions to calculate the number of rows for a year. the leap year consideration) will be taken care by Oracle.
Obviously generating a list 0..364 would work, too, since I could always: Personally, if an application has a need for a list of dates then I'd just create a table with them, or create a table with a series of integers up to something ridiculous like one million that can be used for this sort of thing.
Posterity nothing; that helped me figure out some bizzarely difficult to track down sql commands for TD.