oracle - 在 SELECT INTO 之前使用 SELECT COUNT(*) 是否比使用 Exceptions 慢？

Question

我的 last question让我开始思考。

1)

SELECT COUNT(*) INTO count FROM foo WHERE bar = 123;
IF count > 0 THEN
    SELECT a INTO var FROM foo WHERE bar = 123;
    -- do stuff
ELSE
    -- do other stuff
END IF;

2)

BEGIN
    SELECT a INTO var FROM foo where bar = 123;
    -- do stuff
EXCEPTION
    WHEN no_data_found THEN
        --do other stuff
END ;

我假设数字 2 更快，因为它需要更少的数据库访问。

有没有我没有考虑到 1 会更好的情况？

编辑:我打算让这个问题再悬置几天，以便在回答之前收集更多对答案的投票。

Answer 1

如果您使用问题中的精确查询，那么第一个变体当然会更慢，因为它必须计算表中满足条件的所有记录。

必须写成

SELECT COUNT(*) INTO row_count FROM foo WHERE bar = 123 and rownum = 1;

或

select 1 into row_count from dual where exists (select 1 from foo where bar = 123);

因为检查记录是否存在就足以满足您的目的。

当然，这两种变体都不能保证其他人不会在两个语句之间更改 foo 中的某些内容，但如果此检查是更复杂场景的一部分，则这不是问题。想一想有人在将 foo.a 的值选择到 var 之后更改它的值，同时执行一些引用所选 var 值的操作的情况。所以在复杂的场景下，最好在应用程序逻辑层面处理此类并发问题。
要执行原子操作，最好使用单个 SQL 语句。

上面的任何变体都需要在 SQL 和 PL/SQL 之间进行 2 次上下文切换以及 2 次查询，因此当在表中找到行时，执行速度比下面描述的任何变体都要慢。

还有另一种变体可以毫无异常(exception)地检查行的存在:

select max(a), count(1) into var, row_count 
from foo 
where bar = 123 and rownum < 3;

如果 row_count = 1，则只有一行满足条件。

有时只检查是否存在就足够了，因为 foo 的唯一约束保证了 foo 中没有重复的 bar 值.例如。 bar 是主键。
在这种情况下，可以简化查询:

select max(a) into var from foo where bar = 123;
if(var is not null) then 
  ...
end if;

或者使用游标来处理值:

for cValueA in ( 
  select a from foo where bar = 123
) loop
  ...  
end loop;

下一个变体来自 link ，由@user272735 在他的回答中提供:

select 
  (select a from foo where bar = 123)
  into var 
from dual;

根据我的经验，在大多数情况下，任何没有异常 block 的变体都比有异常的变体更快，但如果此类 block 的执行次数很低，那么最好使用异常 block 来处理 no_data_found 和 too_many_rows 异常以提高代码可读性。

选择使用异常或不使用异常的正确点是问一个问题“这种情况对于应用程序来说是否正常？”。如果未找到行并且这是可以处理的预期情况(例如添加新行或从其他地方获取数据等)，则最好避免异常。如果它是意外的并且没有办法修复某个情况，那么捕获异常以自定义错误消息，将其写入事件日志并重新抛出，或者根本不捕获它。

要比较性能，只需在您的系统上做一个简单的测试用例，多次调用两个变体并进行比较。
多说一点，在 90% 的应用程序中，这个问题的理论性大于实际性，因为还有许多其他性能问题来源必须首先考虑。

更新

我从 this page 复制了示例在 SQLFiddle 站点上进行了一些更正 ( link )。
结果证明，从 dual 中选择的变体表现最好:当大多数查询成功时，开销很小，当缺失行数增加时，性能下降最低。
count() 和两个查询的令人惊讶的变体在所有查询都失败的情况下显示了最佳结果。

| FNAME | LOOP_COUNT | ALL_FAILED | ALL_SUCCEED | variant name |
----------------------------------------------------------------
|    f1 |       2000 |       2.09 |        0.28 |  exception   |
|    f2 |       2000 |       0.31 |        0.38 |  cursor      |
|    f3 |       2000 |       0.26 |        0.27 |  max()       |
|    f4 |       2000 |       0.23 |        0.28 |  dual        |
|    f5 |       2000 |       0.22 |        0.58 |  count()     |

