Oracle SQL Tutorial - How To Make Your SQL Lightning-Fast

Tip #3 - Use Bind Variables

The 3rd tip in this Oracle sql tutorial is to use bind variables rather than literals in queries. That way the query becomes generic and re-usable.

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 = <emp_id>;

The first query will only be re-used when you request the details for project number 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 (i.e. not distributed evenly) 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 UK telephone directory. Depending on where you are in the UK, the most popular surnames are likely to be Admas, Smith, Jones, Brown and there are likely to be very few 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 examines why you should use only selective indexes in your queries.

Return to Oracle SQL Tutorial part 1