325-328

Previous

Table of Contents

Next

WHERE Clause Tips

While most of these tips are generalizations , each of them has been proven to be effective much of time. In general, it s best to write your code according to these tips (making necessary allowances to meet your required functionality) and then differentiate from the tips as necessary to improve performance. These tips all apply to the WHERE clause for your SELECT , DELETE , and UPDATE statements:

   Use the first column of the index. If necessary, use several columns of the index to assure that Oracle selects the index that you want to use. Knowing how to identify and use the indexes on a table is a very important skill.

   If your statement references more than one table, make sure that each column is referenced with a table name or table alias. This avoids overhead required for Oracle to determine the table of each column.

   If you re using AND conditions, make sure that the condition most likely to cause the query to fail is tested first. This will save processing time by avoiding comparisons that will later be invalidated by a frequently occurring condition.

   If you re using OR conditions, make sure that the condition most likely to cause the query to fail is tested last. This will save processing by avoiding comparisons that are more likely to fail in favor of comparisons that are likely to succeed.

   Don t join against unnecessary tables.

   Join only columns of the same datatype and length.

   Avoid the use of implicit datatype conversions.

   Don t use any functions (whether built-in or user -defined) on the left side of an expression.

   Try to avoid the use of the IN , ANY , ALL , BETWEEN , and NOT operators.

   Use the >= operator instead of the LIKE operator wherever possible. If the LIKE operator must be used, try to avoid the use of %string% conditions with the LIKE operator. Using the LIKE operator is expensive because Oracle must step through each position in a string of text, which takes a considerable amount of processing time.

Remember, each of these tips is a generalization based on many individual statements. Successful performance tuning is the result of many hours of tedious work to wring out every bit of performance; these tips will only start you along that road.

There are some additional tuning tips that apply when you re using Oracle s rule-based optimizer, which attempts to execute every SQL statement using the same method.

Rule-Based Optimizer

The tips outlined in this section are relevant only when using the rule-based optimizer. Most Oracle installations now predominantly use the cost-based optimizer, but use of the rule-based optimizer is still far from uncommon.

There are two primary conditions that you should be aware of when using the rule-based optimizer in your queries.

   Indexed columns referenced in the WHERE clause of your query should be listed in the same order as the columns are included in the index. If your code doesn t follow this pattern, Oracle might use a less effective index to execute your query.

   If you are joining multiple tables in your query, list the tables in the FROM clause in order from the largest to the smallest. This will allow Oracle to cache data from smaller tables so conditions can be evaluated against the data in larger tables.

If it seems like using the rule-based optimizer requires more work, that s because it does. Using the rule-based optimizer requires you to be much more conscious of the conditions that exist in your data and the indexes in place on your tables.

While tuning SQL statements is the most common type of performance tuning, there are some general guidelines that can significantly improve the performance of your PL/SQL code as well.

Tuning PL/SQL

There s very little call for tuning a properly designed block of PL/SQL. When performance tuning is necessary, most (if not all) of the work goes into tweaking performance improvements out of individual SQL statements. Still, there are several important design considerations for PL/SQL blocks that can have a significant impact on performance, most notably in the areas of using cursors and exception handling. The use of cursors allows you to significantly reduce the overhead required by your SELECT statements.

Using Cursors

In PL/SQL terms, a cursor is best described as a defined SELECT statement that can be referenced in your code as a variable. Most PL/SQL blocks contain at least one SELECT statement. This statement is often included inside the body of the PL/SQL block, as shown in Listing 10.3.

Listing 10.3 A SELECT statement inside the body of a PL/SQL block.

 DECLARE    vLastName   varchar2 (20); BEGIN    SELECT last_name    INTO   vLastName    FROM   STUDENTS    WHERE  ssn = '999999999'; END; 

This query returns a single row (at least, we re assuming that there is one distinct social security number per student). While there s nothing wrong with the SELECT statement itself, there is a performance problem associated with its use inside the PL/SQL block.

Oracle executes two fetches to return this single row of data. The first fetch returns the row of data returned by the query. The second fetch is performed to make sure that there are no more rows that satisfy the conditions of the query. Any SELECT statement inside a PL/SQL block will always perform an extra fetch for this purpose.

This extra fetch can be avoided if the SELECT statement is implemented by using a cursor, as shown in Listing 10.4.

Previous

Table of Contents

Next