 |
|
Table Access Hints
Oracle Tips by
Burleson
|
The following hints are used for specialized
table access methods. In sophisticated queries, this class of hints
can be used to direct each step in the access of multiple table
accesses within a query.
The full Hint
The full hint requests the
bypassing of indexes and invokes a full-table scan. This is often used
in conjunction with a parallel hint. The most common use of the
full hint is in cases where you have determined that an index
range scan retrieves a significant number of table blocks and a
parallel full-table scan will improve the speed of the query.
Cluster Table Hints
Oracle provides a specialized table storage
mechanism as an alternative to row-sequenced tables. Oracle clusters
are used in cases where two tables have a one-to-many relationship and
the vast majority of queries access the tables from owner to member
(Figure 12-5).
Figure 5: An Oracle
cluster with multiple tables
This table storage technique stores the owner
and member table rows on adjacent blocks such that a single block I/O
will retrieve rows from both tables. In order to improve the speed of
table joins in a cluster, Oracle provides a hash hint and a
cluster hint.
The use_hash Hint
The use_hash hint explicitly chooses a hash
scan to access the specified cluster table.
select /*+
hash */
emp.ename,
deptno
from
emp e,
dept d
where
e.deptno = d.deptno
and
deptno = 20;
The cluster Hint
The cluster hint explicitly chooses a
cluster scan to access the specified table.
select /*+
cluster */
emp.ename,
deptno
from
emp e,
dept d
where
e.deptno = d.deptno
and
deptno = 20;
The no_expand Hint
The no_expand hint prevents the
cost-based optimizer from considering OR expansion for queries having
OR conditions or IN lists in the where clause. Usually, the
optimizer considers using OR expansion and uses the no_expand
method if it decides the cost is lower than not using it. This OR
expansion is related to optimizer internals and does not mean that the
logic itself will be changed and return a different result set.
The nocache Hint
In Oracle8 and beyond, the
nocache hint directs that table blocks specified for the KEEP
pool be placed at the midpoint of the DEFAULT pool instead of
the KEEP pool.
This hint is rarely used in SQL tuning because
all of the small tables should properly be placed in the KEEP pool,
and there is never a need to change this behavior.
The ordered Hint
In the cost-based optimizer, the ordered
hint requests that the tables should be joined in the order that they
are specified in the from clause, with the first table in the
from clause specifying the driving table.
The ordered hint is commonly used in
conjunction with other hints to ensure that multiple tables are joined
in their proper order. For example, we may have a query that joins
five tables together, and we want several of the joins to use a hash
join and other tables to use a nested loop join. The ordered
hint is very common in tuning data warehouse queries that join more
than four tables together.
TIP: Large n-way table joins with seven or more
tables can often take more than 30 minutes to parse the SQL. This is
because Oracle must evaluate all possible table join orders. For
example, with eight tables, Oracle must evaluate 8!, or 40,320,
possible join combinations. Most people use the ordered hint to
bypass this very expensive and time-consuming SQL parsing operation.
For example, the following query uses the
ordered hint to join the tables in their specified order in the
from clause (emp, dept, sal, bonus). We further refine the
execution plan by specifying that the emp to dept join
use a hash join and the sal to bonus join use a nested
loop join.
select /*+
ordered use_hash (emp, dept) use_nl (sal, bonus) */
from
emp,
dept,
sal,
bonus
where . . .
The ordered_predicates Hint
The ordered_predicates hint is a
specialized hint that is specified in the where clause of a
query, and it directs the order in which the Boolean predicates in the
where clause are evaluated. To see how this hints works, let’s
review the standard method used by the CBO to evaluate SQL predicates.
1.
Subqueries are evaluated before the outer Boolean conditions in
the where clause.
2.
All Boolean conditions without built-in functions or subqueries
are evaluated in their order in the where clause.
3.
With Boolean predicates with built-in functions, the optimizer
computes the cost of each predicate and evaluates them in increasing
order of their costs.
These evaluation rules can be overridden by
using the ordered_predicates hint. This hint is the equivalent
of re-sequencing Booleans in the where clause for the RBO, where
the where clause items are evaluated in the order that they
appear in the query.
The push_subq Hint
The push_subq hint causes all subqueries
in the query block to be executed at the earliest possible place in
the execution plan. Normally, subqueries that are not merged are
executed as the last step in the execution plan. If the subquery is
relatively inexpensive and reduces the number of rows significantly,
then it improves the overall performance to evaluate the subquery as
soon as possible. The push_subq hint has no effect if the
subquery is using a sort merge join, or when the subquery references a
remote table. For more details on this hint for SQL tuning, see
Chapter 19.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
