Recent blog posts
- ORA-29701: Unable to connect to Cluster Manager
- Extract days hours minutes seconds from two dates in oracle
- ORA-00845: MEMORY_TARGET not supported on this system
- Killing Oracle Session
- Copy MySQL database to remote server
- ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 960
- Oracle row length of table
- Oracle 11g password case sensitivity
- Comments Within SQL Statements - Oracle SQL hints
- PHP random string
Comments Within SQL Statements - Oracle SQL hints
Sun, 05/24/2009 - 18:36 — birijan
Hints are the comments in a SQL statements to pass instruction to the Oracle Database optimizer. Those hints are used by oracle database optimizer to choose an execution plan for the statement, unless some conditions exists that prevents the optimizer from doing so.
A statement block can have only one comment containing hints, and that comment must follow the SELECT, UPDATE, INSERT, MERGE, or DELETE keyword. Only two hints are used with INSERT statements: The APPEND hint always follows the INSERT keyword, and the PARALLEL hint can follow the INSERT keyword.
Usage:
/*+ hint */
/*+ hint(argument) */
/*+ hint(argument-1 argument-2) */
eg.
SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
FROM employees
WHERE department_id = 20;
SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id
FROM employees
WHERE employee_id = 7566;
FROM employees
WHERE department_id = 20;
SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id
FROM employees
WHERE employee_id = 7566;
Below are the lists of hints by functional category with short description
| Functional Category | Hints | Description |
| Optimization Goals and Approaches |
ALL_ROWS | The ALL_ROWS hint instructs the optimizer to optimize a statement block with a goal of best throughput, which is minimum total resource consumption. |
| FIRST_ROWS | The FIRST_ROWS hint instructs Oracle to optimize an individual SQL statement for fast response, choosing the plan that returns the first n rows most efficiently. | |
| Access Path Hints |
CLUSTER | The CLUSTER hint instructs the optimizer to use a cluster scan to access the specified table. This hint applies only to clustered tables. |
| FULL | The FULL hint instructs the optimizer to perform a full table scan for the specified table. | |
| HASH | The HASH hint instructs the optimizer to use a hash scan to access the specified table. This hint applies only to tables stored in a table cluster. | |
| INDEX | The INDEX hint instructs the optimizer to use an index scan for the specified table. | |
| NO_INDEX | The NO_INDEX hint instructs the optimizer not to use one or more indexes for the specified table. | |
| INDEX_ASC | The INDEX_ASC hint instructs the optimizer to use an index scan for the specified table. | |
| INDEX_DESC | The INDEX_DESC hint instructs the optimizer to use a descending index scan for the specified table. | |
| INDEX_COMBINE | The INDEX_COMBINE hint instructs the optimizer to use a bitmap access path for the table. | |
| INDEX_JOIN | The INDEX_JOIN hint instructs the optimizer to use an index join as an access path. | |
| INDEX_FFS | The INDEX_FFS hint instructs the optimizer to perform a fast full index scan rather than a full table scan. | |
| INDEX_SS | The INDEX_SS hint instructs the optimizer to perform an index skip scan for the specified table. | |
| INDEX_SS_ASC | The INDEX_SS_ASC hint instructs the optimizer to perform an index skip scan for the specified table. | |
| INDEX_SS_DESC | The INDEX_SS_DESC hint instructs the optimizer to perform an index skip scan for the specified table. | |
| NATIVE_FULL_OUTER_JOIN | The NATIVE_FULL_OUTER_JOIN hint instructs the optimizer to use native full outer join, which is a native execution method based on a hash join. | |
| NO_NATIVE_FULL_OUTER_JOIN | The NO_NATIVE_FULL_OUTER_JOIN hint instructs the optimizer to exclude the native execution method when joining each specified table. | |
| NO_INDEX_FSS | The NO_INDEX_FFS hint instructs the optimizer to exclude a fast full index scan of the specified indexes on the specified table. | |
| NO_INDEX_SS | The NO_INDEX_SS hint instructs the optimizer to exclude a skip scan of the specified indexes on the specified table. | |
| Join Other Hints |
ORDERED | The ORDERED hint instructs Oracle to join tables in the order in which they appear in the FROM clause. |
| LEADING | The LEADING hint instructs the optimizer to use the specified set of tables as the prefix in the execution plan. This hint is more versatile than the ORDERED hint. | |
| Join Operation Hints |
USE_HASH | The USE_HASH hint instructs the optimizer to join each specified table with another row source using a hash join. |
| NO_USE_HASH | The NO_USE_HASH hint instructs the optimizer to exclude hash joins when joining each specified table to another row source using the specified table as the inner table. | |
| USE_MERGE | The USE_MERGE hint instructs the optimizer to join each specified table with another row source using a sort-merge join. | |
| NO_USE_MERGE | The NO_USE_MERGE hint instructs the optimizer to exclude sort-merge joins when joining each specified table to another row source using the specified table as the inner table. | |
| USE_NL | The USE_NL hint instructs the optimizer to join each specified table to another row source with a nested loops join, using the specified table as the inner table. | |
| USE_NL_WITH_INDEX | The USE_NL_WITH_INDEX hint instructs the optimizer to join the specified table to another row source with a nested loops join using the specified table as the inner table. | |
| NO_USER_NL | The NO_USE_NL hint instructs the optimizer to exclude nested loops joins when joining each specified table to another row source using the specified table as the inner table. | |
| Parallel Execution Hints |
PARALLEL | The PARALLEL hint instructs the optimizer to use the specified number of concurrent servers for a parallel operation. |
| NO_PARALLEL | The NO_PARALLEL hint overrides a PARALLEL parameter in the DDL that created or altered the table. | |
| PARALLEL_INDEX | The PARALLEL_INDEX hint instructs the optimizer to use the specified number of concurrent servers to parallelize index range scans, full scans, and fast full scans for partitioned indexes. | |
| NO_PARALLEL_INDEX | The NO_PARALLEL_INDEX hint overrides a PARALLEL parameter in the DDL that created or altered the index, thus avoiding a parallel index scan operation. | |
| PQ_DISTRIBUTE | The PQ_DISTRIBUTE hint instructs the optimizer how to distribute rows of joined tables among producer and consumer query servers. | |
| Query Transformation Hints |
FACT | The FACT hint is used in the context of the star transformation. It instructs the optimizer that the table specified in tablespec should be considered as a fact table. |
| NO_FACT | The NO_FACT hint is used in the context of the star transformation. It instruct the optimizer that the queried table should not be considered as a fact table. | |
| MERGE | The MERGE hint lets you merge views in a query. | |
| NO_MERGE | The NO_MERGE hint instructs the optimizer not to combine the outer query and any inline view queries into a single query. | |
| NO_EXPAND | The NO_EXPAND hint instructs the optimizer not to consider OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. | |
| USE_CONCAT | The USE_CONCAT hint instructs the optimizer to transform combined OR-conditions in the WHERE clause of a query into a compound query using the UNION ALL set operator. | |
| REWRITE | The REWRITE hint instructs the optimizer to rewrite a query in terms of materialized views, when possible, without cost consideration. | |
| NO_REWRITE | The NO_REWRITE hint instructs the optimizer to disable query rewrite for the query block, overriding the setting of the parameter QUERY_REWRITE_ENABLED. | |
| UNNEST | The UNNEST hint instructs the optimizer to unnest and merge the body of the subquery into the body of the query block that contains it, allowing the optimizer to consider them together when evaluating access paths and joins. | |
| NO_UNNEST | Use of the NO_UNNEST hint turns off unnesting . | |
| STAR_TRANSFORMATION | The STAR_TRANSFORMATION hint instructs the optimizer to use the best plan in which the transformation has been used. | |
| NO_STAR_TRANSFORMATION | The NO_STAR_TRANSFORMATION hint instructs the optimizer not to perform star query transformation. | |
| NO_QUERY_TRANSFORMATION | The NO_QUERY_TRANSFORMATION hint instructs the optimizer to skip all query transformations, including but not limited to OR-expansion, view merging, subquery unnesting, star transformation, and materialized view rewrite. | |
| XML Hints |
NO_XMLINDEX_REWRITE | The NO_XMLINDEX_REWRITE hint instructs the optimizer not to use any XMLIndex indexes for the current query. |
| NO_XML_QUERY_REWRITE | The NO_XML_QUERY_REWRITE hint instructs the optimizer to prohibit the rewriting of XPath expressions in SQL statements. | |
| Other Hints |
APPEND | The APPEND hint instructs the optimizer to use direct-path INSERT. |
| NOAPPEND | The NOAPPEND hint instructs the optimizer to use conventional INSERT by disabling parallel mode for the duration of the INSERT statement. | |
| CACHE | The CACHE hint instructs the optimizer to place the blocks retrieved for the table at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. | |
| NOCACHE | The NOCACHE hint instructs the optimizer to place the blocks retrieved for the table at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. | |
| CURSOR_SHARING_EXACT | The CURSOR_SHARING_EXACT hint instructs the optimizer to switch this behavior off. When you specify this hint, Oracle executes the SQL statement without any attempt to replace literals with bind variables. | |
| DRIVING_SITE | The DRIVING_SITE hint instructs the optimizer to execute the query at a different site than that selected by the database. | |
| DYNAMIC_SAMPLING | The DYNAMIC_SAMPLING hint instructs the optimizer how to control dynamic sampling to improve server performance by determining more accurate predicate selectivity and statistics for tables and indexes. | |
| MODEL_MIN_ANALYSIS | The MODEL_MIN_ANALYSIS hint instructs the optimizer to omit some compile-time optimizations of spreadsheet rules—primarily detailed dependency graph analysis. | |
| MONITOR | The MONITOR hint forces real-time SQL monitoring for the query, even if the statement is not long running. | |
| NO_MONITOR | The NO_MONITOR hint disables real-time SQL monitoring for the query, even if the query is long running. | |
| OPT_PARAM | The OPT_PARAM hint lets you set an initialization parameter for the duration of the current query only. | |
| PUSH_PRED | The PUSH_PRED hint instructs the optimizer to push a join predicate into the view. | |
| NO_PUSH_PRED | The NO_PUSH_PRED hint instructs the optimizer not to push a join predicate into the view. | |
| PUSH_SUBQ | The PUSH_SUBQ hint instructs the optimizer to evaluate nonmerged subqueries at the earliest possible step in the execution plan. | |
| NO_PUSH_SUBQ | The NO_PUSH_SUBQ hint instructs the optimizer to evaluate nonmerged subqueries as the last step in the execution plan. | |
| PX_JOIN_FILTER | This hint forces the optimizer to use parallel join bitmap filtering. | |
| NO_PX_JOIN_FILTER | This hint prevents the optimizer from using parallel join bitmap filtering. | |
| QB_NAME | Use the QB_NAME hint to define a name for a query block. | |
| RESULT_CACHE | The RESULT_CACHE hint instructs the database to cache the results of the current query or query fragment in memory and then to use the cached results in future executions of the query or query fragment. | |
| NO_RESULT_CACHE | The optimizer caches query results in the result cache if the RESULT_CACHE_MODE initialization parameter is set to FORCE. |
- birijan's blog
- Login to post comments
Feedback to: info@birijan.com.np
© Copyright 2007 birijan.com.np, All Rights Reserved
We are not responsible for any loss or liability incurred by using this information.