Oracle PL/SQL - Are NO_DATA_FOUND Exceptions bad for stored procedure performance?

Question

I'm writing a stored procedure that needs to have a lot of conditioning in it. With the general knowledge from C#.NET coding that exceptions can hurt performance, I've always avoided using them in PL/SQL as well. My conditioning in this stored proc mostly revolves around whether or not a record exists, which I could do one of two ways:

SELECT COUNT(*) INTO var WHERE condition; IF var > 0 THEN    SELECT NEEDED_FIELD INTO otherVar WHERE condition; .... 

-or-

SELECT NEEDED_FIELD INTO var WHERE condition; EXCEPTION WHEN NO_DATA_FOUND .... 

The second case seems a bit more elegant to me, because then I can use NEEDED_FIELD, which I would have had to select in the first statement after the condition in the first case. Less code. But if the stored procedure will run faster using the COUNT(*), then I don't mind typing a little more to make up processing speed.

Any hints? Am I missing another possibility?

EDIT I should have mentioned that this is all already nested in a FOR LOOP. Not sure if this makes a difference with using a cursor, since I don't think I can DECLARE the cursor as a select in the FOR LOOP.

Answer 1

I would not use an explicit cursor to do this. Steve F. no longer advises people to use explicit cursors when an implicit cursor could be used.

The method with count(*) is unsafe. If another session deletes the row that met the condition after the line with the count(*), and before the line with the select ... into, the code will throw an exception that will not get handled.

The second version from the original post does not have this problem, and it is generally preferred.

That said, there is a minor overhead using the exception, and if you are 100% sure the data will not change, you can use the count(*), but I recommend against it.

I ran these benchmarks on Oracle 10.2.0.1 on 32 bit Windows. I am only looking at elapsed time. There are other test harnesses that can give more details (such as latch counts and memory used).

SQL>create table t (NEEDED_FIELD number, COND number); 

Table created.

SQL>insert into t (NEEDED_FIELD, cond) values (1, 0); 

1 row created.

declare   otherVar  number;   cnt number; begin   for i in 1 .. 50000 loop      select count(*) into cnt from t where cond = 1;       if (cnt = 1) then        select NEEDED_FIELD INTO otherVar from t where cond = 1;      else        otherVar := 0;      end if;    end loop; end; / 

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.70

declare   otherVar  number; begin   for i in 1 .. 50000 loop      begin        select NEEDED_FIELD INTO otherVar from t where cond = 1;      exception        when no_data_found then          otherVar := 0;      end;    end loop; end; / 

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.06