oracle hint (續)

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 has