I am running a query that returns me a collection of date objects for months between a certain date range. The query works fine, but is very slow (~2 seconds on my local machine, ~30 in our corporate development environment). Here it is:
SELECT ADD_MONTHS(TO_DATE('200804', 'YYYYMM'), -1+rownum) AS MONTH
FROM all_objects
WHERE ADD_MONTHS(TO_DATE('200804', 'YYYYMM'), -1+rownum) <= TO_DATE('200805', 'YYYYMM')
Currently, it will only return one month, but if you extend the second date string, it returns more.
I have two questions. First, why does this run so slow? I know Oracle functions really slow down a query, but this takes about 30 seconds on a development machine at my work.
The second, and more puzzling question: why does the runtime shorten to a fraction of a second when you extend the range to, say, '201805'? I would think that a greater range would take longer. It seems to be the opposite effect.
There is no need to use inline views and I see too much date functions being used. If you skip all that, this remains:
SQL> var START_YM varchar2(6)
SQL> var END_YM varchar2(6)
SQL> exec :START_YM := '200804'; :END_YM := '201805'
PL/SQL procedure successfully completed.
SQL> select add_months(to_date(:START_YM,'yyyymm'),level-1) m
2 from dual
3 connect by level <= months_between(to_date(:END_YM,'yyyymm'),to_date(:START_YM,'yyyymm'))+1
4 /
M
-------------------
01-04-2008 00:00:00
01-05-2008 00:00:00
01-06-2008 00:00:00
<... 116 rows skipped ...>
01-03-2018 00:00:00
01-04-2018 00:00:00
01-05-2018 00:00:00
122 rows selected.
Which looks even easier ...
Regards, Rob.