HINTs #2 - [Oracle]

Specifies if any of the tables or indexes in the statement are analyzed, use the Cost Based optimizer otherwise use the Rule Based. If they are analyzed, it uses the cost based optimizer to provide the best response time for batch processing. Processing the SQL statements using ALL_ROWS is the best practice for databases that have large amounts of batch processing, such as Data Warehouses. It is usually better to set the OPTIMIZER_MODE=ALL_ROWS in the INIT.ora rather than apply the hint to individual statements. Setting ALL_ROWS will often use full table scans instead of indexes, and sort merges or hash joins in preference to nested loops. The hint returns all of the rows requested by the statement in the least possible time.

 

SELECT /*+ ALL_ROWS */ a.acct_name, t.trans_date, t.trans_amt

   FROM trans t, acct a

WHERE a.acct_no = 910717

      AND a.acct_no = t.acct_no;

 

Uses the cost based optimizer to provide the best response time for OLTP processing. Processing the SQL statements using FIRST_ROWS is the best practice for databases that have large numbers of OLTP users. The hint will force the statement to return rows to the user as soon as possible. Returning some rows immediately allows a screen buffer to fill quickly and present the rows to the user.

 

It is usually better to set the OPTIMIZER_MODE = FIRST_ROWS in the INIT.ora rather than apply the hint to individual statements. Setting FIRST_ROWS will often use indexes even though a full table scan will return the entire set of records using significantly less disk reads. It will also use nested loops in preference to sort merges and hash joins.

 

SELECT /*+ FIRST_ROWS */ a.acct_name, t.trans_date, t.trans_amt

   FROM trans t, acct a

 WHERE a.acct_no = 27217

       AND a.acct_no = t.acct_no;

The optimizer ignores the FIRST_ROWS hint in DELETE and UPDATE statements as well as SELECTs, the use of UNION, INTERSECT, MINUS, UNION ALL, GROUP BY, FOR UPDATE or DISTINCT.

 

 

Specifies if any of the tables or indexes in the statement are analyzed, use the Cost Based optimizer otherwise use the Rule Based. Each statement is taken on its merits with no bias towards batch processing or OLTP processing. Processing the SQL statements using CHOOSE is the best practice for databases that have a mixture of OLTP and batch processing, which is the majority of sites. It is usually better to set the OPTIMIZER_MODE=CHOOSE in the INIT.ora rather than apply the hint to individual statements.

 

SELECT /*+ CHOOSE */ a.acct_name, t.trans_date, t.trans_amt

   FROM trans t, acct a

 WHERE a.acct_no = 0210717

      AND a.acct_no = t.acct_no;

 

The statement will run using the rule based optimizer. This hint is often used when you wish to transfer to the Cost Based optimizer, but have one problem statement that ran well when you were using the rule based optimizer. It is usually better to set the OPTIMIZER_MODE=RULE in the INIT.ora rather than applying the hint to individual statements. This allows you to ANALYZE your objects and receive useful information without changing from the reliable rule based behavior.

 

SELECT /*+ RULE */ a.acct_name, t.trans_date, t.trans_amt

   FROM trans t, acct a

 WHERE a.acct_no = 1717

       AND a.acct_no = t.acct_no;

Warning: Oracle continually informs us that the RULE hint and rule-based optimizer will not be available in future versions of Oracle.

 

 

This hint tells the optimizer to do a full scan of the specified table, even if indexes are in place. If you have set a degree of parallelism on the table, Oracle will use parallel query processing. Often full table scans are significantly faster that index searches when more than 15% of the rows in the table are being selected.

 

SELECT /*+ FULL(a) */ a.acct_no, t.trans_date, t.trans_amt

   FROM trans t, accts a

WHERE a.acct_no = 1717

      AND a.acct_no = t.acct_no;

Notice that the table alias name is used and not the table name itself.

 

 

The ROWID hint explicitly chooses a table scan by ROWID for the specified table. We are still to find a use for this hint.

 

SELECT /*+ ROWID(a) */ rowid, acct_no

   FROM accts a;

