|
About Oracle WHERE clause
One of the
most common Oracle SQL tuning problems are
full-table scans that are imposed by
invalidating a 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
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’;
SQL
WHERE clause order can be important to
performance!
Many people believe that the Oracle cost-based SQL optimizer does not
consider the order that the Boolean
predicates appear in the WHERE clause.
However, there is some
evidence that this is not complete true, as
evidenced by the order_predicates SQL hint.
The ordered_predicates
hint 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, 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 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 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.
|
