What are bind variables?

What are bind variables?

Rating:

To increase the hit ratio of the shared pool, it is required that the statement currently issued is hashed to an existing P-Code in the memory. However, this requires that the statement currently issued and the one for which the P-Code already exists in the memory, be textually similar in all respect. Generally, it happens that two statements differ only by the value of the variable assigned in the statement. To eliminate this drawback, it is recommended to use bind variables, which are more generic in nature than specific. An example of using bind variables is as follows:

Statement issued without using bind variable:
SQL> select student_name
from student
where rollno = 98008;

Statement issued using bind variable:
SQL> select student_name
from student
where rollno = :var_value;

Since the hash value is decided using the bind variable name, and not by the value stored therein, the probability of a cache hit increases.

Rating: