Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

12.4 Improving on EXPLAIN PLAN Results

If
you don't like the results you get from EXPLAIN
PLAN, you can change how Oracle executes your query. Generally
speaking, these things fall into the following three categories:

Restating the query Creating or modifying indexes Using hints
First, however, you have to be sure the default execution path is a
bad one. This isn't as easy as you may think.

12.4.1 Knowing Good Results from Bad

Knowing a good execution plan from a bad one requires some degree of
experience and judgment. It helps to understand your data. In many
cases, it may not be enough to look at the plan. You may have to do
some benchmarking as well.

Consider the issue of doing a full table scan, reading all the rows
in the table, to find rows for a query. On the surface, reading the
entire table to find the desired rows seems like an inefficient
approach. Many people avoid it, thinking that an indexed retrieval is
better. But this isn't necessarily the case. If you
have a reasonably large table and are searching for one or two rows,
then a full table scan is an inefficient approach. However, if you
are retrieving or summarizing a large percentage of the rows in the
table, then a full table scan will likely outperform an indexed
retrieval. The problem is that somewhere between these two extremes
lies a large gray area. That's where you have to do
some benchmarking and use some judgment based on your expectations of
what the query will be asked to do when it is in production.

Here are some questions to ask yourself as you look at an execution
plan:

Is a table scan being used to retrieve only a small percentage of
rows from a large table? If so, you may want to create an index.

Is an index scan being used when you are retrieving, or summarizing,
a large percentage of a table's rows? If so, you may
be better off forcing a full table scan.

Is Oracle using the most selective index? An index on a YES/NO field
would typically be much less selective than an index on last name,
for example.

Is Oracle joining the largest table last, and the most selective
table first? It's generally better to eliminate as
many rows as possible prior to any joins.

Determining an execution plan to use for a statement that
isn't performing well is perhaps
the most crucial step in any tuning exercise.
The only author I've ever seen address this problem
by providing any kind of repeatable and deterministic methodology is
Dan Tow in his book SQL Tuning
(O'Reilly). If you are tuning SQL statements and
require a reliable method for determining the optimal execution plan
for a statement, I recommend that you read Dan's
book.

12.4.2 Creating Indexes

Creating indexes is an easy way to affect
a query. If, for example, you have a large employee table (much
larger than the one used in this book), keyed on
employee_id, and your query is searching for
employees by name, then Oracle will do a full table scan for each
name lookup. The response time will be poor, and your users will be
unhappy. Creating an index on the employee_name
column would improve your results a great deal.

Don't
overlook the possibility of creating a multicolumn index even if you
don't use all the index columns in your
query's WHERE clause. Suppose that you frequently
execute the following query, which searches for an employee by name
and displays that employee's current billing rate:

SELECT employee_name, employee_billing_rate
FROM employee
WHERE employee_name = :emp_name;

If you index the employee table by name, Oracle will look up the name
in the index, get the ROWID, read the correct employee row, and
return the billing rate. However, it takes an extra read to fetch the
employee record. You can eliminate that extra read by creating an
index such as this one:

CREATE INDEX employee_by_name ON employee
(employee_name, employee_billing_rate);

Because the index contains the
employee_billing_rate column, Oracle
doesn't need to read the actual employee record to
retrieve it. Oracle recognizes that all the columns needed to satisfy
the query are in the index, and it will take advantage of that fact.

12.4.3 Rewriting the Query

Sometimes you can
restate a query, get the results that you want, and have it run much
more efficiently. Example 12-8 shows a query listing
all employees who have ever charged time to project 1001. To generate
that listing, the query joins employee and
project_hours.

Example 12-8. The join approach to listing employees who have charged time to a project

SET AUTOTRACE ON
SELECT DISTINCT employee.employee_id, employee.employee_name
FROM employee, project_hours
WHERE employee.employee_id = project_hours.employee_id
AND project_hours.project_id = 1001;
EMPLOYEE_ID EMPLOYEE_NAME
----------- ----------------------------------------
101 Marusia Churai
102 Mykhailo Hrushevsky
104 Pavlo Virsky
105 Mykola Leontovych
107 Lesia Ukrainka
108 Pavlo Chubynsky
110 Ivan Mazepa
111 Taras Shevchenko
112 Igor Sikorsky
113 Mykhailo Verbytsky
114 Marusia Bohuslavka
116 Roxolana Lisovsky
12 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=93 Bytes=23
25)
1 0 SORT (UNIQUE) (Cost=5 Card=93 Bytes=2325)
2 1 MERGE JOIN (Cost=4 Card=93 Bytes=2325)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (C
ost=2 Card=12 Bytes=228)
4 3 INDEX (FULL SCAN) OF 'EMPLOYEE_PK' (INDEX (UNIQUE))
(Cost=1 Card=12)
5 2 SORT (JOIN) (Cost=2 Card=93 Bytes=558)
6 5 INDEX (RANGE SCAN) OF 'PROJECT_HOURS_PK' (INDEX (UNI
QUE)) (Cost=1 Card=93 Bytes=558)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
782 bytes sent via SQL*Net to client
511 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
12 rows processed The query in Example 12-8 can be restated using an
EXISTS predicate instead of joining the employee
and project_hours tables together. Example 12-9 shows that version.

