Hints can be used to force the creation of a query execution plan in terms of over-riding the default query plan. This option should only be used as a last resort. Queries retrieving less than 10% of rows will generally use an index if one exists. With small static tables it may be better to avoid index use by suppressing index use and forcing a full table-scan using a hint. Not collecting statistics and not adding hints will force use of rule-based optimisation. Keep statistics up-to-date. Hints apply to INSERT, UPDATE, DELETE and SELECT statements, ie. all DML statements. Also all hints apply to the cost-based approach except for the RULE hint which applies to the rule-based approach.
Note that incorrectly or inappropriately specified or coded hints do not produce errors but are simply ignored. Be sure that hints are syntactically correct. Also when using aliases for table objects in SQL statements then the alias must be used otherwise the hint will be ignored.
Hints are local to the SQL statements into which they are placed. Thus a hint does not apply to a subquery unless present in the subquery aswell. Therefore a subquery has a separate hint even if that hint is the same. A hint is effectively a comment and is denoted as being between the /*+ and */ character sequences. Effective hints for use with SQL are FULL, INDEX, ORDERED, ALL_ROWS, FIRST_ROWS and USE_NL.
ALL_ROWS - This hint forces the CBO optimiser to select the quickest route for retrieval of all rows from a query regardless of single-row retrieval. The ALL_ROWS hint is applicable to batch processing and not OLTP type transactions. The ALL_ROWS hint will typically suppress the utilisation of indexes by the optimiser completely.
SELECT /*+ ALL_ROWS */ <column, column, ...> ...
FIRST_ROWS - This hint will force the retrieval of the first row in the table as quickly as possible. The FIRST_ROWS hint will effectively force the use of indexes. Note that the FIRST_ROWS hint is ignored from some SQL statements and some forms of SQL statements. Those hint ignoring SQL statements are as shown below.
SELECT /*+ FIRST_ROWS */ <column, column, ...> ...
CHOOSE - Optimiser will choose rule or cost-based optimisation based on the presence of statistics for atleast one of the tables.
RULE - Selects rule-based optimisation regardless of all other considerations.
FULL - Force a full table-scan. Use when querying large portions or all of a table or for small static tables. Note that retrieval of rows from tables by use of indexes will actually read two objects, both the index and the table objects.
SELECT /*+ FULL(<tablename>) */ * FROM <tablename>;
ROWID - Scan a table by ROWID.
CLUSTER - Cluster scan on a cluster.
HASH - Cluster hash scan.
HASH_AJ - Change a NOT IN subquery to a hash anti-join.
HASH_SJ - Change an EXISTS subquery to a hash semi-join.
INDEX[_ASC | _DESC] - Force the use of one or more indexes. The CBO optimiser will by default utilise the index which returns the smallest number of columns from a table. Specifying multiple indexes with an index hint will force the optimiser to choose from one of the list.
SELECT /*+ INDEX(<tablename indexname [indexname]>) */ * FROM <tablename> WHERE ...;
The ASC and DESC options imply index scanning in ascending or descending order.
INDEX_COMBINE - Forces bitmap access and combination of individual column bitmap indexes if available.
INDEX_JOIN - Forces the use of an index join.
INDEX_FFS - Forces an index fast full scan instead of a table scan.
NO_INDEX - Prevents the use of a set of indexes.
MERGE_AJ - Changes a NOT IN subquery into a merge anti-join.
MERGE_SJ - Changes EXISTS subquery into a merge semi-join.
AND_EQUAL - Merges the scans on a number of single column indexes.
USE_CONCAT - Changes WHERE OR conditions into UNION ALL query compounds.
NO_EXPAND - Removes OR expansion.
[NO]REWRITE - Materialised views.
SELECT /*+ ORDERED */ * FROM <tablename, tablename>
STAR - A start query places the largest query last and joins it with a nested loops join on a concatenated index.
USE_NL - The USE_NL hint forces the optimiser to return a single row. Thus USE_NL will be slower for queries returning all the rows in a query.
SELECT /*+ USE_NL (<tablename tablename>) */ <column, column, ...> ...
USE_MERGE - Join each table with a sort-merge join.
USE_HASH - Join each table with a hash-merge join.
DRIVING_SITE - Perform query execution remotely.
[NO]PARALLEL - The PARALLEL hint will perform full table scans in parallel by splitting a query into multiple threads running simultaneously, the results are merged to be returned as a single row set. Obviously a machine with multiple processors will allow the parallel running server threads to execute on each processor. Parallel processing can help in heavy adhoc environments where full table scans are commonly used. The example below breaks the query up into four processes.
SELECT /*+FULL(table) PARALLEL(table, 4) */ field1, field2 FROM table
PQ_DISTRIBUTE - Improve parallel join.
[NO]APPEND - Do not use table free space, simply append to the end into newly extended, empty blocks.
[NO]PARALLEL_INDEX - Use parallel indexes for partitioned tables.
[NO]CACHE - Place retrieved blocks into the MRU list; useful for static data.
[NO_]MERGE - View merge.
[NO_]PUSH_JOIN_PRED - Push a join predicate into a view.
PUSH_SUBQ - Nonmerged subqueries are evaluated at the earliest possible position in the execution plan.
STAR TRANSFORMATION - Use the best plan produced in the past.
ORDERED_PREDICATES - Preserve evaluation order or precedence, for use in SELECT statement WHERE clause.