oracle - Is the use of SELECT COUNT(*) before SELECT INTO slower than using Exceptions? -

my last question got me thinking.

1)

select count(*) count foo bar = 123; if count > 0     select var foo bar = 123;     -- stuff else     -- other stuff end if; 

2)

begin     select var foo bar = 123;     -- stuff exception     when no_data_found         --do other stuff end ; 

i assume number 2 faster because requires 1 less trip database.

is there situation 1 superior, not considering?

edit: i'm going let question hang few more days, gather more votes on answers, before answering it.

if use exact queries question 1st variant of course slower because must count records in table satisfies criteria.

it must writed

select count(*) row_count foo bar = 123 , rownum = 1; 

or

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

because checking record existence enough purpose.

of course, both variants don't guarantee else don't change in foo between 2 statements, it's not issue if check part of more complex scenario. think situation when changed value of foo.a after selecting it's value var while performing actions refers selected var value. in complex scenarios better handle such concurrency issues on application logic level.
perform atomic operations better use single sql statement.

any of variants above requires 2 context switches between sql , pl/sql , 2 queries performs slower variant described below in cases when row found in table.

there variants check existence of row without exception:

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

if row_count = 1 1 row satisfies criteria.

sometime it's enough check existence because of unique constraint on foo guarantees there no duplicated bar values in foo. e.g. bar primary key.
in such cases it's possible simplify query:

select max(a) var foo bar = 123; if(var not null)    ... end if; 

or use cursor processing values:

for cvaluea in (    select foo bar = 123 ) loop   ...   end loop; 

next variant link, provided @user272735 in answer:

select    (select foo bar = 123)   var  dual; 

from experience variant without exception blocks in cases faster variant exceptions, if number of executions of such block low better use exception block handling of no_data_found , too_many_rows exceptions improve code readability.

right point choose use exception or don't use it, ask question "is situation normal application?". if row not found , it's expected situation can handled (e.g. add new row or take data place , on) better avoid exception. if it's unexpected , there no way fix situation, catch exception customize error message, write event log , re-throw, or don't catch @ all.

to compare performance make simple test case on system whith both variants called many times , compare.
more, in 90 percent of applications question more theoretical practical because there lot of sources of performance issues must taken account first.

update

i reproduced example this page @ sqlfiddle site little corrections (link).
results prove variant selecting dual performs best: little overhead when of queries succeed , lowest performance degradation when number of missing rows raises.
surprisingly variant count() , 2 queries showed best result in case if queries failed.

| 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 1 test run -- all_failed   - time in seconds if tested rows missed table -- all_succeed  - time in seconds if tested rows found in table -- variant name - short name of tested variant 

below setup code test environment , test script.

create table t_test(a, b) select level,level dual connect level<=1e5 / insert t_test(a, b) select null, level dual connect 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 params(pstart, pend) values(1, 2000) / 

-- f1 - exception handling

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

-- f2 - cursor loop

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

-- f3 - max()

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

-- f4 - select field in select dual

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

-- f5 - check count() value

create or replace function f5(p in number) return number   res number;   cnt number; begin   select count(*) cnt   t_test t t.a=p , rownum = 1;    if(cnt = 1)     select b res t_test t t.a=p;   end if;    return res; end; / 

test script:

declare   v       integer;   v_start integer;   v_end   integer;    vstarttime number;  begin   select pstart, pend v_start, v_end params;    vstarttime := dbms_utility.get_cpu_time;    in v_start .. v_end loop     v:=f1(i);   end loop;    insert 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 v_start, v_end params;    vstarttime := dbms_utility.get_cpu_time;    in v_start .. v_end loop     v:=f2(i);   end loop;    insert 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 v_start, v_end params;    vstarttime := dbms_utility.get_cpu_time;    in v_start .. v_end loop     v:=f3(i);   end loop;    insert 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 v_start, v_end params;    vstarttime := dbms_utility.get_cpu_time;    in v_start .. v_end loop     v:=f4(i);   end loop;    insert 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 v_start, v_end params;   --v_end := v_start + trunc((v_end-v_start)*2/3);    vstarttime := dbms_utility.get_cpu_time;    in v_start .. v_end loop     v:=f5(i);   end loop;    insert timings(fname, loop_count, exec_time)      values ('f5', v_end-v_start+1, (dbms_utility.get_cpu_time - vstarttime)/100) ; end; /  select * timings order fname /