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.
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
you dont have to use open when you are using for loops.
declare
cursor cur_name is select * from emp;
begin
for cur_rec in cur_name Loop
dbms_output.put_line(cur_rec.ename);
end loop;
End ;
or
declare
cursor cur_name is select * from emp;
cur_rec emp%rowtype;
begin
Open cur_name;
Loop
Fetch cur_name into Cur_rec;
Exit when cur_name%notfound;
dbms_output.put_line(cur_rec.ename);
end loop;
Close cur_name;
End ;
Yes, you're missing using cursors
DECLARE
CURSOR foo_cur IS
SELECT NEEDED_FIELD WHERE condition ;
BEGIN
OPEN foo_cur;
FETCH foo_cur INTO foo_rec;
IF foo_cur%FOUND THEN
...
END IF;
CLOSE foo_cur;
EXCEPTION
WHEN OTHERS THEN
CLOSE foo_cur;
RAISE;
END ;
admittedly this is more code, but it doesn't use EXCEPTIONs as flow-control which, having learnt most of my PL/SQL from Steve Feuerstein's PL/SQL Programming book, I believe to be a good thing.
Whether this is faster or not I don't know (I do very little PL/SQL nowadays).
@DCookie
I just want to point out that you can leave off the lines that say
EXCEPTION
WHEN OTHERS THEN
RAISE;
You'll get the same effect if you leave off the exception block all together, and the line number reported for the exception will be the line where the exception is actually thrown, not the line in the exception block where it was re-raised.
Since SELECT INTO assumes that a single row will be returned, you can use a statement of the form:
SELECT MAX(column)
INTO var
FROM table
WHERE conditions;
IF var IS NOT NULL
THEN ...
The SELECT will give you the value if one is available, and a value of NULL instead of a NO_DATA_FOUND exception. The overhead introduced by MAX() will be minimal-to-zero since the result set contains a single row. It also has the advantage of being compact relative to a cursor-based solution, and not being vulnerable to concurrency issues like the two-step solution in the original post.
Stephen Darlington makes a very good point, and you can see that if you change my benchmark to use a more realistically sized table if I fill the table out to 10000 rows using the following:
begin
for i in 2 .. 10000 loop
insert into t (NEEDED_FIELD, cond) values (i, 10);
end loop;
end;
Then re-run the benchmarks. (I had to reduce the loop counts to 5000 to get reasonable times).
declare
otherVar number;
cnt number;
begin
for i in 1 .. 5000 loop
select count(*) into cnt from t where cond = 0;
if (cnt = 1) then
select NEEDED_FIELD INTO otherVar from t where cond = 0;
else
otherVar := 0;
end if;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.34
declare
otherVar number;
begin
for i in 1 .. 5000 loop
begin
select NEEDED_FIELD INTO otherVar from t where cond = 0;
exception
when no_data_found then
otherVar := 0;
end;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.10
The method with the exception is now more than twice as fast. So, for almost all cases,the method:
SELECT NEEDED_FIELD INTO var WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND....
is the way to go. It will give correct results and is generally the fastest.
May be beating a dead horse here, but I bench-marked the cursor for loop, and that performed about as well as the no_data_found method:
declare
otherVar number;
begin
for i in 1 .. 5000 loop
begin
for foo_rec in (select NEEDED_FIELD from t where cond = 0) loop
otherVar := foo_rec.NEEDED_FIELD;
end loop;
otherVar := 0;
end;
end loop;
end;
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.18
Rather than having nested cursor loops a more efficient approach would be to use one cursor loop with an outer join between the tables.
BEGIN
FOR rec IN (SELECT a.needed_field,b.other_field
FROM table1 a
LEFT OUTER JOIN table2 b
ON a.needed_field = b.condition_field
WHERE a.column = ???)
LOOP
IF rec.other_field IS NOT NULL THEN
-- whatever processing needs to be done to other_field
END IF;
END LOOP;
END;
An alternative to @Steve's code.
DECLARE
CURSOR foo_cur IS
SELECT NEEDED_FIELD WHERE condition ;
BEGIN
FOR foo_rec IN foo_cur LOOP
...
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END ;
The loop is not executed if there is no data. Cursor FOR loops are the way to go - they help avoid a lot of housekeeping. An even more compact solution:
DECLARE
BEGIN
FOR foo_rec IN (SELECT NEEDED_FIELD WHERE condition) LOOP
...
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END ;
Which works if you know the complete select statement at compile time.
The count(*) will never raise exception because it always returns actual count or 0 - zero, no matter what. I'd use count.
Yes, you're missing using cursors
DECLARE
CURSOR foo_cur IS
SELECT NEEDED_FIELD WHERE condition ;
BEGIN
OPEN foo_cur;
FETCH foo_cur INTO foo_rec;
IF foo_cur%FOUND THEN
...
END IF;
CLOSE foo_cur;
EXCEPTION
WHEN OTHERS THEN
CLOSE foo_cur;
RAISE;
END ;
admittedly this is more code, but it doesn't use EXCEPTIONs as flow-control which, having learnt most of my PL/SQL from Steve Feuerstein's PL/SQL Programming book, I believe to be a good thing.
Whether this is faster or not I don't know (I do very little PL/SQL nowadays).
An alternative to @Steve's code.
DECLARE
CURSOR foo_cur IS
SELECT NEEDED_FIELD WHERE condition ;
BEGIN
FOR foo_rec IN foo_cur LOOP
...
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END ;
The loop is not executed if there is no data. Cursor FOR loops are the way to go - they help avoid a lot of housekeeping. An even more compact solution:
DECLARE
BEGIN
FOR foo_rec IN (SELECT NEEDED_FIELD WHERE condition) LOOP
...
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END ;
Which works if you know the complete select statement at compile time.
Since SELECT INTO assumes that a single row will be returned, you can use a statement of the form:
SELECT MAX(column)
INTO var
FROM table
WHERE conditions;
IF var IS NOT NULL
THEN ...
The SELECT will give you the value if one is available, and a value of NULL instead of a NO_DATA_FOUND exception. The overhead introduced by MAX() will be minimal-to-zero since the result set contains a single row. It also has the advantage of being compact relative to a cursor-based solution, and not being vulnerable to concurrency issues like the two-step solution in the original post.
The first (excellent) answer stated -
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.
Not so. Within a given logical Unit of Work Oracle is totally consistent. Even if someone commits the delete of the row between a count and a select Oracle will, for the active session, obtain the data from the logs. If it cannot, you will get a "snapshot too old" error.
Since SELECT INTO assumes that a single row will be returned, you can use a statement of the form:
SELECT MAX(column)
INTO var
FROM table
WHERE conditions;
IF var IS NOT NULL
THEN ...
The SELECT will give you the value if one is available, and a value of NULL instead of a NO_DATA_FOUND exception. The overhead introduced by MAX() will be minimal-to-zero since the result set contains a single row. It also has the advantage of being compact relative to a cursor-based solution, and not being vulnerable to concurrency issues like the two-step solution in the original post.
Stephen Darlington makes a very good point, and you can see that if you change my benchmark to use a more realistically sized table if I fill the table out to 10000 rows using the following:
begin
for i in 2 .. 10000 loop
insert into t (NEEDED_FIELD, cond) values (i, 10);
end loop;
end;
Then re-run the benchmarks. (I had to reduce the loop counts to 5000 to get reasonable times).
declare
otherVar number;
cnt number;
begin
for i in 1 .. 5000 loop
select count(*) into cnt from t where cond = 0;
if (cnt = 1) then
select NEEDED_FIELD INTO otherVar from t where cond = 0;
else
otherVar := 0;
end if;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.34
declare
otherVar number;
begin
for i in 1 .. 5000 loop
begin
select NEEDED_FIELD INTO otherVar from t where cond = 0;
exception
when no_data_found then
otherVar := 0;
end;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.10
The method with the exception is now more than twice as fast. So, for almost all cases,the method:
SELECT NEEDED_FIELD INTO var WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND....
is the way to go. It will give correct results and is generally the fastest.
@DCookie
I just want to point out that you can leave off the lines that say
EXCEPTION
WHEN OTHERS THEN
RAISE;
You'll get the same effect if you leave off the exception block all together, and the line number reported for the exception will be the line where the exception is actually thrown, not the line in the exception block where it was re-raised.
Rather than having nested cursor loops a more efficient approach would be to use one cursor loop with an outer join between the tables.
BEGIN
FOR rec IN (SELECT a.needed_field,b.other_field
FROM table1 a
LEFT OUTER JOIN table2 b
ON a.needed_field = b.condition_field
WHERE a.column = ???)
LOOP
IF rec.other_field IS NOT NULL THEN
-- whatever processing needs to be done to other_field
END IF;
END LOOP;
END;
Rather than having nested cursor loops a more efficient approach would be to use one cursor loop with an outer join between the tables.
BEGIN
FOR rec IN (SELECT a.needed_field,b.other_field
FROM table1 a
LEFT OUTER JOIN table2 b
ON a.needed_field = b.condition_field
WHERE a.column = ???)
LOOP
IF rec.other_field IS NOT NULL THEN
-- whatever processing needs to be done to other_field
END IF;
END LOOP;
END;
Yes, you're missing using cursors
DECLARE
CURSOR foo_cur IS
SELECT NEEDED_FIELD WHERE condition ;
BEGIN
OPEN foo_cur;
FETCH foo_cur INTO foo_rec;
IF foo_cur%FOUND THEN
...
END IF;
CLOSE foo_cur;
EXCEPTION
WHEN OTHERS THEN
CLOSE foo_cur;
RAISE;
END ;
admittedly this is more code, but it doesn't use EXCEPTIONs as flow-control which, having learnt most of my PL/SQL from Steve Feuerstein's PL/SQL Programming book, I believe to be a good thing.
Whether this is faster or not I don't know (I do very little PL/SQL nowadays).
May be beating a dead horse here, but I bench-marked the cursor for loop, and that performed about as well as the no_data_found method:
declare
otherVar number;
begin
for i in 1 .. 5000 loop
begin
for foo_rec in (select NEEDED_FIELD from t where cond = 0) loop
otherVar := foo_rec.NEEDED_FIELD;
end loop;
otherVar := 0;
end;
end loop;
end;
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.18
you dont have to use open when you are using for loops.
declare
cursor cur_name is select * from emp;
begin
for cur_rec in cur_name Loop
dbms_output.put_line(cur_rec.ename);
end loop;
End ;
or
declare
cursor cur_name is select * from emp;
cur_rec emp%rowtype;
begin
Open cur_name;
Loop
Fetch cur_name into Cur_rec;
Exit when cur_name%notfound;
dbms_output.put_line(cur_rec.ename);
end loop;
Close cur_name;
End ;
@DCookie
I just want to point out that you can leave off the lines that say
EXCEPTION
WHEN OTHERS THEN
RAISE;
You'll get the same effect if you leave off the exception block all together, and the line number reported for the exception will be the line where the exception is actually thrown, not the line in the exception block where it was re-raised.
If it's important you really need to benchmark both options!
Having said that, I have always used the exception method, the reasoning being it's better to only hit the database once.
May be beating a dead horse here, but I bench-marked the cursor for loop, and that performed about as well as the no_data_found method:
declare
otherVar number;
begin
for i in 1 .. 5000 loop
begin
for foo_rec in (select NEEDED_FIELD from t where cond = 0) loop
otherVar := foo_rec.NEEDED_FIELD;
end loop;
otherVar := 0;
end;
end loop;
end;
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.18
Yes, you're missing using cursors
DECLARE
CURSOR foo_cur IS
SELECT NEEDED_FIELD WHERE condition ;
BEGIN
OPEN foo_cur;
FETCH foo_cur INTO foo_rec;
IF foo_cur%FOUND THEN
...
END IF;
CLOSE foo_cur;
EXCEPTION
WHEN OTHERS THEN
CLOSE foo_cur;
RAISE;
END ;
admittedly this is more code, but it doesn't use EXCEPTIONs as flow-control which, having learnt most of my PL/SQL from Steve Feuerstein's PL/SQL Programming book, I believe to be a good thing.
Whether this is faster or not I don't know (I do very little PL/SQL nowadays).
The count(*) will never raise exception because it always returns actual count or 0 - zero, no matter what. I'd use count.
you dont have to use open when you are using for loops.
declare
cursor cur_name is select * from emp;
begin
for cur_rec in cur_name Loop
dbms_output.put_line(cur_rec.ename);
end loop;
End ;
or
declare
cursor cur_name is select * from emp;
cur_rec emp%rowtype;
begin
Open cur_name;
Loop
Fetch cur_name into Cur_rec;
Exit when cur_name%notfound;
dbms_output.put_line(cur_rec.ename);
end loop;
Close cur_name;
End ;
The first (excellent) answer stated -
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.
Not so. Within a given logical Unit of Work Oracle is totally consistent. Even if someone commits the delete of the row between a count and a select Oracle will, for the active session, obtain the data from the logs. If it cannot, you will get a "snapshot too old" error.
Stephen Darlington makes a very good point, and you can see that if you change my benchmark to use a more realistically sized table if I fill the table out to 10000 rows using the following:
begin
for i in 2 .. 10000 loop
insert into t (NEEDED_FIELD, cond) values (i, 10);
end loop;
end;
Then re-run the benchmarks. (I had to reduce the loop counts to 5000 to get reasonable times).
declare
otherVar number;
cnt number;
begin
for i in 1 .. 5000 loop
select count(*) into cnt from t where cond = 0;
if (cnt = 1) then
select NEEDED_FIELD INTO otherVar from t where cond = 0;
else
otherVar := 0;
end if;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.34
declare
otherVar number;
begin
for i in 1 .. 5000 loop
begin
select NEEDED_FIELD INTO otherVar from t where cond = 0;
exception
when no_data_found then
otherVar := 0;
end;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.10
The method with the exception is now more than twice as fast. So, for almost all cases,the method:
SELECT NEEDED_FIELD INTO var WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND....
is the way to go. It will give correct results and is generally the fastest.
If it's important you really need to benchmark both options!
Having said that, I have always used the exception method, the reasoning being it's better to only hit the database once.
Since SELECT INTO assumes that a single row will be returned, you can use a statement of the form:
SELECT MAX(column)
INTO var
FROM table
WHERE conditions;
IF var IS NOT NULL
THEN ...
The SELECT will give you the value if one is available, and a value of NULL instead of a NO_DATA_FOUND exception. The overhead introduced by MAX() will be minimal-to-zero since the result set contains a single row. It also has the advantage of being compact relative to a cursor-based solution, and not being vulnerable to concurrency issues like the two-step solution in the original post.
you dont have to use open when you are using for loops.
declare
cursor cur_name is select * from emp;
begin
for cur_rec in cur_name Loop
dbms_output.put_line(cur_rec.ename);
end loop;
End ;
or
declare
cursor cur_name is select * from emp;
cur_rec emp%rowtype;
begin
Open cur_name;
Loop
Fetch cur_name into Cur_rec;
Exit when cur_name%notfound;
dbms_output.put_line(cur_rec.ename);
end loop;
Close cur_name;
End ;
If it's important you really need to benchmark both options!
Having said that, I have always used the exception method, the reasoning being it's better to only hit the database once.
An alternative to @Steve's code.
DECLARE
CURSOR foo_cur IS
SELECT NEEDED_FIELD WHERE condition ;
BEGIN
FOR foo_rec IN foo_cur LOOP
...
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END ;
The loop is not executed if there is no data. Cursor FOR loops are the way to go - they help avoid a lot of housekeeping. An even more compact solution:
DECLARE
BEGIN
FOR foo_rec IN (SELECT NEEDED_FIELD WHERE condition) LOOP
...
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END ;
Which works if you know the complete select statement at compile time.
Stephen Darlington makes a very good point, and you can see that if you change my benchmark to use a more realistically sized table if I fill the table out to 10000 rows using the following:
begin
for i in 2 .. 10000 loop
insert into t (NEEDED_FIELD, cond) values (i, 10);
end loop;
end;
Then re-run the benchmarks. (I had to reduce the loop counts to 5000 to get reasonable times).
declare
otherVar number;
cnt number;
begin
for i in 1 .. 5000 loop
select count(*) into cnt from t where cond = 0;
if (cnt = 1) then
select NEEDED_FIELD INTO otherVar from t where cond = 0;
else
otherVar := 0;
end if;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.34
declare
otherVar number;
begin
for i in 1 .. 5000 loop
begin
select NEEDED_FIELD INTO otherVar from t where cond = 0;
exception
when no_data_found then
otherVar := 0;
end;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.10
The method with the exception is now more than twice as fast. So, for almost all cases,the method:
SELECT NEEDED_FIELD INTO var WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND....
is the way to go. It will give correct results and is generally the fastest.
An alternative to @Steve's code.
DECLARE
CURSOR foo_cur IS
SELECT NEEDED_FIELD WHERE condition ;
BEGIN
FOR foo_rec IN foo_cur LOOP
...
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END ;
The loop is not executed if there is no data. Cursor FOR loops are the way to go - they help avoid a lot of housekeeping. An even more compact solution:
DECLARE
BEGIN
FOR foo_rec IN (SELECT NEEDED_FIELD WHERE condition) LOOP
...
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END ;
Which works if you know the complete select statement at compile time.
May be beating a dead horse here, but I bench-marked the cursor for loop, and that performed about as well as the no_data_found method:
declare
otherVar number;
begin
for i in 1 .. 5000 loop
begin
for foo_rec in (select NEEDED_FIELD from t where cond = 0) loop
otherVar := foo_rec.NEEDED_FIELD;
end loop;
otherVar := 0;
end;
end loop;
end;
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.18
Source: Stackoverflow.com