Are PL / SQL variables in cursors as efficient as binding parameters?
I've heard that using bind variables is (maybe) more efficient, because for subsequent calls with a different bind value, the query itself is still the same, so it doesn't need to be parsed anymore.
I understand why this is the case for fixed values. In the cursor below, the value is fixed at 1. If I have another cursor that is the same, except that 1 becomes 2, that is a clear request. It is still clear.
declare
cursor C_CURSOR is
select * from TESTTABLE pt where pt.ID = 1;
But I wondered if this is the case when using PL / SQL variables inside a cursor. Whether they are expanded as a fixed value or interpreted as bind variables.
I've searched everywhere, but everywhere I find examples about literals like in the above case, but doesn't give clear explanations regarding the use of PL / SQL variables.
In other words, of the two snippets below, is the second one potentially more efficient, or are they essentially the same?
With a PL / SQL variable directly in the cursor:
declare
V_TEST integer := 1;
cursor C_CURSOR is
select *
from
TESTTABLE pt
where
pt.ID = V_TEST;
begin
for r in C_CURSOR loop
null;
end loop;
end;
With a binding variable:
declare
V_TEST int := 1;
cursor C_CURSOR(B_TEST int) is
select *
from
TESTTABLE pt
where
pt.ID = B_TEST;
begin
for r in C_CURSOR(V_TEST) loop
null;
end loop;
end;
source to share
First of all, a good question.
I would like to make a small quote:
Every reference to a PL / SQL variable is actually a bind variable.
Having said that
PL / SQL itself takes care of most of the problems associated with bind variables , to the point that most of the code you write uses bind variables without your knowledge. Take, for example, the following PL / SQL bit:
create or replace procedure dsal(p_empno in number)
as
begin
update emp
set sal=sal*2
where empno = p_empno;
commit;
end;
/
Now, you might think that you should replace p_empno with a bind variable. However, the good news is that every PL / SQL variable reference is actually a bind variable.
source to share
Reading the Thin Manual: PL / SQL Static SQL
A static PL / SQL SQL statement can have a PL / SQL identifier wherever its SQL instance can have a placeholder for a bind variable. A PL / SQL identifier must identify either a variable or a formal parameter.
Both of your snippet examples are equivalent from a SQL engine perspective and work equally well.
Reading a more accurate guide:
Typically, PL / SQL parses an explicit cursor only the first time a session is opened, and parses an SQL statement (creating an implicit cursor) only the first time it is started.
All parsed SQL statements are cached. An SQL statement is repaired only if it is out of the cache with a new SQL statement. Although you must close the explicit cursor before you can reopen it, PL / SQL does not need to re-process the associated query. If you close and immediately open the explicit cursor, PL / SQL does not repeat the associated query.
Therefore, no SQL parsing is required to change the binding variable.
From a code readability (i.e. maintenance) standpoint, the second snippet that uses parameters is excellent. This is not obvious in small snippets, but in large PL / SQL programs it is a headache if cursors use package variables or subroutines directly. When reading the code, each time you need to check in what state the cursor depends. With the cursor options, you can see this immediately.
source to share