Always use Binding Variables in SQL queries

While writing queries in SQL always prefer using the Binding Variables. Here's why:

Everytime a query is executed, it is first checked into the Shared Pool to see whether the query was executed before or not. If yes, then its execution plan is used again to execute the new query. If no, Hard Parse is done by the database. The query is parsed, working out the various execution paths and coming up with an optimal access plan before it can be executed. Hard parsing is very CPU intensive, and involves obtaining latches on key shared memory areas.

So, lets take an example:

select * from table1 where salary = 2000

Now if the value 2000 changes everytime with input from user, the query will never be unique and will be hard parsed everytime, generating extra CPU burden.

Solution: Binding Variables
Example:

select * from table1 where salary = :salary

Now this makes the statement unique everytime and just the values change in it, reducing the Hard Parse overhead. Every reference to a PL/SQL variable is in fact a binding variable.


For more details see the link @ http://www.akadia.com/services/ora_bind_variables.html[^]