Example 12-9. The EXISTS approach to listing employees who have charged time to projects 1001 or 1002

SET AUTOTRACE ON
SELECT employee_id, employee_name
FROM employee
WHERE EXISTS (SELECT *
FROM project_hours
WHERE project_hours.project_id = 1001
AND project_hours.employee_id = employee.employee_id);
EMPLOYEE_ID EMPLOYEE_NAME
----------- ----------------------------------------
101 Marusia Churai
102 Mykhailo Hrushevsky
104 Pavlo Virsky
105 Mykola Leontovych
107 Lesia Ukrainka
108 Pavlo Chubynsky
110 Ivan Mazepa
111 Taras Shevchenko
112 Igor Sikorsky
113 Mykhailo Verbytsky
114 Marusia Bohuslavka
116 Roxolana Lisovsky
12 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=12 Bytes=30
0)
1 0 MERGE JOIN (SEMI) (Cost=4 Card=12 Bytes=300)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (Cos
t=2 Card=12 Bytes=228)
3 2 INDEX (FULL SCAN) OF 'EMPLOYEE_PK' (INDEX (UNIQUE)) (C
ost=1 Card=12)
4 1 SORT (UNIQUE) (Cost=2 Card=93 Bytes=558)
5 4 INDEX (RANGE SCAN) OF 'PROJECT_HOURS_PK' (INDEX (UNIQU
E)) (Cost=1 Card=93 Bytes=558)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
782 bytes sent via SQL*Net to client
511 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
12 rows processed The performance results from Examples Example 12-8
and Example 12-9 are a bit inconclusive. When you
compare estimated costs, you'll see a cost of 5 for
Example 12-8 and a cost of 4 for Example 12-9. Look further at the execution plans, and
you'll see that Example 12-9 avoids
the sort triggered in Example 12-8 by the use of
DISTINCT. Interestingly, the number of logical reads (consistent
gets) is lower in the plan with the higher estimated cost,
potentially leaving you in a quandary as to which choice to make
here. My own feeling is to go with Example 12-9,
which avoids the sort because the number of rows to be sorted will
only grow as the project_hours table grows.

