| |
 |
|
WHERE Clause Tips
Oracle Tips by
Burleson
|
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.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
