Bind variables are an integral part of every application. There is no chance that any application will work correctly without them. It all about performance and understanding of Oracle’s architecture.
You can find the same behavior anywhere in your daily life. When you connect to your email, Facebook account, or when you just shop online 🙂 All of these actions generates some database queries and most of them are the same. Why? Because you are not the only one doing so 🙂
Let’s take online shopping as an example. You are online and checking a product A (id = 1). In a database, there will be a query generated like:
SELECT * FROM product WHERE id = 1;
Of course, the query will be a bit more complicated, but this is just for illustration purposes. Now you wanna check another product so another query will be generating. Let’s check another 3 products.
SELECT * FROM product WHERE id = 1; SELECT * FROM product WHERE id = 20; SELECT * FROM product WHERE id = 23; SELECT * FROM product WHERE id = 13;
Each of these queries will generate a Hard Parse (check out the article SELECT statement processing for more details), which, as we said, is very bad with regard to performance. So, how could we improve it? Yes, with bind variables 🙂
Every time you have a query that will be executed repeatedly but with different values (mostly in WHERE conditions) you have to use bind variables. Let’s check out how would such query look like for the example above:
SELECT * FROM product WHERE id = :1;
In this case, the variable :1 will be replaced with the value. However, it is after the parsing phase so no matter with what values will it be replaced, the query is considered the same; therefore soft parse will be applied 🙂 Bingo!
Bind Variables vs Dynamic SQL
Do not confuse bind variables with dynamic SQL. Unlike bind variables, dynamic SQL will always fall into the hard parsing phase. Why? Because it does not use any variables it just dynamically composes the query.
-- bind variable example
DECLARE
v_id INT := 10;
v_salary INT := 3000;
v_query VARCHAR2(255);
BEGIN
v_query := 'UPDATE employee SET salary = :1 WHERE id_employee := 2';
EXECUTE IMMEDIATE v_query USING v_salary, v_id;
commit;
END;
-- bind variable with parameters
CREATE OR REPLACE PROCEDURE p_update_salary(p_id_emp IN NUMBER, p_salary IN NUMBER)
IS
BEGIN
UPDATE employee SET salary = p_salary WHERE i_employee = p_id_emp;
commit;
END;
-- dynamic sql example
DECLARE
v_id INT := 10;
v_salary INT := 3000;
v_query VARCHAR2(255);
BEGIN
v_query := 'UPDATE employee SET salary = '||v_salary||' WHERE id_employee = '||v_id;
EXECUTE IMMEDIATE v_query;
commit;
END;
The first two examples are correct and they will be soft-parsed after the first execution. The last one will be always hard-parsed if you use different values.
Performance demonstration of Bind vs Dynamic
I can easily demonstrate how substantial the difference is between using bind variables and dynamic queries.
DECLARE
v_start NUMBER;
v_object_name all_objects.object_name%TYPE;
v_query VARCHAR2(255);
BEGIN
/*** DYNAMIC SQL ***/
v_start := DBMS_UTILITY.get_time;
FOR x IN (SELECT object_id as obj_id FROM all_objects WHERE object_id BETWEEN 1 AND 1000)
LOOP
v_query := 'SELECT object_name
FROM all_objects
WHERE object_id = ' || x.obj_id;
EXECUTE IMMEDIATE v_query INTO v_object_name;
END LOOP;
dbms_output.put_line('Dynamic SQL: '||(dbms_utility.get_time - v_start));
/***************************************************/
/*** BIND VARIABLE ***/
v_start := DBMS_UTILITY.get_time;
FOR x IN (SELECT object_id as obj_id FROM all_objects WHERE object_id BETWEEN 1 AND 1000)
LOOP
v_query := 'SELECT object_name
FROM all_objects
WHERE object_id = :1';
EXECUTE IMMEDIATE v_query INTO v_object_name USING x.obj_id;
END LOOP;
dbms_output.put_line('Bind variable: '||(dbms_utility.get_time - v_start));
/***************************************************/
END;
/* output (in seconds)
Dynamic SQL: 2966
Bind variable: 8
*/
As you can see, it’s enormous. Always stick with the bind variables 🙂