'extract(year from sysdate)‘的速度

Question

我有两个问题：

with tmp as (
select asy.aim_student_id, ast.aim_test, asq.response
  from aim_student_test ast
  join aim_student_qst asq on (asq.aps_yr = ast.aps_yr and asq.aim_test = ast.aim_test and asq.aim_id = ast.aim_id)
  join aim_student_yr asy on (asy.aps_yr = ast.aps_yr and asy.aim_student_yr_id = ast.aim_student_yr_id)
    where asq.aps_yr = '2012'
    and asq.qst_num = 1)
select aim_student_id, aim_test, response  
  from tmp
  where response is null
  -- execution-time: 0.032 seconds


define this_year = extract(year from sysdate)
with tmp as (
select asy.aim_student_id, ast.aim_test, asq.response
  from aim_student_test ast
  join aim_student_qst asq on (asq.aps_yr = ast.aps_yr and asq.aim_test = ast.aim_test and asq.aim_id = ast.aim_id)
  join aim_student_yr asy on (asy.aps_yr = ast.aps_yr and asy.aim_student_yr_id = ast.aim_student_yr_id)
    where asq.aps_yr = &this_year
    and asq.qst_num = 1)
select aim_student_id, aim_test, response  
  from tmp
  where response is null
  -- execution-time: 82.202 seconds

唯一的区别是，在一个代码中我使用了'2012‘，而在另一个代码中，我实现了extract(从sysdate开始的年份)。

我只能想象Oracle正在为它检查的每个记录计算提取(Year from sysdate)，而我只是想不出如何让它计算一次并将其用作变量。搜索并没有给我我想要的答案。所以我来找SO.com的魔术师。如何正确使用

extract(year from sysdate)

作为一个变量？

Answer 1

在查询中使用&this_year会导致字符串extract(year from sysdate)被替换，因此第二个查询实际上具有：

where asq.aps_yr = extract(year from sysdate)

你可以从第二个解释计划中看到。这本身可能不是问题所在；可能会让它慢下来的是，这样做是将计划从index range scan更改为针对aim_student_qstp1的index skip scan。真正的不同之处在于，在快速版本中，您将asq.aps_yr与字符串('2012')进行比较，在第二个版本中，它是一个数字(2012)，并且-正如在explain计划中所示-这会导致它执行to_number(asq.aps_yr)操作，从而停止使用您预期的索引。

您可以通过以下方式在代码中修复此问题：

where asq.aps_yr = to_char(&this_year)

如果您希望在查询运行之前计算一次，然后将其用作变量，则至少有两种方法(在SQL*Plus/SQL Developer中)。坚持使用替代变量，您可以使用column命令而不是define

column tmp_this_year new_value this_year
set termout off
select extract(year from sysdate) as tmp_this_year from dual;
set termout on
set verify off

..。这使得&this_year=2012 ( termout更改只是使实际的检索不可见，并且verify在使用替换时停止通知您；两者都是如此，这样您就不会在脚本中获得额外的输出)，并将您的查询更改为：

where asq.aps_yr = '&this_year'

..。因此，该值被视为字符串，因此不需要to_char()。

或者，您可以使用绑定变量：

var this_year varchar2(4);
set feedback off;
exec :this_year := extract(year from sysdate);

..。然后你的查询就是：

where asq.aps_yr = :this_year

请注意，在这种情况下，您不需要引号，因为绑定变量已经定义为字符串-在设置它的exec中有一个隐式转换。

Answer 2

我怀疑差异是由于从日期中提取年份造成的。我非常确定Oracle只提取一次年份，因为它在第二种情况下使用了一个变量。

不同之处在于查询所使用的执行路径。您需要发布执行计划才能真正看到差异。使用显式常量可以为优化器提供更多用于选择最佳查询计划的信息。

例如，如果数据是按年分区的，那么对于固定的年份，Oracle可以确定哪个分区包含该数据。在第二种情况下，Oracle可能无法将该值识别为常量，因此需要读取所有数据分区。这只是一个可能发生的情况的示例--我不确定Oracle在这种情况下会做什么。