Notice that the table alias name is used and not the table name itself.

 

 

Clusters store each child related to a parent in the same address. In the following example, the ACCT table is the parent table which will have its TRANS rows attached to it. The search takes place using the parent key.

 

SELECT /*+ CLUSTER(a) */ a.acct_no, t.trans_date, t.trans_amt

   FROM trans t, accts a

WHERE a.acct_no = 1717

      AND a.acct_no = t.acct_no;

Notice that the table alias name is used and not the table name itself.

 

 

This hint cause a hash access to the table specified. Notice that the alias is used instead of the table name when an alias is used. The hash key in the example is on the acct_no column.

 

SELECT /*+ HASH(a) */ acct_no, acct_name

   FROM accts a

WHERE acct_no = 12929;

 

Use the HASH_AJ hint to utilize a hash anti-join for a NOT IN subquery. You will find that this will speed up your response times considerably, particularly if the NOT IN needs to process a significant amount of data. Don't forget to set the HASH_JOIN_ENABLED and HASH_AREA_SIZE parameters to get the best hash performance.

 

SELECT * FROM accts

WHERE  acct_name like 'GURR%''

    AND   acct_no NOT IN

            (SELECT /*+ HASH_AJ */ acct_no

                FROM held_accts

             WHERE held_flag='Y'

                   AND end_date > sysdate);

To obtain ongoing good performance for NOT IN's, you should set the INIT.ora parameter ALWAYS_ANTI_JOIN to MERGE or HASH.

 

 

Use the MERGE_AJ hint to utilize a merge anti-join for a NOT IN subquery You will find that this will speed up your response times considerably, particularly if the NOT IN needs to process a significant amount of data.

 

SELECT * FROM accts

WHERE  acct_name like 'GURR%''

    AND   acct_no NOT IN

            (SELECT /*+ MERGE_AJ */ acct_no

                FROM held_accts

             WHERE held_flag='Y'

                   AND end_date > sysdate);

To obtain ongoing good performance for NOT IN's, you should set the INIT.ora parameter ALWAYS_ANTI_JOIN to MERGE or HASH.

 

 

Forces an index scan of the table using the specified index(s). If a list of indexes is specified, the optimizer chooses the index which will perform the fewest accesses to retrieve the data. If an index is not specified, the optimizer chooses the index which will perform the fewest accesses to retrieve the data. Use the index if less than 15% of the rows are returned. By default, the data will be retrieved in the ascending order of the index. You may be able to avoid a sort by removing the ORDER BY clause.

 

However, Oracle warns that the order of the data retrieved may not be ascending on the index columns. It is best to use the INDEX_ASC hint.

 

SELECT /*+ INDEX(a acct_u1) */ a.acct_no, t.trans_date, t.trans_amt

   FROM trans t, accts a

WHERE a.acct_no = 1717

      AND a.acct_no = t.acct_no;

 

Forces an index scan of the table using the specified index(s). If a list of indexes is specified, the optimizer chooses the index which will perform the fewest accesses to retrieve the data. If an index is not specified, the optimizer chooses the index which will perform the fewest accesses to retrieve the data. Use the index if less than 15% of the rows are returned. By default, the data will be retrieved in the ascending order of the index. You may be able to avoid a sort by removing the ORDER BY clause.

 

SELECT /*+ INDEX_ASC(a acct_u1) */ a.acct_no, t.trans_date, t.trans_amt

   FROM trans t, accts a

WHERE a.acct_no = 1717

      AND a.acct_no = t.acct_no;

 

This hint is used to combine bitmap indexes. If multiple indexes are specified, Oracle will use a boolean join of the indexes. If no indexes are specified, the optimizer will use the combination of bitmap indexes that returns the rows with the least amount of processing.

 

SELECT /*+ INDEX_COMBINE (emp emp_ndx2 emp_ndx3) */ name, salary

   FROM emp

 WHERE sex='F'

  AND     cost_center=10;

 