The cost of the sort in Example 12-9
doesn't come through in the AUTOTRACE statistics.
One approach to getting a better handle on such costs is to generate
the kind of SQL trace data that Cary Millsap and Jeff Holt talk about
in their book, Optimizing Oracle Performance
(O'Reilly).

12.4.4 Using Hints

Rather
than allowing Oracle to have total control over how a query is
executed, you can provide specific directions to the optimizer
through the use of hints.
A
hint , in Oracle, is an optimizer directive
embedded in a SQL statement in the form of a comment. Here is a query
with an optimizer hint telling Oracle to do a full table scan:

SELECT /*+ FULL(employee) */
employee_id, employee_name, employee_billing_rate
FROM employee
WHERE employee_name = 'Igor Sikorsky';

The hint in this case is FULL(employee), which
tells Oracle to do a full table scan of the
employee table. Oracle will honor this hint and
perform a full table scan even if there happens to be an index on the
employee_name field.

12.4.4.1 Syntax for a hint

A hint applies to a single SQL statement, and hints may be specified
only for SELECT, INSERT, UPDATE, DELETE, and MERGE
statements. A hint takes the form of a
specially formatted comment and must appear immediately following the
keyword that begins the statement:

keyword
/*+ [hint
[hint
...]] */ in which:

keyword
Is the keyword that begins the statement. This will be one of the
keywords SELECT, INSERT, UPDATE, DELETE, or MERGE.

hint
Is the hint, sometimes with one or more arguments enclosed in
parentheses. Tables Table 12-3 through Table 12-10 provide a list of the hints available in
Oracle Database 10 g , Release 1. Hints
aren't case-sensitive. A single comment may contain
more than one hint, as long as the hints are separated by at least
one space.

Oracle allows you to intersperse comments with your hints.

Here are some examples of how hints may be specified:

SELECT /*+ FULL(employee) */ employee_id, employee_name
FROM employee
WHERE employee_billing_rate > 100;
SELECT /*+ FULL(e) do a full table scan on the employee table, because
most employees do have billing rates > 100. */
employee_id, employee_name
FROM employee e
WHERE employee_billing_rate > 100;

In the second example, the table name is employee,
but an alias of e has been given. The hint for the
table uses the same alias and is specified as
FULL(e). Whenever an alias is used, you must use
the alias name in any hints for the table. Be careful about this
because hints are specially formatted comments, and you
won't get any error messages as the result of a
malformed hint.

If you want to supply multiple hints for a statement, they must all
appear in the same comment:

SELECT /*+ FULL(employee) first_rows */
employee_id, employee_name
FROM employee
WHERE employee_billing_rate > 100;

When subqueries are used, they are allowed to have their own hints.
The hint for a subquery follows immediately after the keyword that
starts the subquery:

SELECT /*+ FIRST_ROWS */ employee_id, employee_name
FROM employee
WHERE exists (SELECT /*+ FULL(project_hours) */*
FROM project_hours
WHERE project_hours.project_id = 1001
AND project_hours.employee_id = employee.employee_id);

When using hints, be very careful to get the syntax right. Because
hints are embedded in statements as comments, Oracle
can't do any syntax checking. Oracle treats any
incorrectly specified hint as a comment. In addition, you should do
an EXPLAIN PLAN after you code your hints to be sure that the
optimizer is really doing what you think you told it to do.

12.4.4.2 Specifying table and index names

Many hints take one or more table names as arguments. The FULL hint,
for example, takes one table name as an argument.

/*+ FULL(employee) */ Some access method hints are index-related and allow you to specify
one or more indexes to be used. In many cases, as with the INDEX
hint, you have the choice of specifying an index name or not. The
following hint, for example, tells Oracle that you want to do an
index scan on the employee table, but
it's up to Oracle to pick the index:

/*+ INDEX(employee) */ This is useful if you think Oracle will make the correct choice, or
if you don't want to hardcode an index name into the
hint. You have the option, however, of specifying the exact index to
use. Here's an example:

/*+ INDEX(employee employee_by_name) */ You may specify a list of indexes, and Oracle will choose from the
indexes in that list. If, for example, you had seven indexes on the
employee table but you believed that only two
would be useful for the query in question, you could specify a hint
like this:

/*+ INDEX(employee employee_by_name, employee_by_billing_rate) */ This tells Oracle that you want to use an index scan to access the
employee table, and that you want to use either
the name index or the billing rate index.

12.4.4.3 Hint conflicts and applicability

Whenever two hints are in conflict, Oracle will ignore at least one
of them. Oracle ignores hints that can't be
followed. For example, take a look at the following query:

SELECT /*+ USE_CONCAT */
employee_id, employee_name
FROM employee
WHERE employee_name = 'Jeff Gennick';

The USE_CONCAT hint makes no sense here because
the query doesn't contain an OR condition. You
can't break this into two queries and then UNION the
results together, so Oracle will ignore the hint.

A bad hint will be honored, however, whenever it is implementable.
The following query contains a hint to do an index scan on the
primary key index for the employee table:

SELECT /*+ INDEX(employee employee_pk) */
employee_name
FROM employee
WHERE employee_name = 'Jeff Gennick';

The primary key for employee is the
employee_id field. An index on
employee_name does exist. The query seeks one
record based on the employee name. Even though it makes perfect sense
to look up the name in the name index, Oracle will honor the request
to use the primary key index. Here is the execution plan for this
statement:

0 SELECT STATEMENT Cost = 2
1 TABLE ACCESS BY INDEX ROWID EMPLOYEE
2 INDEX FULL SCAN EMPLOYEE_PK Oracle is going to read every entry in the primary key index,
retrieve the associated row from the employee table, and check the
name to see if it has a match. This is worse than a full table scan.
Oracle does this because the hint requested it and because it
physically can be done; so be careful what you ask for, and check the
results.

12.4.4.4 Hint query blocks

Oracle Database 10 g introduces a new,
query block syntax to hints. When you have a
statement, such as a SELECT, that consists of a main statement and
one or more subqueries, each statement and subquery is a
query block.
The new query block syntax lets you refer to those query blocks by
name from a hint.

You can name the query blocks in a statement using the QB_NAME hint.
The statement in Example 12-10 contains two query
blocks that are given the names main and
sub respectively. The two FULL hints use query
block names to fully qualify their respective table references, with
the end result that both instances of employee are
accessed via a full table scan.

Example 12-10. A statement with named query blocks

SELECT /*+ QB_NAME(main) FULL(@main e1) FULL(@sub e2) */
e1.employee_name, employee_hire_Date
FROM employee e1
WHERE EXISTS (SELECT /*+ QB_NAME(sub) */ e2.employee_id
FROM employee e2
WHERE employee_hire_date >=
TO_DATE('1-Jan-2000','dd-mon-yyyy')
AND e1.employee_id = e2.employee_id);

If you don't name the query blocks in a statement,
Oracle will generate query block names for you. To see those names
automatically, run an EXPLAIN PLAN on your statement followed by a
query using the ALL option of DBMS_XPLAN.DISPLAY:

SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));

