Question: I need
to understand how to add an Oracle WHERE
clause on my SQL. Can you explain how
the Oracle WHERE clause works?
Answer: The Oracle
WHERE clause is used to apply row filtering
conditions to a SQL query, a way to limit
the number of rows returned by the query.
re are a few important things to know
select
stuff
from
author
where
author_key = 'A103';
There are some important caveats about
the Oracle WHERE clause.
One of the
most common Oracle SQL tuning problems are
full-table scans that are imposed by
invalidating an Oracle WHERE clause predicate with
an Oracle built-in Function (BIF). This
problem is especially prevalent when
constraining SQL queries for date ranges
because of the intrinsic encryption of the
relational database DATE datatype.
Here are details on
using function based indexes.
For example, these
Oracle WHERE clause predicates might invoke an
unexpected full-table scan:
WHERE trunc(ship_date) > trunc(sysdate-7);
WHERE to_char(ship_date,'YYYY-MM-DD')
= '2004-01-04';
Oracle
WHERE clause order can be important to
performance! (only in 10g and previous
releases)
Many people believe that the Oracle cost-based SQL optimizer does not
consider the order that the Boolean
predicates appear in the Oracle WHERE clause.
However, there is some
evidence that this is not complete true, as
evidenced by the ordered_predicates SQL hint.
The ordered_predicates hint (deprecated
in 10g r2) is specified in the WHERE clause
of a query and is used to specify the order
in which Boolean predicates should be
evaluated. In the absence of
ordered_predicates (which is deprecated in
Oracle 10g and beyond), Oracle uses the
following steps to evaluate the order of SQL
predicates:
- Subqueries are
evaluated before the outer Boolean
conditions in the WHERE clause.
- All Boolean
conditions without built-in functions or
subqueries are evaluated in reverse from the
order they are found in the WHERE clause,
with the last predicate being evaluated
first.
- Boolean predicates
with built-in functions of each predicate
are evaluated in increasing order of their
estimated evaluation costs.
Why is
searching for large-table full-table scans
critical to SQL tuning?
If the optimizer gets confused or cannot
find an appropriate index that matches the
WHERE clause, the optimizer will read every
row in the table. Hence large-table
full-table scans often indicate a missing
index or a sub-optimal choice of optimizer
goal.
What
is the relationship between indexes and SQL
performance?
The sole purpose of indexes is to make SQL
queries run faster. If the optimizer detects
an indexes that matches part of the Oracle WHERE
clause of the query, then the optimizer will
use the index to avoid having to read every
row in the table.
Oracle always interrogates the
Oracle WHERE clause of the
SQL statement to see if a matching index
exists and then evaluates the cost to see of
the index is the lowest-cost access method.
By using function-based indexes, the Oracle
designer can create a matching index that
exactly matches the predicates within the
SQL where clause. This ensures that the
query is retrieved with a minimal amount of
disk I/O and the fastest possible speed.
If you experiment with
changing the order of predicates in the
WHERE clause you will notice changes to
the execution plan.
Also see the notes on the
Oracle where clause:
Oracle where clause order can change
performance
Oracle WHERE clause in export
operations
|