This hint returns the data in the descending order of the indexed columns. You can replace the ORDER BY a.acct_no DESC in the statement shown below and retrieve the rows in the same descending sequence.

 

SELECT /*+ INDEX_DESC(a acct_u1) */ a.acct_no, t.trans_date, t.trans_amt

   FROM trans t, accts a

WHERE a.acct_no = 1717

      AND a.acct_no = t.acct_no;

QUERY_PLAN

-----------------------------------------------------

  HASH JOIN

  INDEX RANGE SCAN DESCENDING ACCT_U1

  TABLE ACCESS BY INDEX ROWID TRANS

      INDEX RANGE SCAN TRANS_NDX1

 

If all of the columns being selected are in an index, the FAST FULL SCAN of an index can provide you with pleasing performance improvements. It is faster than a regular index scan because it takes advantage of multiblock I/O. It can also be parallelized, just like a Full Table scan. The limitiations are that no columns can be specified in the where clause. There is also no guarantee that the data will be returned in ascending order. The feature can't be used with bitmap indexes.

 

SELECT /*+ INDEX_FFS(a acct_u1) */ a.acct_no, t.trans_date, t.trans_amt

   FROM trans t, accts a

WHERE a.acct_no = t.acct_no;

QUERY_PLAN

---------------------------------------------------

  HASH JOIN

    INDEX FAST FULL SCAN ACCT_U1

    TABLE ACCESS FULL TRANS_NDX!

 

This hint causes a merge on the specified single column indexes. Our advice in single column indexes is to consider having a 2 column concatenated index in preference to 2 single column indexes. Oracle7.3 and later has shown performance improvements using AND_EQUAL; however, it is still not as responsive as having concatenated indexes. You cannot specify more than five indexes to be merged.

 

SELECT /*+ AND_EQUAL(a acct_ndx3 acct_ndx2) */ a.cost_center, a.area_code

 FROM acct a

WHERE a.cost_center=10

  AND  a.area_code=20;

QUERY_PLAN

----------------------------------------

  AND-EQUAL

    INDEX RANGE SCAN ACCT_NDX2

    INDEX RANGE SCAN ACCT_NDX3

 

Use the USE_CONCAT hint to change OR conditions into a UNION ALL. We have found that this hint does not always seem to work as expected. The OR EXISTS in the script below is one case where the USE_CONCAT would speed up the processing considerably.

 

SELECT /*+ USE_CONCAT */ a.acct_name

 FROM accts a

WHERE a.acct_no between (1000 and 2000)

      OR  EXISTS

    (SELECT 'x'

         FROM trans t

       WHERE a.acct_no = t.acct_no

            AND t.trans_type='E');

 

Use ORDERED to force Oracle to use the tables to be joined in the order first to last in the FROM clause. The driving table can be a critical factor in improving performance. The table that returns the fewest rows, as distinct to the table that contains the lesser number of rows, should be the driving table. The RULE based optimizer tends to benefit most from this hint. The COST based optimizer will almost always select the correct driving table if the tables are analyzed.

 

SELECT /*+ ORDERED */ *

 FROM accts a, trans b

WHERE a.acct_type = 'A'

  AND b.trans_type= 'E'

  AND a.acct_no   = b.acct_no 

/

NESTED LOOPS

    TABLE ACCESS BY ROWID ACCT

      INDEX RANGE SCAN ACCT_N1

    TABLE ACCESS BY ROWID TRANS

      INDEX RANGE SCAN TRANS_N1

SELECT /*+ ORDERED */ *

 FROM trans b, accts a

WHERE a.acct_type = 'A'

  AND b.trans_type= 'E'

  AND a.acct_no   = b.acct_no 

/

 NESTED LOOPS

    TABLE ACCESS BY ROWID TRANS

      INDEX RANGE SCAN TRANS_N1

    TABLE ACCESS BY ROWID ACCT

      INDEX RANGE SCAN ACCT_N1

 

Use the STAR hint to speed up queries that have a central table accessing multiple reference tables. The small tables are merged first, usually using a cartesian join. The central larger table is then joined using nested loops on the table's index. The hint can provide the largest performance improvements when used against a very large database, such as a Data Warehouse.

 