12.4.5 Oracle's Hint Syntax

The following subsections categorize the various hints available in
Oracle Database 10 g and provide a quick summary
of the hints.

The hints in Tables Table 12-3 through Table 12-10 are valid for Oracle Database
10 g . Many, but not all of those hints work in
previous releases. Hints from previous releases that are deprecated
in Oracle Database 10 g aren't
listed here.

12.4.5.1 Optimizer goal hints

Optimization goal hints allow you
to influence the optimizer's overall goal when
formulating an execution plan. You may, for example, specify that you
want the plan optimized to return the first record as quickly as
possible. Table 12-3 gives a list of these hints.

Table 12-3. Optimizer goal hints
Hint	Description
ALL_ROWS	Tells the optimizer to produce an execution plan that minimizes resource consumption
FIRST_ROWS(n)	Tells the optimizer to generate a plan that gets to the first `n` rows as soon as possible

You should avoid the now obsolete RULE hint if at all possible. That
hint causes the rule-based optimizer to be used. The rule-based
optimizer uses a fixed set of rules when determining the execution
plan for a statement and doesn't attempt to factor
in the ultimate cost of executing that plan. The cost-based
optimizer, on the other hand, bases its decision on the estimated I/O
and CPU overhead required by various alternative plans. Although
Oracle still supports the rule-based optimizer, it
hasn't been enhanced in years,
won't be enhanced in the future, and will be
de-supported at some point. Oracle is putting its development effort
into the cost-based optimizer.

12.4.5.2 Access method hints

Access method hints allow you to
control the way data are accessed. For example, you can tell Oracle
to do a full table scan or to use an index when accessing a table.
You can name the specific index to be used. Table 12-4 provides a list of these hints.