-- FNAME        - tested function name 
-- LOOP_COUNT   - number of loops in one test run
-- ALL_FAILED   - time in seconds if all tested rows missed from table
-- ALL_SUCCEED  - time in seconds if all tested rows found in table
-- variant name - short name of tested variant

下面是测试环境和测试脚本的设置代码。

create table t_test(a, b)
as
select level,level from dual connect by level<=1e5
/
insert into t_test(a, b) select null, level from dual connect by level < 100
/

create unique index x_text on t_test(a)
/

create table timings(
  fname varchar2(10), 
  loop_count number, 
  exec_time number
)
/

create table params(pstart number, pend number)
/
-- loop bounds
insert into params(pstart, pend) values(1, 2000)
/

-- f1 - 异常处理

create or replace function f1(p in number) return number
as
  res number;
begin
  select b into res
  from t_test t
  where t.a=p and rownum = 1;
  return res;
exception when no_data_found then
  return null;
end;
/

-- f2 - 光标循环

create or replace function f2(p in number) return number
as
  res number;
begin
  for rec in (select b from t_test t where t.a=p and rownum = 1) loop
    res:=rec.b;
  end loop;
  return res;
end;
/

-- f3 - 最大值()

create or replace function f3(p in number) return number
as
  res number;
begin
  select max(b) into res
  from t_test t
  where t.a=p and rownum = 1;
  return res;
end;
/

-- f4 - select as field in select from dual

create or replace function f4(p in number) return number
as
  res number;
begin
  select
    (select b from t_test t where t.a=p and rownum = 1)
    into res
  from dual;
  return res;
end;
/

-- f5 - 检查 count() 然后获取值

create or replace function f5(p in number) return number
as
  res number;
  cnt number;
begin
  select count(*) into cnt
  from t_test t where t.a=p and rownum = 1;

  if(cnt = 1) then
    select b into res from t_test t where t.a=p;
  end if;

  return res;
end;
/

测试脚本:

declare
  v       integer;
  v_start integer;
  v_end   integer;

  vStartTime number;

begin
  select pstart, pend into v_start, v_end from params;

  vStartTime := dbms_utility.get_cpu_time;

  for i in v_start .. v_end loop
    v:=f1(i);
  end loop;

  insert into timings(fname, loop_count, exec_time) 
    values ('f1', v_end-v_start+1, (dbms_utility.get_cpu_time - vStartTime)/100) ;
end;
/

declare
  v       integer;
  v_start integer;
  v_end   integer;

  vStartTime number;

begin
  select pstart, pend into v_start, v_end from params;

  vStartTime := dbms_utility.get_cpu_time;

  for i in v_start .. v_end loop
    v:=f2(i);
  end loop;

  insert into timings(fname, loop_count, exec_time) 
    values ('f2', v_end-v_start+1, (dbms_utility.get_cpu_time - vStartTime)/100) ;
end;
/

declare
  v       integer;
  v_start integer;
  v_end   integer;

  vStartTime number;

begin
  select pstart, pend into v_start, v_end from params;

  vStartTime := dbms_utility.get_cpu_time;

  for i in v_start .. v_end loop
    v:=f3(i);
  end loop;

  insert into timings(fname, loop_count, exec_time) 
    values ('f3', v_end-v_start+1, (dbms_utility.get_cpu_time - vStartTime)/100) ;
end;
/

declare
  v       integer;
  v_start integer;
  v_end   integer;

  vStartTime number;

begin
  select pstart, pend into v_start, v_end from params;

  vStartTime := dbms_utility.get_cpu_time;

  for i in v_start .. v_end loop
    v:=f4(i);
  end loop;

  insert into timings(fname, loop_count, exec_time) 
    values ('f4', v_end-v_start+1, (dbms_utility.get_cpu_time - vStartTime)/100) ;
end;
/

declare
  v       integer;
  v_start integer;
  v_end   integer;

  vStartTime number;

begin
  select pstart, pend into v_start, v_end from params;
  --v_end := v_start + trunc((v_end-v_start)*2/3);

  vStartTime := dbms_utility.get_cpu_time;

  for i in v_start .. v_end loop
    v:=f5(i);
  end loop;

  insert into timings(fname, loop_count, exec_time) 
    values ('f5', v_end-v_start+1, (dbms_utility.get_cpu_time - vStartTime)/100) ;
end;
/

select * from timings order by fname
/