SELECT /*+ STAR */  c.acct_no, c.acct_name, r1.state, r2.cost_center, r3.delivery_code, r4.trans_type

FROM  acct c, states r1, cost_center r2, delivery_type r3, trans_type r4

WHERE c.acct_name like 'BHP'

  AND    c.state_code=r1.code

  AND    c.cost_code=r2.code

  AND    c.delivery_code = r3.code

  AND    c.trans_code  = r4.code;

 

Use the STAR_TRANSFORMATION hint to speed up queries that have a central table accessing multiple reference tables. The small tables are merged first, but unlike the STAR hint, often not using a cartesian join. The central larger table is then joined using nested loops on the table's index. The hint can provide the largest performance improvements when used against a very large database, such as a Data Warehouse.

 

SELECT /*+ STAR_TRANSFORMATION */  c.acct_no, c.acct_name, r1.state, r2.cost_center, r3.delivery_code,

r4.trans_type

FROM  acct c, states r1, cost_center r2, delivery_type r3, trans_type r4

WHERE c.acct_name like 'BHP'

  AND    c.state_code=r1.code

  AND    c.cost_code=r2.code

  AND    c.delivery_code = r3.code

  AND    c.trans_code  = r4.code;

It is essential that you have the STAR_TRANSFORMATION_ENABLED INIT.ora parameter set to TRUE for this hint to take effect.

 

 

This hint is used to force tables being joined to get one row from the driving table and then get the associated rows from the joined table using an index. Rows are returned to the user immediately, which makes the access method ideal for users running online screens. As each row being joined to is returned, an additional physical read is often required to obtain additional columns specified in the select. If more than 15% of the rows are returned, a merge join will usually return the entire set of data faster.

 

SELECT /*+ USE_NL(a b) */ a.acct_no, b trans_date, b.trans_amt

   FROM trans b, accts a

 WHERE a.acct_no = 19181

    AND   a.acct_no = b.acct_no;

QUERY_PLAN

-----------------------------------------------

  NESTED LOOPS

    INDEX RANGE SCAN ACCTS_U1

    TABLE ACCESS BY INDEX ROWID TRANS

      INDEX RANGE SCAN TRANS_NDX1

 

This hint is used to force tables being joined to be sorted and merged using a merge join. Rows are returned to the user only after all rows have been sorted and the merge has begun. This method is more suited to batch applications, such as Data Warehouses. If less than 15% of the rows are returned, a merge join will usually return the entire set of data faster.

 

SELECT /*+ USE_MERGE(a b) */ a.acct_no, b trans_date, b.trans_amt

   FROM trans b, accts a

 WHERE a.acct_no = 19181

    AND   a.acct_no = b.acct_no;

QUERY_PLAN

--------------------------------------------------

  MERGE JOIN

    SORT JOIN

      TABLE ACCESS BY INDEX ROWID TRANS

        INDEX RANGE SCAN TRANS_NDX1

    SORT JOIN

      INDEX RANGE SCAN ACCTS_U1

 

NO_MERGE causes Oracle not to merge views specified in the FROM clause. The hint makes the optimization of the query faster. This hint was added with Oracle8.

 

SELECT /*+ NO_MERGE(a) */ a.acct_no, b trans_date, b.trans_amt

   FROM trans b, accts a

 WHERE a.acct_no = 19181

    AND   a.acct_no = b.acct_no;

QUERY_PLAN

--------------------------------------------------

  HASH JOIN

    INDEX RANGE ACCTS_U1

    TABLE ACCESS BY INDEX ROWID TRANS

      INDEX RANGE SCAN TRANS_NDX1

 

Tables are joined using a hash join. Don't forget to set HASH_JOIN_ENABLED to enable hash joins and HASH_AREA_SIZE to improve the performance of the hash joins. This hint was added with Oracle8.

 

SELECT /*+ USE_HASH(a b) */ a.acct_no, b trans_date, b.trans_amt

  FROM trans b, accts a

 WHERE a.acct_no = 19181

   AND a.acct_no = b.acct_no;

