/*+ hint */
/*+ hint(argument) */
/*+ hint(argument-1 argument-2) */
 
SELECT /*+ FIRST_ROWS(10) */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;
 
SELECT /*+ index(table_alias f_name) */ ... FROM TABLE.test table_alias
 
-- Hint List:
 
/*+ ALL_ROWS */
 
Explicitly chooses the cost-based approach TO optimize 
a statement block WITH a goal OF best throughput (that 
IS, minimum total resource consumption)
 
/*+ CHOOSE */
 
Causes the optimizer TO choose BETWEEN the rule-based approach 
AND the cost-based approach FOR a SQL statement based ON the 
presence OF statistics FOR the tables accessed BY the statement
 
/*+ FIRST_ROWS */
 
Explicitly chooses the cost-based approach TO optimize a statement 
block WITH a goal OF best response TIME (minimum resource usage TO 
RETURN FIRST ROW). It will also force the optimizer TO make USE OF 
INDEX, IF available. There are other versions OF FIRST_ROWS hints. 
This hint IS useful IN an OLTP environment WHEN the USER cannot 
wait till the LAST ROW IS fetched. This IS mainly used IN JAVA 
lookup screens. IF there are some calculations THEN this hint should 
NOT be used.
 
Test your PL/SQL knowledge, Which code runs faster?
/*+ RULE */
 
Explicitly chooses rule-based optimization FOR a statement block
 
/*+ AND_EQUAL(table index) */
 
Explicitly chooses an execution plan that uses an access PATH that 
merges the scans ON several single-column indexes
 
/*+ CLUSTER(table) */
 
Explicitly chooses a CLUSTER scan TO access the specified TABLE
 
/*+ FULL(table) */
 
Explicitly chooses a full TABLE scan FOR the specified TABLE
 
/*+ HASH(table) */
 
Explicitly chooses a hash scan TO access the specified TABLE
 
/*+ HASH_AJ(table) */
 
Transforms a NOT IN sub query INTO a hash anti join TO access the 
specified TABLE
 
/*+ HASH_SJ (table) */
 
Transforms a NOT IN sub query INTO a hash anti-join TO access the 
specified TABLE
 
/*+ INDEX(table index) */
 
Explicitly chooses an INDEX scan FOR the specified TABLE
 
/*+ INDEX_ASC(table index) */
 
Explicitly chooses an ascending-RANGE INDEX scan FOR the specified 
TABLE
 
/*+ INDEX_COMBINE(table index) */
 
IF no indexes are given AS arguments FOR the INDEX_COMBINE hint, the 
optimizer uses whatever BOOLEAN combination OF bitmap indexes has the 
best cost estimate. IF particular indexes are given AS arguments, the 
optimizer tries TO USE some BOOLEAN combination OF those particular 
bitmap indexes.
 
/*+ INDEX_DESC(table index) */
 
Explicitly chooses a descending-RANGE INDEX scan FOR the specified TABLE
 
/*+ INDEX_FFS(table index) */
 
Causes a fast full INDEX scan TO be performed rather than a full 
TABLE scan
 
/*+ MERGE_AJ (table) */
 
Transforms a NOT IN sub query INTO a merge anti-join TO access the 
specified TABLE
 
/*+ MERGE_SJ (table) */
 
Transforms a correlated EXISTS sub query INTO a merge semi-join TO 
access the specified TABLE
 
/*+ ROWID(table) */
 
Explicitly chooses a TABLE scan BY ROWID FOR the specified TABLE
 
/*+ USE_CONCAT */
 
Forces combined OR conditions IN the WHERE clause OF a query TO be 
transformed INTO a compound query using the
 
UNION ALL SET OPERATOR
 
/*+ ORDERED */
 
Causes Oracle TO join tables IN the ORDER IN which they appear IN 
the FROM clause
 
/*+ STAR */
 
Forces the large TABLE TO be joined using a nested-LOOP join ON the INDEX
 
/*+ DRIVING_SITE (table) */
 
Forces query execution TO be done AT a different site FROM that selected 
BY Oracle
 
/*+ USE_HASH (table) */
 
Causes Oracle TO join each specified TABLE WITH another ROW source 
WITH a hash join
 
/*+ USE_MERGE (table) */
 
Causes Oracle TO join each specified TABLE WITH another ROW source 
WITH a sort-merge join
 
/*+ USE_NL (table) */
 
Causes Oracle TO join each specified TABLE TO another ROW source 
WITH a nested-loops join using the specified TABLE AS the inner TABLE
 
/*+ APPEND */ , /*+ NOAPPEND */
 
Specifies that data IS simply appended (OR NOT) TO a TABLE; existing 
free SPACE IS NOT used. USE these hints only following the INSERT keyword.
 
/*+ NOPARALLEL(table) */
 
Disables parallel scanning OF a TABLE, even IF the TABLE was created 
WITH a PARALLEL clause
 
/*+ PARALLEL(table, instances) */
 
This allows you TO specify the desired NUMBER OF concurrent slave processes 
that can be used FOR the operation. DELETE, INSERT, AND UPDATE operations 
are considered FOR parallelization only IF the session IS IN a PARALLEL DML 
enabled MODE. (USE ALTER SESSION PARALLEL DML TO enter this MODE.)
 
/*+ PARALLEL_INDEX */
 
Allows you TO parallelize fast full INDEX scan FOR partitioned AND 
non-partitioned indexes that have the PARALLEL attribute
 
/*+ NOPARALLEL_INDEX */
 
Overrides a PARALLEL attribute setting ON an INDEX
 
/*+ CACHE */
 
Specifies that the blocks retrieved FOR the TABLE IN the hint are placed 
AT the most recently used END OF the LRU list IN the buffer cache WHEN a 
full TABLE scan IS performed
 
/*+ NOCACHE */
 
Specifies that the blocks retrieved FOR this TABLE are placed AT the 
LEAST recently used END OF the LRU list IN the buffer cache WHEN a full 
TABLE scan IS performed
 
/*+ MERGE (table) */
 
Causes Oracle TO evaluate complex views OR sub queries before the 
surrounding query
 
/*+ NO_MERGE (table) */
 
Causes Oracle NOT TO merge mergeable views
 
/*+ PUSH_JOIN_PRED (table) */
 
Causes the optimizer TO evaluate, ON a cost basis, whether OR NOT TO push 
individual join predicates INTO the VIEW
 
/*+ NO_PUSH_JOIN_PRED (table) */
 
Prevents pushing OF a join predicate INTO the VIEW
 
/*+ PUSH_SUBQ */
 
Causes non merged sub queries TO be evaluated AT the earliest possible 
place IN the execution plan
 
/*+ STAR_TRANSFORMATION */
 
Makes the optimizer USE the best plan IN which the transformation has been used.