Java Programming with Oracle SQLJ

10.3. Tuning SQL Statements

One of the main strengths of SQL is that you do not have to tell the database exactly how to obtain the data requested; you simply tell it what information you want, and the database figures out the best way to get it. Sometimes you can improve the performance of your SQL statements by tuning them. This section is not intended to be a comprehensive guide to SQL tuning, as this is a vast and complex subject, but it will give you a simple introduction. For further details, I recommend the books Oracle Performance Tuning and Oracle SQL: The Essential Reference (both published by O'Reilly).

Three simple ways to improve the performance of SQL queries are as follows:

• Reduce the number of rows retrieved from tables by using the WHERE clause in SELECT statements.

• Use table joins rather than multiple queries.

• Use indexes.

Following these three simple tuning tips, which I describe in detail in the next few sections, should improve the performance of your SQL queries.

10.3.1. Using the WHERE Clause

Rather than selecting all the rows from a table into a SQLJ iterator and then accessing the iterator for just the rows you need, you should use a WHERE clause in your SELECT statement to limit the number of rows selected in the first place. In the following example, an iterator is used to retrieve all the rows from the products table. However, only the first two rows in the iterator are actually used by the program.

#sql product_iterator = { SELECT id, name, ...