QUERY_PLAN

-----------------------------------------------

  HASH JOIN

    INDEX RANGE SCAN ACCTS_U1

    TABLE ACCESS BY INDEX ROWID TRANS

      INDEX RANGE SCAN TRANS_NDX1

 

This hint employs the parallel query processes to access a table. Prior to Oracle 8, the parallel query processes only applied to Full Table Scans. Oracle8 allows parallel processing to proceed for the Full table Scan as well as Inserts, Updates, and Deletes. The statement below will use 4 parallel query processes to access the accts table. It is important that your table is in a datafile that is striped over 4 disks for best performance.

 

SELECT /*+  PARALLEL(accts, 4) */  acct_no, acct_name

  FROM accts;

INSERT /*+ PARALLEL(4) */ INTO accts ……

You can specify a parameter to split the parallel query processors against separate parallel server instances. Use /*+ PARALLEL(accts, 4, 2) */ with the 2 being the split to the parallel servers.

 

 

If you have a table that has a degree of parallelism set to 4 and you wish to turn the parallel processing off, use the NOPARALLEL hint. You may consider using this hint when your system is heavily loaded with many tables performing parallel query processing. There is only a finite number of parallel processes and you may wish to dedicate them to more important tables.

 

  SELECT /*+ NOPARALLEL(scott_emp) */ ename

     FROM scott.emp scott_emp;

 

Use the APPEND hint on the INSERT statement to place data at the end of a table. Blocks that have their usage falling below the PCTUSED will not be considered for inserting into. If you INSERT with the PARALLEL option, the append functionality is used by default.

 

INSERT /*+ APPEND */ INTO accts ……

 

Use the NOAPPEND hint on the INSERT statement to ensure that the blocks that have free space exceeding the PCTUSED value, insert the rows into them. If you INSERT using parallel, the NOAPPEND will be overridden.

 

INSERT /*+ NOAPPEND */ INTO accts ……

 

Take advantage of the PARALLEL_INDEX hint to have multiple parallel processes perform a fast full index scan. The hint operates for both partitioned and non partitioned indexes. All of the columns selected must be in the index for this hint to work correctly.

 

SELECT  /*+ PARALLEL_INDEX */ acct_no, acct_name

   FROM accts;

QUERY_PLAN

-------------------------------------

  INDEX FAST FULL SCAN ACCTS_NDX1

 

Use the CACHE hint to bias a table to be held in the buffer cache longer. Usually full table scans will place the first 5 blocks on the most recently used data and the rest of the table on the least recently used end. The data on the least recently used end are overridden immediately as new data is read into the buffer cache. You must perform a full table scan to load the data into the cache. There is also no guarantee that the data will remain in the buffer cache.

 

SELECT /*+  CACHE(accts) */ *

FROM accts;

 

Use the NOCACHE hint to have the data from the table placed on the least recently used end of the LRU chain. The data is overridden immediately as new data is read into the buffer cache. This is the default behavior for a Full Table Scan. Use this hint to override a table that has been created with the CACHE option.

 

SELECT /*+  NOCACHE(accts) */ *

FROM accts;

 

The PUSH_SUBQ hint causes subqueries to be evaluated as early as possible in the execution plan. It can provide some very pleasing performance improvements if the subquery returns only a few distinct rows. The hint will not work on remote tables (distributed SQL statement) or the subquery uses a merge join. The statement must also use a Nested Loop.

 

SELECT acct_no, trans_date, trans_amt

  FROM  trans

WHERE trans_date >=

       IN

   (SELECT /*+ PUSH_SUBQ */ fin_st_dt

       FROM  fin_yr

     WHERE fin_yr = 1998);

 

The HASH_SJ hint transforms a correlated EXISTS subquery into a hash semi-join to access the specified table. A subquery will be evaluated as a semi-join only with these limitations:

 

· There can only be one table in the subquery.

· The outer query block must not itself be a subquery.

· The subquery must be correlated with an equality predicate.

