Oracle 中日期函数的异常运行时间

Question

我正在运行一个查询，该查询返回特定日期范围内几个月的日期对象集合。查询工作正常，但速度非常慢（在我的本地计算机上约为 2 秒，在我们的企业开发环境中约为 30 秒）。它是这样的：

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

目前，它只会返回一个月，但如果您扩展第二个日期字符串，它会返回更多。

我有两个问题。首先，为什么它运行得这么慢？我知道 Oracle 函数确实会减慢查询速度，但在我工作的开发机器上这大约需要 30 秒。

第二个也是更令人费解的问题：当您将范围扩展到“201805”时，为什么运行时间会缩短到几分之一秒？我认为更大的范围需要更长的时间。似乎起到了相反的效果。

Answer 1

请改用它，

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

这可以避免 all_objects 在您的两个环境之间可能有所不同。

all_objects 是一个复杂的视图，因此其性能不如上面使用的内联视图。如果您不想使用“连接方式”语法，则创建一个整数表并使用它。

Answer 2

Janek 函数的轻微变体，通过使用 MONTHS_BETWEEN() 函数摆脱任意 4000 个月的限制

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

不需要使用内联视图，而且我发现使用了太多日期函数。
如果你跳过所有这些，这仍然是：

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.

这看起来更容易......

问候，
抢。

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 使查询运行得更快的评论实际上是错误的。查询并不会运行得更快，它只会更快地返回第一行，因此看起来更快。

由于结束日期设置为 2008 年 5 月 1 日，它需要在返回任何行之前直接运行整个 ALL_OBJECTS 表，但时间周期较长，当缓冲区已满时，它将向您返回行。每个查询将在相同的时间内运行完成。