Unusual runtime of date functions in Oracle

Question

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.

Answer 1

Use this instead,

SELECT ADD_MONTHS(TO_DATE('200804', 'YYYYMM'), -1+rn) AS MONTH
FROM (select level rn from dual connect by level < 4000)
WHERE ADD_MONTHS(TO_DATE('200804', 'YYYYMM'), -1+rn) <= TO_DATE('200805', 'YYYYMM')
;

This avoids all_objects which is likely to be different between your two environments.

all_objects is a complex view so will not be as performant as the inline view used above. If you do not want to use the "connect by" syntax then create a table of integers and use that.

Answer 2

Slight variant of Janek's function that gets rid of the arbitrary 4000 month limit by using the MONTHS_BETWEEN() function

SELECT ADD_MONTHS(TO_DATE('200804', 'YYYYMM'), -1+rn) AS MONTH 
  FROM ( select level rn 
           from dual 
           connect by level < abs(months_between(TO_DATE('200804', 'YYYYMM'),TO_DATE('201805', 'YYYYMM')))+2
       ) 
 WHERE ADD_MONTHS(TO_DATE('200804', 'YYYYMM'), -1+rn) <= TO_DATE('201805', 'YYYYMM') 
; 

Answer 3

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.

Answer 4

Part of the difficulty here is that it needs to evaluate ADD_MONTHS(TO_DATE('200804', 'YYYYMM'), -1+rownum) for every row in the ALL_OBJECTS view. If you rewrite the where clause, it will then use a different plan with COUNT STOPKEY instead of COUNT.

Try the query below, instead. This ran quite a lot faster on mine.

SELECT ADD_MONTHS(TO_DATE('200804', 'YYYYMM'), -1+rownum) AS MONTH
FROM all_objects
where 
  months_between(date '2008-05-01, date '2008-04-01') >= rownum

The comment that you made about using 201805 to make the query run faster is actually wrong. The query does not run faster, it will just bring the first rows back faster, so appears to be faster.

With the end date set at 2008-05-01, it needs to run right through the whole of the ALL_OBJECTS table before returning any rows but with the longer time period, it will return rows to you when the buffer is full. Each query will run to completion in the same amount of time.