· The subquery must have no GROUP BY, CONNECT BY, or ROWNUM references.

SELECT * FROM accts

 WHERE  EXISTS

    (SELECT /*+ HASH_SJ */ acct_no

       FROM held_accts

      WHERE held_flag='Y'

        AND end_date > sysdate);

 

The INDEX_JOIN hint explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query.

 

SELECT /*+ INDEX_JOIN(a acct_u1) */

   a.acct_no, t.trans_date, t.trans_amt

   FROM trans t, accts a

  WHERE a.acct_no = 1717

    AND a.acct_no = t.acct_no;

 

The NO_INDEX hint explicitly disallows a set of indexes for the specified table. Use this hint to optionally specify one or more indexes:

 

· If this hint specifies a single available index, the optimizer does not consider a scan on this index. Other indexes not specified are still considered. 

 

· If this hint specifies a list of available indexes, the optimizer does not consider a scan on any of the specified indexes. Other indexes not specified in the list are still considered.

 

· If this hint specifies no indexes, the optimizer does not consider a scan on any index on the table. This behavior is the same as a NO_INDEX hint that specifies a list of all available indexes for the table.

 

The NO_INDEX hint applies to function-based, B*-tree, bitmap, cluster, or domain indexes. NOTE: If a NO_INDEX hint and an index hint (INDEX, INDEX_ASC, INDEX_DESC, INDEX_COMBINE, or INDEX_FFS) both specify the same indexes, then both the NO_INDEX hint and the index hint are ignored for the specified indexes and the optimizer will consider the specified indexes.

 

SELECT /*+ NO_INDEX(a acct_u1) */

   a.acct_no, t.trans_date, t.trans_amt

   FROM trans t, accts a

  WHERE a.acct_no = 1717

    AND a.acct_no = t.acct_no;

 

Use the MERGE_SJ hint to transform a correlated EXISTS subquery into a merge semi-join to access the specified table.

 

SELECT * FROM accts

WHERE  acct_name like 'GURR%''

    AND   acct_no EXISTS

            (SELECT /*+ MERGE_SJ */ acct_no

                FROM held_accts

             WHERE held_flag='Y'

                   AND end_date > sysdate);

To obtain ongoing good performance for EXISTS's, you should set the INIT.ora parameter ALWAYS_SEMI_JOIN to MERGE or HASH.

 

 

The NO_EXPAND hint prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or INLISTS in the WHERE clause. Normally, the optimizer would consider using OR expansion and use this method if it decides the cost is lower than not using it.

 

SELECT /*+ NO_EXPAND */ name, salary

   FROM emp

  WHERE cost_center=9

     OR cost_center=10;

 

Use the REWRITE hint with or without a view list. If you use REWRITE with a view list and the list contains an eligible materialized view, Oracle uses that view regardless of its cost. Oracle does not consider views outside of the list. If you do not specify a view list, Oracle searches for an eligible materialized view and always uses it regardless of its cost.

 

 

Use the NOREWRITE hint on any query block of a request. This hint disables query rewrite for the query block, overriding the setting of the parameter QUERY_REWRITE_ENABLED.

 

 

The DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization. Table is the name or alias for the table at which site the execution should take place.

 

SELECT /*+DRIVING_SITE(DEPT)*/ *

  FROM EMP, DEPT@RSITE

 WHERE EMP.DEPTNO = DEPT.DEPTNO;

If this query is executed without the hint, rows from DEPT will be sent to the local site and the join will be executed there. With the hint, the rows from EMP will be sent to the remote site and the query will be executed there, returning the result to the local site.

 

 

Use the PQ_DISTRIBUTE hint to improve parallel join operation performance. Do this by specifying how rows of joined tables should be distributed among producer and consumer query servers. Using this hint overrides decisions the optimizer would normally make.

 

Use the EXPLAIN PLAN statement to identify the distribution chosen by the optimizer. The optimizer ignores the distribution hint if both tables are serial.

 

There are six combinations for table distribution:

