Oracle: Bind Variables

What are Bind Variables?

Bind variables are so called SQL query performance improvement catalysts. The simple definition of a Bind variable is "Passing a value by reference". Bind variables are 'substitution' variables that are used in place of literals.
Using bind variable is your dynamic queries could potentially improve your SQL query performance by reusing the execution plan that the statement is previously used.

NOTE: Important thing to remember is that you can't substitute object names (tables, views, columns etc) with bind variables. You could only substitute literals with the bind variables.

NOTE: "Using Duplicate Placeholders":
Placeholders in a dynamic SQL statement are associated with bind arguments in the USING clause by position, not by name. So, if the same placeholder appears two or more times in the SQL statement, each appearance must correspond to a bind argument in the USING clause.

For example: following query is defined using the identical names to bind variables, but all the placeholder are being replaced with a proper value.


DECALRE
v_empno number := 100;
v_name varchar2(100) := 'srinivas sreeramoju';
v_sal     number := 5000;
BEGIN
v_sql := 'INSERT INTO emp (empno,fname,lname,sal) VALUES (:x, :y, :y, :x)';

EXECUTE IMMEDIATE v_sql USING v_empno, v_name, v_name, v_sal;
END;

How will they work?

Just analyze the following example:
SELECT f_name, l_name, sal FROM customers WHERE empno = 100;
SELECT f_name, l_name, sal FROM customers WHERE empno = 101;
SELECT f_name, l_name, sal FROM customers WHERE empno = 102;
SELECT f_name, l_name, sal FROM customers WHERE empno = 103;

Each time the query is submitted, Oracle first checks for a matching statement in the shared pool. if found, the execution plan that this statement previously used is retrieved, and the SQL is executed.

If the statement cannot be found in the shared pool, Oracle has to go through the process of parsing the statement, working out the various execution paths and coming up with an optimal access plan before it can be executed.

In the above example, the "empno" column predicate value changes to each query, so you'll never get a match, and every statement you submit will need to be hard parsed.

So the best way to get Oracle to reuse the execution plans for these statements is to use bind variables.

SELECT f_name, l_name, sal FROM customers WHERE empno = :emp_no;
SELECT f_name, l_name, sal FROM customers WHERE empno = :emp_no;
SELECT f_name, l_name, sal FROM customers WHERE empno = :emp_no;
SELECT f_name, l_name, sal FROM customers WHERE empno = :emp_no;