WHERE Clause Tips
Oracle Tips by
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
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
Donít join against unnecessary tables.
Join only columns of the same datatype and
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.
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
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
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.