Distribution	Interpretation
Hash, Hash	Maps the rows of each table to consumer query servers using a hash function on the join keys. When mapping is complete, each query server performs the join between a pair of resulting partitions. This hint is recommended when the tables are comparable in size and the join operation is implemented by hash-join or sort-merge join.
Broadcast, None	All rows of the outer table are broadcast to each query server. The inner table rows are randomly partitioned. This hint is recommended when the outer table is very small compared to the inner table. A rule-of-thumb is: Use the Broadcast/None hint if the size of the inner table * number of query servers < size of the outer table
None, Broadcast	All rows of the inner table are broadcast to each consumer query server. The outer table rows are randomly partitioned. This hint is recommended when the inner table is very small compared to the outer table. A rule-of-thumb is: Use the None/Broadcast hint if the size of the inner table * number of query servers < size of 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 on the join keys. This hint is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers, for example, 14 partitions and 15 query servers. Note: The optimizer ignores this hint if the inner table is not partitioned or not equijoined on the partitioning key.
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. This hint is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers, for example, 14 partitions and 15 query servers. Note: The optimizer ignores this hint if the outer table is not partitioned or not equijoined on the partitioning key.
None, None	Each query server performs the join operation between a pair of matching partitions, one from each table. Both tables must be equi-partitioned on the join keys.

 

Given two tables R and S that are joined using a hash-join, the following query contains a hint to use hash distribution:

 

SELECT <column_list>

/*+ORDERED PQ_DISTRIBUTE(S HASH HASH) USE_HASH (S)*/

  FROM R,S

 WHERE R.C=S.C;

To broadcast the outer table R, the query should be:

SELECT <column list>

/*+ORDERED PQ_DISTRIBUTE(S BROADCAST NONE) USE_HASH (S) */

  FROM R,S

 WHERE R.C=S.C;

 

Use the NOPARALLEL_INDEX hint to override a PARALLEL attribute setting on an index. In this way you can avoid a parallel index scan operation.

 

SELECT  /*+ NOPARALLEL_INDEX */ acct_no, acct_name

   FROM accts;

 

Merge a view on a per-query basis by using the MERGE hint.

 

SELECT /*+ MERGE(V) */ T1.X, V.AVG_Y

  FROM T1

    (SELECT X, AVG(Y) AS AVG_Y

       FROM T2

      GROUP BY X) V

 WHERE T1.X = V.X AND T1.Y = 1;

 

Use the PUSH_JOIN_PRED hint to force pushing of a join predicate into the view.

 

SELECT /*+ PUSH_JOIN_PRED(V) */ T1.X, V.Y

  FROM T1

    (SELECT T2.X, T3.Y

       FROM T2, T3

      WHERE T2.X = T3.X) V

 WHERE t1.x = v.x and t1.y = 1;

 

Use the NO_PUSH_JOIN_PRED hint to prevent pushing of a join predicate into the view.

 

SELECT /*+ NO_PUSH_JOIN_PRED(V) */ T1.X, V.Y

  FROM T1

    (SELECT T2.X, T3.Y

       FROM T2, T3

      WHERE T2.X = T3.X) V

 WHERE t1.x = v.x and t1.y = 1;

 

The ORDERED_PREDICATES hint forces the optimizer to preserve the order of predicate evaluation, except for predicates used as index keys. Use this hint in the WHERE clause of SELECT statements.

 

If you do not use the ORDERED_PREDICATES hint, Oracle evaluates all predicates in the order specified by the following rules:

· Predicates without user-defined functions, type methods, or subqueries are evaluated first, in the order specified in the WHERE clause.

· Predicates with user-defined functions and type methods that have user-computed costs are evaluated next, in increasing order of their cost.

· Predicates with user-defined functions and type methods without user-computed costs are evaluated next, in the order specified in the WHERE clause.

· Predicates not specified in the WHERE clause (for example, predicates transitively generated by the optimizer) are evaluated next.

· Predicates with subqueries are evaluated last in the order specified in the WHERE clause.

[Source] Knowledge Xpert for PL/SQL