oracle sql tunning all hints

上一篇 / 下一篇 2007-12-24 18:25:02

The following is a list of all hints available in Oracle9i. Many of the hints are also available in earlier releases of Oracle. The purpose of this list is not to exhaustively describe the syntax of each hint, but to show the way each hint is most commonly used.
ALL_ROWS
Optimizes for least resource usageto return all rows required by the query. This hint will sometimes override a NESTED LOOP with a SORT MERGE or a HASH JOIN if applied to a SELECT, UPDATE, or DELETE statement when OPTIMIZER_MODE=CHOOSE.
SELECT /*+ ALL_ROWS */ ...

AND_EQUAL (table index1 index2[... index5])
Explicitly merges single-column indexes. A minimum of two indexes must be specified, and no more than five are allowed. Single-column index merges can be incredibly inefficient if the first index in the WHERE returns a lot of rows.
SELECT /*+ AND_EQUAL(horse_owners ho_ndx1
ho_ndx2 ho_ndx3) */
count(*)
FROM horse_owners
WHERE horse_name = 'WILD CHARM'
AND wner = 'Mr M A Gurry'
AND identifier = 14;

APPEND
Allows a direct path insert to a table. Data to be inserted bypasses the buffer cache, and is appended to the end of the table. Integrity constraints are ignored during the load, although I have observed that after the load has taken place, the integrity checks are made and your statement can still fail with an integrity constraint error.
INSERT /*+ APPEND */ * INTO y
SELECT FROM winners;

CACHE (table)
Instructs the optimizer to position all blocks retrieved via a full table scan at the most recently used end of the LRU (Least Recently Used) list in the buffer cache. You would

usually use this hint on small tables, but I have seen sites with a very large amount of memory cache very large tables that are infrequently changed.
SELECT /*+ FULL(winners) CACHE(winners)
*/ count(*)
FROM winners

CHOOSE
Uses the cost-based optimizer if statistics are available for at least one table; otherwise, uses the rule-based optimizer.
SELECT /*+ CHOOSE */

CLUSTER(table)
Forces the use of a cluster scan for the specified table. This hint can only be used for objects that are clustered. A cluster is two or more related tables with the parents and the related child records stored physically next to each other. For example, account1 will have its transactions stored in the same physical block as the account record.
SELECT /*+ CLUSTER(a) */ acct_name
FROM acct a