Table 12-4. Access method hints
Hint	Description
`FULL(table)FULL(@block table)`	Requests a full table scan of the specified table, regardless of any indexes that may exist.
`CLUSTER(table)CLUSTER(@block table)`	Tells Oracle to do a cluster scan of the specified table. This hint is ignored if the table is not clustered.
`HASH(table)HASH(@block table)`	Tells Oracle to do a hash scan of the specified table. This hint is ignored if the table is not clustered.
`INDEX(table [index . . . ])INDEX(@block table [index . . . ])`	Tells Oracle to access the specified table via an index scan. Optionally, you may specify the index to use; otherwise, Oracle chooses the index. You may also specify a list of indexes to choose from, and Oracle will choose from that list.
`NO_INDEX(table[index . . . ])NO_INDEX(@block table[index . . . ])`	Prevents Oracle from using indexes on a table. Optionally, you may specify a list of indexes to avoid, thus allowing all others.
`INDEX_ASC(table [index . . . ])INDEX_ASC(@block table [index . . . ])`	Similar to the INDEX hint but tells Oracle to scan the index in ascending order.
`INDEX_COMBINE(table [index . . . ])INDEX_COMBINE(@block table [index . . . ])`	Tells Oracle to use some combination of two indexes. You may specify the indexes to choose from, or let Oracle make the choice.
`INDEX_JOIN(table [index . . . ])INDEX_JOIN(@block table [index . . . ])`	Instructs the optimizer to resolve conditions on two or more columns by joining indexes containing those columns.
`INDEX_DESC(table [index . . . ])INDEX_DESC(@block table [index . . . ])`	Similar to INDEX_ASC, but forces Oracle to scan the index in descending order.
`INDEX_FFS(table [index . . . ])INDEX_FFS(@block table [index . . . ])`	Tells Oracle to do a fast full index scan.
`NO_INDEX_FFS(table [index . . . ]) NO_INDEX_FFS(@block table [index . . . ])`	Inhibits the use of a fast full index scan. New in Oracle Database 10g.
`INDEX_SS(table [index . . . ])INDEX_SS(@block table [index . . . ])`	Chooses an index skip scan. New in Oracle Database 10g.
`INDEX_SS_ASC(table [index . . . ])INDEX_SS_ASC(@block table [index . . . ])`	Chooses an index skip scan in ascending order. New in Oracle Database 10g.
`INDEX_SS_DESC(table [index . . . ])INDEX_SS_DESC(@block table [index . . . ])`	Chooses an index skip scan in descending order. New in Oracle Database 10g.
`NO_INDEX_SS(table [index . . . ]) NO_INDEX_SS(@block table [index . . . ])`	Inhibits the use of an index skip scan. New in Oracle Database 10g.

12.4.5.3 Query transformation hints

Sometimes Oracle's query optimizer will rewrite a
query in a different form, from which it is possible to generate a
more efficient (you hope) execution plan. The query transformation
hints in Table 12-5 provide some control over
whether and how your queries get rewritten.

Table 12-5. Query transformation hints
Hint	Description
`NO_QUERY_TRANSFORMATION`	Prevents any transformation at all. New in Oracle Database 10g.
`NO_EXPAND NO_EXPAND(@block)`	Prevents the optimizer from expanding IN predicates into a series of OR predicates.
`REWRITE REWRITE(view [view . . . ]) REWRITE(@block view [view...]])`	Specifies that a query be rewritten in terms of materialized views whenever possible, without regard to the cost.
`NO_REWRITE(@block)`	Disallows materialized view rewrites. New in Oracle Database 10g (renamed from Oracle9i Database NOREWRITE).
`MERGE MERGE(view) MERGE(@block) MERGE(@block view)`	Merges a view into a query.
`NO_MERGE NO_MERGE(view) NO_MERGE(@block) NO_MERGE(@block view)`	Prevents views from being merged into a query.
`STAR_TRANSFORMATION STAR_TRANSFORMATION(@block)`	Tells Oracle to transform the query into a star query if possible and uses the best plan for such a query.
`NO_STAR_TRANSFORMATION NO_STAR_TRANSFORMATION(@block)`	Prevents transformation into a star query. New in Oracle Database 10g.
`FACT(table) FACT(@block view)`	Indicates that a table should be considered as a fact table in a star query.
`NO_FACT(@block) NO_FACT(@block table)`	Indicates that a table is not a fact table.
`USE_CONCAT USE_CONCAT(@block)`	Turns a query with OR conditions into two or more queries unioned together with a UNION ALL.
`UNNEST UNNEST(@block)`	Specifies that subqueries be merged into the body of the main query, when possible.
`NO_UNNESTNO_UNNEST(@block)`	Prevents subqueries from being merged into the main query.

