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.
l_empl_id DEPT.emp_id%TYPE;
select first_name  from DEPT  where id = l_empl_id;
-in SQL*Plus it is a variable
variable a number
:a = 1000;
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