CURSOR_SHARING_EXACT
Prevents Oracle from translating literals into bind variables even when the CURSOR_SHARING parameter is set to FORCE or SIMILAR. For example:
SELECT /*+ CURSOR_SHARING_EXACT */ name, suburb
FROM emp
WHERE surname = 'GURRY';
If the hint was not used on this SQL statement, and CURSOR_SHARING was set to SIMILAR or FORCE, the `GURRY' value in this example would be translated into a bind variable.

FACT(table)
Tells the cost-based optimizer that the table listed is a fact table and should be treated as such. This hint is used with the STAR_TRANSFORMATION operation.
SELECT /*+ FACT(results) */

FIRST_ROWS or FIRST_ROWS(n)
Optimizes for best response time to return the first n rows required by a query. Statistics do not have to be available for any table involved in the SQL statement; their statistics can be estimated by the optimizer. Other "access path hints" can be included with the FIRST_ ROWS hint, and may override FIRST_ROWS. If you use the (n) option to specify the exact number of rows to be returned, Oracle can make a more precise execution plan decision. The (n) option is only available with Oracle9i and later.

For example:
SELECT /*+ FIRST_ROWS(100) */
This hint:
• Will always choose an index over a full table scan.
• Uses nested loop joins over sort/merge joins, where possible.
• Uses an index to satisfy an ORDER BY clause, where possible.

The optimizer ignores the hints for DELETE and UPDATE statement blocks, and for any SELECT statement block that contains a "grouping" operation (UNION, INTERSECT, MINUS, GROUP BY, DISTINCT, MAX, MIN, SUM, etc.) or a FOR UPDATE clause. Such statements cannot be optimized for best response time, because all rows must be accessed before the first row can be returned.

FULL(table)
Forces the use of a full table scan on the specified table.
SELECT /*+ FULL(emp) */ ename
FROM emp
WHERE commencement_date > sysdate - 7
If a table has an alias, you must specify the alias name in the hint:
SELECT /*+ FULL(a) */ ename
FROM emp a
WHERE a.commencement_date > sysdate - 7

HASH(table)
Forces the use of a hash table scan for the specified table. This hint applies only to tables stored in a cluster.
SELECT /*+ HASH(a) */ acct_name
FROM acct a
A lot of people get this hint mixed up with USE_HASH, which forces a hash join. This is not the same hint!

HASH_AJ
Provides substantial performance improvements by turning a nested loop operation for a NOT IN into a hash join operation. This hint needs to be placed against the SELECT statement in the subquery, not in the main select clause.
SELECT count(*)
FROM horses
WHERE horse_name LIKE 'M%'
AND horse_name NOT IN

( SELECT /*+ HASH_AJ */ horse_name
FROM horse_owners
WHERE owner LIKE '%Lombardo%');

HASH_SJ
Often speeds response times in an EXISTS subquery by returning the rows in the subquery only once.
SELECT count(*)
FROM horses
WHERE horse_name LIKE 'M%'
AND EXISTS
( SELECT /*+ HASH_SJ */ horse_name
FROM horse_owners
WHERE owner LIKE '%Lombardo%'
AND horses.horse_name= horse_owners.horse_name)
There are some restrictions on this hint:
1. There must be only one table listed in the subquery.
2. The hint can't be used in a subquery within a subquery.
3. The subquery must be correlated with an equality predicate, which is a requirement for all hash joins.
4. The subquery must have no GROUP BY clause, CONNECT BY clause, or ROWNUM reference.

INDEX(table [index [index...]])
Forces the use of an indexed table scan for the specified table. You can optionally specify one or more indexes in the hint. If no indexes are included, the optimizer calculates the cost of all indexes for the table, and uses the most efficient (several indexes may be used in tandem). If several indexes are listed, the optimizer calculates the cost of only those indexes that are specified, and uses the most efficient (several indexes from the list may be used in tandem if they are single-column indexes). If a single index is specified, the optimizer performs a scan using that index.
SELECT /*+ INDEX(EMP EMP_NDX1) */
SELECT /*+ INDEX(EMP) */

INDEX_ASC(table [index])
Forces the use of an ascending indexed table scan for the specified table. Oracle will scan indexes in ascending order by default anyway. So why use this hint? Good question! I suppose this hint guarantees that the index will be traversed in ascending order, even if

Oracle decides to behave differently. The exception to the rule is if the index has been created as a reverse key index, e.g., CREATE INDEX POST ON OWNERS (ZIPCODE) REVERSE.
SELECT /*+ INDEX_ASC(EMP EMP_NDX1) */...

INDEX_COMBINE(table [index [index...]])
Explicitly chooses bitmap indexes to access the table information.
SELECT /*+ INDEX_COMBINE(ACCT_TRAN AT_STATE_BMI AT_TYPE_BMI) */

INDEX_DESC(table [index])
Forces the use of a descending indexed table scan for the specified table. By default, Oracle scans indexes in ascending sequence. This hint guarantees that the index will be traversed in descending order. A typical usage of this hint would be to retrieve the latest transactions on your bank account in descending order by date. This hint can be of great value in distributed queries.
SELECT /*+ INDEX_DESC(ACCT_TRANS ACCT_TRANS_DATE_NDX) */...

INDEX_FFS(table [index])
Instructs the optimizer to do a full scan of an index rather than a full scan of a table. The index scan can sometimes run faster, but if and only if every column in the WHERE clause for the specified table exists in the index.
SELECT /*+ INDEX_FFS(ACCT_TRAN AT_STATE_NDX1) */

INDEX_JOIN(table [index] table [index2)]
This hint tells the optimizer to join two indexes as the access path. Typically the execution plan will include a hash join of the two indexes, which can return some performance improvements. In the following example, two of the table's three primary key columns have been used in the WHERE clause (HORSE_NAME and OWNER), as has the leading column (IDENTIFIER) of a non-primary key index.
SELECT /*+ INDEX_JOIN(HORSE_OWNERS HO_NDX2 HO_PK) */
Horse_name, owner
FROM HORSE_OWNERS
WHERE horse_name = 'WILD CHARM'
AND wner = 'Mr M A Gurry'
AND identifier = 10;
As a matter of interest, without the INDEX_JOIN hint, the optimizers will usually only join the single-column indexes.

MERGE(table)

Used to force the merging of a nested (inline) view with the main driving query. In the example given, the GROUP BY inline view is merged with the selection from the OWNERS table.
The hint can also be used for subqueries if the IN statement is uncorrelated; that is, it does not reference join columns in the main query.
SELECT /*+ MERGE(w) */ o.owner,
w.num_wins, o.suburb
FROM owners o,
(SELECT owner, count(*) num_wins
FROM winners
WHERE position = 1
GROUP BY owner) w
WHERE o.owner = w.owner
AND w.num_wins > 15
ORDER BY w.num_wins desc

MERGE_AJ
Provides substantial performance improvements by turning a nested loop operation for a NOT IN into a merge join operation (similar to HASH_AJ). This hint needs to be placed against the SELECT statement in the subquery, not in the main select clause.
SELECT count(*)
FROM horses
WHERE horse_name LIKE 'M%'
AND horse_name NOT IN
( SELECT /*+ MERGE_AJ */ horse_name
FROM horse_owners
WHERE owner LIKE '%Lombardo%');

MERGE_SJ
This hint will often speed response times in an EXISTS subquery by returning the rows in the subquery only once.
SELECT count(*)
FROM horses
WHERE horse_name LIKE 'M%'
AND EXISTS
( SELECT /*+ MERGE_SJ */ horse_name
FROM horse_owners
WHERE owner LIKE '%Lombardo%'

AND horses.horse_name= horse_owners.horse_name)
There are some restrictions on this hint:
1. There must be only one table in the subquery.
2. The subquery can't be a subquery within a subquery.
3. The subquery must be correlated with an equality predicate.
4. The subquery must have no GROUP BY clause, CONNECT BY clause, or ROWNUM reference.

NL_AJ
Occasionally provides some performance improvements by forcing a nested loop operation for a NOT IN. However, nested loop performance is often inferior to that of the hash join and the sort merge join. The hint needs to be placed against the SELECT statement in the subquery, not in the main select clause.
SELECT count(*)
FROM horses
WHERE horse_name LIKE 'M%'
AND horse_name NOT IN
( SELECT /*+ NL_AJ */ horse_name
FROM horse_owners
WHERE owner LIKE '%Lombardo%');

NL_SJ
This hint is similar to the HASH_SJ and MERGE_SJ hints, but uses the nested loop operation for the semi join.
SELECT count(*)
FROM horses
WHERE horse_name LIKE 'M%'
AND EXISTS
( SELECT /*+ NL_SJ */ horse_name
FROM horse_owners
WHERE owner LIKE '%Lombardo%'
AND horses.horse_name=
horse_owners.horse_name)
There are some restrictions on this hint:
1. There must be only one table in the subquery.
2. It can't be a subquery within a subquery.

3. The subquery must be correlated with an equality predicate.
4. The subquery must have no GROUP BY, CONNECT BY, or ROWNUM reference.

NO_EXPAND
Prevents a query from being broken up into separate pieces, which is almost the reverse of the USE_CONCAT hint.
SELECT /*+ NO_EXPAND */ COUNT(*)
FROM horse_owners
WHERE identifier < 10 OR identifier > 20

NO_FACT(table)
Tells the cost-based optimizer that the table listed is not a fact table and should not be treated as such. This hint is used with STAR_TRANSFORMATION processing.
SELECT /*+ NO_FACT(results) */

NO_MERGE(table)
Prevents the merging of a nested (inline) view.
SELECT /*+ NO_MERGE(w) */ o.owner,
w.num_wins, o.suburb
FROM owners o,
(SELECT owner, count(*) num_wins
FROM winners
WHERE position = 1
GROUP BY owner) w
WHERE o.owner = w.owner
AND w.num_wins > 15
ORDER BY w.num_wins desc

NO_PUSH_PRED(table)
Prevents the join predicate from being pushed into an inline view.
SELECT /*+ NO_PUSH_PRED(v) */ count(*)
FROM horses h,
(SELECT w.horse_name, o.owner,
w.position
FROM winners w, owners o
WHERE w.owner = o.owner) v
WHERE h.horse_name = v.horse_name
AND v.position = 1
NO_UNNEST

Prevents the merging of a subquery into the main statement body. Can only be used when UNNEST_SUBQUERY=TRUE.
SELECT /*+ NO_UNNEST */ count(*)
FROM horses
WHERE horse_name LIKE 'M%'
AND horse_name NOT IN
( SELECT horse_name
FROM horse_owners
WHERE owner LIKE '%Lombardo%');

NOAPPEND
The opposite of APPEND; results in a conventional insert into a table. There is no guarantee that the data will be appended at the end of the table. The rows to be inserted do not bypass the buffer cache, and integrity constraints are respected.
INSERT /*+ NOAPPEND */ * INTO y
SELECT FROM winners;
SELECT /*+ FULL(winners) NOCACHE(winners)
*/ count(*)
FROM winners

NOCACHE(table)
Instructs the optimizer to position all blocks fetched for the specified table at the leastrecently used end of the LRU list in the buffer cache when performing a full table scan. This is the normal behavior. for a full table scan.

NOINDEX(table [index [index...]])
Eliminates the indexes listed from usage in the execution plan for a query.
SELECT /*+ NOINDEX(EMP EMP_NDX1) */
If a table is specified without an index, no indexes on the table can be used.
SELECT /*+ NOINDEX(EMP) */

NOPARALLEL(table)
Prevents Oracle from using parallelism (multiple processes) to scan the specified table. For example, assume you enable parallelism as follows:
ALTER TABLE x PARALLEL 2;
Oracle now attempts to use two processes in parallel whenever the table needs to be scanned. The following statement uses the NOPARALLEL hint to prevent that parallelism from occurring:
SELECT /*+ NOPARALLEL(x) */ COUNT(*)
FROM x;

NOPARALLEL_INDEX(table, index)
Ensures that parallel index processing does not occur for a partitioned index.
SELECT /*+ NOPARALLEL_INDEX(emp, emp_ndx) */

NOREWRITE
Prevents Oracle from utilizing materialized views based on a selected table. It is the exact reverse of the REWRITE hint.
SELECT /*+ NOREWRITE */ horse_name, owner, position, COUNT(*)
FROM results
GROUP BY horse_name, owner, position

ORDERED
Forces the optimizer to join tables in the same order as that in which they are specified in the FROM clause (left to right). This hint can give enormous performance gains in a reporting environment. It is also usually the case that the larger the number of tables in the FROM clause, the larger the benefits from this hint. Following is an example of its use:
SELECT /*+ ORDERED */
acct_name, trans_date, amount,
dept, address
FROM trans t, account a, category c ,
branch b, zip z
WHERE t.trans_date > sysdate - 30
AND a.zip = z.zip
AND z.state = 'WA'
AND t.account between 700000 and
799999
AND t.account = a.account
AND a.account = 'ACTIVE'
AND a.category = c.category
AND c.catgory = 'RETAIL'
AND t.branch_id = b.branch_id
AND b.branch = 'BELLEVUE'
Usually the driving index, and thus the driving table, for a query are determined by the type of index, how many columns are in the index, the number of rows in the index, and so on. For example, a table that has a UNIQUE index column equality check in the WHERE clause will become a driving table over a table that has a NON-UNIQUE column specified in the WHERE clause.

Interestingly, if all things are equal, the cost-based optimizer will use the left to right order in the FROM clause, which is the exact reverse of the rule-based optimizer. However, in a complex query, it is rare to find all things equal in the WHERE clause. Use this hint to guarantee the join order.

ORDERED_PREDICATES
Causes WHERE clause predicates to be evaluated in the order in which they are listed in the WHERE clause. If you do not specify ORDERED_PREDICATES, Oracle will evaluate subqueries and user functions first.
SELECT ...
...
WHERE /*+ ORDERED_PREDICATES */
This is the only hint that goes in the WHERE clause rather than after the keyword that begins the statement.

PARALLEL(table [,integer] [,integer])
Explicitly specifies the actual number of concurrent query servers that will be used to service the query. The first optional value specifies the degree of parallelism (number of query servers) for the table. This is the number of processes assigned to perform. the scan of the specified table in parallel on a single instance. The second optional value specifies the number of Oracle parallel server instances to split the query across. If you specify PARALLEL(EMP, 4 2), there will be four parallel query processes running on two separate parallel server instances. If no parameters are specified, the default (calculated) degree of parallelism and number of parallel servers is sourced from the parameters specified in the INIT.ORA file.
The hint can be used for selects, updates, deletes, and inserts. To get performance improvements using the parallel hint, your datafiles must be striped across multiple disks. Don't set the degree of parallelism higher than the number of disks that the table is striped over. Having multiple processors will make the operation run even faster, but only if the table is striped.
SELECT /*+ PARALLEL (x 4) */ COUNT(*)
FROM x;
SELECT /*+ PARALLEL (x 4 2) */ COUNT(*)
FROM x;
UPDATE /*+ PARALLEL (x 4) */ x
SET position = position+1;

DELETE /*+ parallel(x 4) */ from x;
INSERT INTO x
SELECT /*+ PARALLEL(winners 4) */ *
FROM winners;

PARALLEL_INDEX(table, index, degree of parallelism, cluster split)
Allows you to parallelize index range scans for partitioned indexes. Also allows the work to be done across multiple instances of a parallel server architecture. The following example tells the optimizer to utilize parallel index processing on the EMP table, which is partitioned, to use the EMP_NDX index, and to run at a parallel degree of four over two Oracle parallel server instances.
SELECT /*+ PARALLEL_INDEX(emp, emp_ndx, 4, 2) */
...

PQ_DISTRIBUTE(table [Outer Distribution] [Inner Distribution])
Used to improve parallel join performance. There are six possibilities for distribution hints, as listed in Table 1-6.
SELECT /*+ USE_HASH(o)
PQ_DISTRIBUTE(o HASH, HASH) */ COUNT(*)
FROM winners w, owners o
WHERE w.owner = o.owner;
Table 1-6. PQ_DISTRIBUTE combinations
Distribution combination Meaning
HASH, HASH Uses a hash function on the join keys for each query server process. Can be used for a hash join or sort merge join. Works best when tables are approximately the same size.
BROADCAST, NONE Broadcasts all rows of the outer table to each of the parallel query servers. Use this when the outer table is considerably smaller than the inner table.
NONE, BROADCAST Broadcasts all rows of the inner table to each of the parallel query servers. Use this option when the size of the inner table is much smaller than the outer table.
PARTITION, NONE Maps the rows of the outer table using the partitioning of the inner table. The inner table must be partitioned and equi-joined on the join keys. This option works most effectively if the number of partitions in the outer table is equal to the number of parallel query processes utilized.

NONE, PARTITION Maps the rows of the inner table using the partitioning of the outer table. The outer table must be partitioned on the join keys. Use this option when the number of partitions on the outer table is equal to the number of parallel query servers.
NONE, NONE Causes each query server to perform. a join operation between a pair of matching partitions, one from each table. Both tables must be equi-partitioned for this option to be used effectively.

PUSH_PRED(table)
Pushes the join predicate for a table into an inline view. Doing so can sometimes help the cost-based optimizer make better decisions.
SELECT /*+ PUSH_PRED(v) */ count(*)
FROM horses h,
(SELECT w.horse_name, o.owner,
w.position
FROM winners w, owners o
WHERE w.owner = o.owner) v
WHERE h.horse_name = v.horse_name
AND v.position = 1
The difference in the execution plan for the example is that the HORSE_NAME in the WHERE clause is joined to the inline view as part of the inline view selection.

PUSH_SUBQ
Forces nonmerged subqueries to be evaluated as early as possible in the execution plan. Nonmerged subqueries are normally executed as the last step of an execution plan. This hint has no effect on a subquery if the subquery is over a remote table (as in a distributed SQL statement), or if the subquery uses a merge join.
SELECT count(*)
FROM horses
WHERE EXISTS
( SELECT /*+ PUSH_SUBQ */ 'x'
FROM horse_owners
WHERE owner LIKE '%Lombardo%'
AND horses.horse_name=
horse_owners.horse_name)

REWRITE
Allows Oracle to utilize materialized views based on a selected table. In the example that follows, we have a table that contains horse race results. We have created a materialized

view that stores the OWNER, HORSE_NAME, POSITION, and the COUNT(*) for each of those combinations.
CREATE MATERIALIZE VIEW LOG ON RESULTS
WITH ROWID,
PRIMARY KEY (HORSE_NAME, OWNER,
RACE_DATE)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW winning_horse_owners_vw
USING INDEX
REFRESH ON COMMIT
ENABLE QUERY REWRITE
AS SELECT horse_name, owner, position, COUNT(*)
FROM results
GROUP BY horse_name, owner, position;
In order for this materialized view to be useful, you must have the INIT.ORA parameter QUERY_REWRITE_ENABLED=TRUE, and the schema MUST HAVE the privilege QUERY REWRITE assigned. For example:
GRANT QUERY REWRITE TO HROA;
The SQL query shown next is able to obtain all of the data it requires from the view, and therefore the optimizer will use the view in preference to the table, despite the SELECT being made against the table.
SELECT /*+ REWRITE */ horse_name, owner, position, COUNT(*)
FROM results
GROUP BY horse_name, owner, position;

ROWID(table)
Forces a table scan by ROWID for the specified table. The rowid is the physical disk address of the row.
SELECT /*+ ROWID(a) */ ename
FROM emp a
WHERE rowid > 'AAAGJ2AAIAAABn4AAA'
AND surname like 'GURR%'

RULE
Uses the rule-based optimizer for the current statement block. You can achieve the same effect by having the CHOOSE option specified for the INIT.ORA parameter

OPTIMIZER_MODE, and not analyzing the tables and indexes used in the SELECT statement.

STAR
Forces the largest table to be last in the join order. Typically the other tables should be lookup or reference tables. This hint is used extensively in data warehouse applications. STAR is only effective when you are joining at least three tables.
SELECT /*+ STAR */ h.horse_name, o.owner,
r.position, r.location, r.race_date
FROM results r, horses h, owners o
WHERE h.horse_name like 'WI%'
AND h.horse_name = r.horse_name
AND r.owner = o.owner;

STAR_TRANSFORMATION
Works on fact and dimension tables, and is similar to the STAR hint. The major difference is that it allows the cost-based optimizer to decide if it is worth transforming the statement into a new statement before determining the execution plan. By "transforming," I mean that the statement is broken into a number of subqueries that are able to take advantage of bitmap indexes.
To use this hint, it is essential that you have STAR_TRANSFORMATION_ENABLED=TRUE in your INIT.ORA file.
The most pronounced difference between this hint and the STAR hint is that the STAR_TRANSFORMATION will often combine bitmap indexes on the various fact table columns rather than using a Cartesian join. This is achieved by breaking the statement into subquery pieces.
SELECT /*+ STAR_TRANSFORMATION */
...

UNNEST
Merges the body of a subquery into the body of the main statement, which can often improve optimizer decision making. UNNEST can only be used when the session parameter UNNEST_SUBQUERY=TRUE.
SELECT /*+ UNNEST */ count(*)
FROM horses
WHERE horse_name LIKE 'M%'
AND horse_name NOT IN
( SELECT horse_name
FROM horse_owners

WHERE owner LIKE '%Lombardo%');

USE_CONCAT
Forces the optimizer to take OR conditions in the WHERE clause and convert them to a UNION ALL query operation. In an example such as the one that follows, the index is scanned twice, once for each condition on the two sides of the OR. The data is then joined into one result set via a concatenation operation.
SELECT /*+ USE_CONCAT */ COUNT(*)
FROM horse_owners
WHERE identifier < 10 OR identifier > 20

USE_HASH (table)
A hash join is an alternative to a nested loop. A hash table is created in memory of the smallest table, and then the other table(s) is scanned, with its rows being compared to the hash. A hash join will run faster than a merge join (sort merge) if memory is adequate to hold the entire table that is being hashed. The entire join operation must be performed before a single row is returned to the user. Therefore, hash joins are usually used for reporting and batch processing.
SELECT /*+ USE_HASH(w o) */ count(*)
FROM winners w, owners o
WHERE w.owner like 'Mr M A Gurry'
AND w.owner= o.owner
AND o.suburb = 'RICHMOND'
A hash join can only be used for equality-based joins (=), and not for range-based joins (<, <=, >, >=). A merge join is often appropriate when a hash join cannot be used.

Don't confuse the HASH hint with USE_HASH.

USE_MERGE(table)
A merge join is an alternative to nested loop and hash joins. All tables are sorted, unless all of the columns in the WHERE clause are contained within an index. This sort can be expensive and it explains why a hash join will often run faster then a merge join.
SELECT /*+ USE_MERGE(w o) */ count(*)
FROM winners w, owners o
WHERE w.owner like 'Mr M A Gurry'
AND w.owner < o.owner
AND o.suburb = 'RICHMOND'

The entire set of data must be returned before a single row is returned to the user. Therefore hash joins are usually used for reporting and batch processing.

Don't confuse the MERGE hint and USE_MERGE.

Merge joins work effectively for equality-based joins as well as for range-based joins. Merge joins also often run much faster than a hash join when all of the columns in the WHERE clause are pre-sorted in an index.

USE_NL(table)
Forces the optimizer to join the specified table to another table (or subquery) using a nested loop join. The specified table is joined as the inner table of the nested loops. Nested loop joins are faster than sort/merge or hash joins at retrieving the first row of a query statement.
Online screens should definitely use nested loops, because data will be returned immediately. As a rule of thumb, if less than 10% of the rows are returned from the tables, consider using nested loops. Use hash joins or sort merges if 10% or more of the rows are being returned.
SELECT /*+ USE_NL(w o) */ count(*)
FROM winners w, owners o
WHERE w.owner like 'Mr M A Gurry'
AND w.owner= o.owner
AND o.suburb = 'RICHMOND'