12.4.5.4 Join order hints

Join order hints allow you to exercise some control over the order in
which Oracle joins tables. Only three of them exist, and they are
listed in Table 12-6.

Table 12-6. Join order hints
Hint	Description
`ORDERED`	Tells Oracle to join tables left to right, in the same order in which they are listed in the FROM clause.
`LEADING(table [table . . . ])LEADING(@block table [table . . . ])`	Specifies one or more leading tables for a join query. Tables are joined in the order listed, if possible; otherwise, the hint is ignored.

12.4.5.5 Join operation hints

Join
operation hints allow you to control the manner in which two tables
are joined. Oracle uses three basic methods whenever two tables are
joined: the merge join, the nested loops join, and the hash join.

Merge join
This type of join is done by sorting the rows from each table by the
join columns. Once the two rowsets have been sorted, Oracle reads
through both and joins any matching rows together. A merge may use
fewer resources than the other options, but you have to wait for all
the records to be sorted before you get the first one back. You have
to have enough memory and temporary disk space to handle the sort.

Nested loops join
The method used for a nested loops join
corresponds to the mental image most
people have in mind when they think of joining tables. Oracle picks
one table as the driving table and reads through that table row by
row. For each row read from the driving table, Oracle looks up the
corresponding rows in the secondary table and joins them together.
Because no sort is involved, a nested loops join will get you the
first record back more quickly than a merge join. For the same
reason, a nested loops join doesn't require large
amounts of disk space and memory. However, a nested loops join may
result in a considerably greater number of disk reads than a merge
join.

Hash join
A hash
join is similar to a merge join, but a sort
is not required. A hash table is built in memory to allow quick
access to the rows from one of the tables to be joined. Then rows are
read from the other table. As each row is read from the second table,
the hash function is applied to the join columns, and the result is
used to find the corresponding rows from the first table.

Table 12-7 lists the join operation hints.

Table 12-7. Join operation hints
Hint	Description
`USE_NL(table [table . . . ])USE_NL(@block table [table . . . ])`	Tells Oracle to use a nested loop when joining the specified table(s). The table(s) specified by this hint will be the one(s) accessed by the innermost loop(s). New in Oracle Database 10g.
`USE_MERGE(table [table . . . ])USE_MERGE(@block table[table . . . ])`	Tells Oracle to use the sort merge method when joining the listed table(s).
`USE_HASH(table [table . . . ])USE_HASH(@block table[table . . . ])`	Tells Oracle to use a hash join for the specified table(s).
`NO_USE_NL(table [table . . . ])NO_USE_NL(@block table [table . . . ])`	Prevents the use of nested loops when joining the specified table(s). New in Oracle Database 10g.
`USE_NL_WITH_INDEX(table [index [index . . . ]])USE_NL_WITH_INDEX(@block table [index [index . . . ]])`	Specifies a nested loops join in a manner similar to USE_NL, but requires the use of at least one index having columns corresponding to those in the join predicates. New in Oracle Database 10g.
`NO_USE_MERGE(table [table . . . ])NO_USE_MERGE(@block table [table . . . ])`	Prevents the use of merge joins to the listed table(s). New in Oracle Database 10g.
`NO_USE_HASH(table [table . . . ])NO_USE_HASH(@block table[table . . . ])`	Prevents the use of hash joins to the listed table(s). New in Oracle Database 10g.

12.4.5.6 Parallel execution hints

The hints shown in Table 12-8 allow you to
influence the way Oracle executes a query in a parallel processing
environment. In an environment with a single CPU, parallel processing
is not possible, and these hints are
ignored.

