Performance Lesson :What is a Bind Variable



Last updated on August 16th, 2015 at 03:23 am

What is a Bind Variable?

-It is a placeholder for a value
-In PL/SQL it is called a local or global variable.
DECLARE
l_empl_id DEPT.emp_id%TYPE;
BEGIN
select first_name  from DEPT  where id = l_empl_id;
END;
-in SQL*Plus it is a variable
variable a number
BEGIN
:a = 1000;
dbms_output.put_line(a);
END;- Sql without binds
SELECT id FROM users
WHERE user_id=875875;
SELECT id FROM users
WHERE user_id=86986;
– Sql with  binds
SELECT id FROM users
WHERE user_id==:a_user_id

– When sql statement with binds are used, It can be shared with mutiple user session and Soft parse is just good to execute the statement. Hard parsing each statement is quite a pain and severely impact the performance of the oracle database.


Leave a Reply