Unusual runtime of date functions in Oracle

Question

I run a query that returns me a set of date objects for months between a specific date range. The request works fine, but very slow (~ 2 seconds on my local computer, ~ 30 in our corporate development environment). There he 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 returns only one month, but if you extend the second date string, it returns more.

I have two questions. First, why is this happening so slowly? I know that Oracle features really slow down the request, but it takes about 30 seconds on the development machine at my work.

A second and more cryptic question: why is the reduction in runtime reduced to a fraction of a second when you expand the range to, say, '201805'? I think a larger range will take longer. It looks like the opposite effect.

Answer 1

There is no need to use inline views, and I see too many date functions used. If you miss all of this, this will remain:

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 2

Use 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 may be different between two environments.

all_objects - , , , . "connect by", .

Answer 3

Janek, 4000- , MONTHS_BETWEEN()

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 4

, ALL_OBJECTS ADD_MONTHS(TO_DATE('200804', 'YYYYMM'), -1+rownum) . where, COUNT STOPKEY COUNT.

. .

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

, 201805 , . , , , , .

With the end date set in 2008-05-01, it should run right across the ALL_OBJECTS table before returning any rows, but with a longer period of time, it will return you rows when the buffer is full. Each request will be completed in the same period of time.