| Hint name | Description |
|---|---|
BEGIN_OUTLINE_DATA |
Outline data start marker. |
END_OUTLINE_DATA |
Outline data end marker. |
QB_NAME |
Specifies a name for a query block to be referenced in other hints. |
OPTIMIZER_FEATURES_ENABLE |
Specifies the optimizer version for query plan generation. |
OUTLINE DATA structure
OUTLINE DATA identifies hints during query optimization. Here is an example of a valid OUTLINE DATA structure:
SELECT /*+PARALLEL(2)*/ 1
FROM (SELECT c2 FROM t1 WHERE c1 = 1) v, t2
WHERE t2.c2 = v.c2;
/*+
BEGIN_OUTLINE_DATA
// Optimization hints
LEADING(@"SEL$6D1A4751" ("TEST.T1"@"SEL$2" "TEST.T2"@"SEL$1"))
USE_HASH(@"SEL$6D1A4751" "TEST.T2"@"SEL$1")
PQ_DISTRIBUTE(@"SEL$6D1A4751" "TEST.T2"@"SEL$1" NONE BROADCAST)
PX_JOIN_FILTER(@"SEL$6D1A4751" "TEST.T2"@"SEL$1")
FULL(@"SEL$6D1A4751" "TEST.T1"@"SEL$2")
FULL(@"SEL$6D1A4751" "TEST.T2"@"SEL$1")
// Transformation hints
MERGE(@"SEL$2" > "SEL$1")
// Global hints
OPTIMIZER_FEATURES_ENABLE('4.0.0.0')
PARALLEL(2)
END_OUTLINE_DATA
*/
Hints between BEGIN_OUTLINE_DATA and END_OUTLINE_DATA that include the OPTIMIZER_FEATURES_ENABLE hint and specify a valid optimizer version are recognized as OUTLINE DATA.
OPTIMIZER_FEATURES_ENABLE hint
The OPTIMIZER_FEATURES_ENABLE hint specifies the optimizer version used for the current query.
Syntax
/*+ OPTIMIZER_FEATURES_ENABLE ( version_number ) */
Parameters
version_number: the optimizer version.
Examples
-- Use the OPTIMIZER_FEATURES_ENABLE hint to specify that the optimizer should use features from version 4.0.0.0 when generating the execution plan.
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('4.0.0.0') */ employee_id, last_name
FROM employees
WHERE last_name = 'Smith';
QB_NAME hint
The QB_NAME hint allows you to specify a name for a query block, which can then be referenced in other hints. This is particularly useful when you need to apply more granular control to specific parts of a query.
For more information, see Control query optimization using Query Block Name and QB_NAME Hint
Syntax
/*+ QB_NAME(queryblock) */
Parameters
qb_name: case-insensitive, cannot contain special characters, and has a maximum length of 20 characters.
Considerations
The QB_NAME hint is ineffective in the following scenarios:
- Specifying multiple valid
qb_namevalues at the same query level. - Specifying the same
qb_namevalue at different query levels.
Examples
-- Use the QB_NAME hint to name the query block 'qb' and the FULL hint to force a full table scan on the employees table.
SELECT /*+ QB_NAME(qb) FULL(@qb e) */ employee_id, last_name
FROM employees e
WHERE last_name = 'Smith';