Oracle SQL Tutorial - 
Using Bind Variables

Tip #3 in this Oracle sql tutorial  - use bind variables instead of literals. That way the query becomes generic and theerefore re-usable which saves time coding and testing

For example, instead of writing a query like :-

SELECT name,sponsor,due_date FROM project WHERE id = 125;

Change it to:-

SELECT name,sponsor,due_date FROM project WHERE id = <project_id>;

The first query will only be re-used when you request the details for project id 125, whereas the second query can be re-used for any project for which you want the details.

There may be occasions when you want to use a literal value when you know the data is skewed (distributed unevenly) and you are retrieving data via an indexed column. This is because the cost-based optimizer doesn't take account of the value of bind variables when choosing a query path.

As an example, consider a telephone directory. In the UK, the most popular surnames are Smith, Jones and Taylor and there are far fewer surnames beginning with Y or Z.

This means that if all the names and phone numbers were stored in an database then querying the database for the telephone numbers of everybody with the surname Jones would return a lot more rows than a query for Zaphod-Beeblebrox.

In this particular case , you might want to use the literal to force the optimizer to take account of the value of ths data. Generally, though, using bind variables will solve more problems than it causes.

The next Oracle SQL tutorial explains why you should use only selective indexes in your queries.

Need help with your Oracle systems? Contact Smartsoft Computing for expert Oracle training and consultancy in the UK 

Return to Oracle SQL Tutorial part 1