Table 12-8. Parallel execution hints
Hint	Description
`PARALLEL(table)PARALLEL(table degree)PARALLEL(table DEFAULT)PARALLEL(@block table)PARALLEL(@block table degree)PARALLEL(@block table DEFAULT)`	Specifies that data from `table` be processed in parallel, and optionally allows you to specify the `degree` of parallelism. Use DEFAULT to request the instance-wide default degree of parallelism.
`NO_PARALLEL(table)NO_PARALLEL(@block table)`	Tells Oracle not to access the specified table in parallel. New in Oracle Database 10g (renamed from Oracle9i Database NOPARALLEL).
`PARALLEL_INDEX(table [index [index...]] degree)PARALLEL_INDEX(table [index [index...]] DEFAULT)PARALLEL_INDEX(@block table [index [index...]] degree)PARALLEL_INDEX(@block table [index [index...]] DEFAULT)`	Similar to PARALLEL, but specifies that indexes are to be range-scanned in parallel.
`PQ_DISTRIBUTE(table outer inner)PQ_DISTRIBUTE(@block table outer inner)`	Specifies how rows from joined tables should be distributed among producer and consumer query servers. See Table 12-9 for a list of valid values for `outer` and `inner`.
`NO_PARALLEL_INDEX(table [index [index...]])NO_PARALLEL_INDEX(@block table [index [index...]])`	Prevents parallel index scans. New in Oracle 10g (renamed from Oracle9i Database NOPARALLEL_INDEX).

Table 12-9. Outer and inner distribution values for PQ hints
Outer	Inner	Description
`HASH`	`HASH`	Rows from each table are mapped to query servers using a hash function on the join keys.
`BROADCAST`	`NONE`	All rows from the outer table are sent to each of the query servers while inner rows are randomly distributed to the query servers.
`NONE`	`BROADCAST`	The reverse of BROADCAST, NONE.
`PARTITION`	`NONE`	Rows from the outer table are mapped to query servers based on the partitioning of the inner table, which must itself be partitioned on the join keys.
`NONE`	`PARTITION`	The reverse of PARTITION, NONE.
`NONE`	`NONE`	Each query server handles a corresponding pair of partitions from the outer and inner tables, both of which must be partitioned on the join key.

12.4.5.7 Other hints

A few hints don't fit neatly into one of the other
categories. These are listed in Table 12-10.

Table 12-10. Other hints
Hint	Description
`CACHE(table)CACHE(@block table)`	Applies only when a full table scan is being performed on the specified table. It tells Oracle to place blocks for that table at the most recently used end of the buffer cache, so they will remain in memory as long as possible. This can be useful for small lookup tables that you expect to access repeatedly.
`NOCACHE(table)NOCACHE(@block table)`	This is the opposite of CACHE, and tells Oracle to place blocks at the least recently used end of the buffer cache, where they will be cleared out as soon as possible.
`PUSH_SUBQPUSH_SUBQ(@block)`	Tells Oracle to evaluate non-merged subqueries as soon as possible during query execution. If you expect a subquery to eliminate a large number of rows, this can result in a performance improvement.
`DRIVING_SITE(table)DRIVING_SITE(@block table)`	This hint applies when you are executing a distributed join, one that joins tables from two or more databases. Without a hint, Oracle will choose which database collects the tables and does the join. By using the hint, you are telling Oracle that you want the join performed by the database containing the specified table.
`APPEND`	Enables direct-path INSERT.
`NOAPPEND`	Explicitly requests conventional INSERT, and disables parallelism.
`PUSH_PREDPUSH_PRED(table) PUSH_PRED(@block)PUSH_PRED(@block table)`	Pushes a join predicate into the specified view.
`NO_PUSH_PREDNO_PUSH_PRED(table)NO_PUSH_PRED (@block)NO_PUSH_PRED(@block table)`	Prevents join predicates from being pushed into views.
`NO_PUSH_SUBQNO_PUSH_SUBQ(@block)`	Relegates non-merged subqueries to the final steps in the execution plan.
`QB_NAME(block)`	Defines a name for a query block. New in Oracle Database 10g.
`CURSOR_SHARING_EXACT`	Prevents the optimizer from replacing literals in a SQL statement with bind variables.
`DYNAMIC_SAMPLING(effort)DYNAMIC_SAMPLING(table effort)DYNAMIC_SAMPLING(@block effort) DYNAMIC_SAMPLING(@block table effort)`	Specifies, on a scale from 0 to 10, the amount of effort put into dynamic sampling to determine more accurate predicate selectivity and statistics for tables and indexes involved in a query.
`SPREAD_MIN_ANALYSIS`	Reduces compile time by omitting some compile-time optimizations of MODEL clause rules. New in Oracle Database 10g.