Hint name |
Explanation |
|---|---|
BEGIN_OUTLINE_DATA |
Start marker of the outline data. |
END_OUTLINE_DATA |
End-of-outline data marker. |
QB_NAME |
Specify a name for the query block so that it can be referenced in other hints. |
OPTIMIZER_FEATURES_ENABLE |
Query plan generation optimizer version. |
OUTLINE DATA structure
OUTLINE DATA is used to identify hint information during query optimization. A valid OUTLINE DATA structure example is as follows:
SELECT /*+PARALLEL(2)*/ 1
FROM (SELECT c2 FROM t1 WHERE c1 = 1) v, t2
WHERE t2.c2 = v.c2;
/*+
BEGIN_OUTLINE_DATA
// Optimization prompt
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")
// Conversion prompt
MERGE(@"SEL$2" > "SEL$1")
// Global prompt
OPTIMIZER_FEATURES_ENABLE('4.0.0.0')
PARALLEL(2)
END_OUTLINE_DATA
*/
In the prompt information between BEGIN_OUTLINE_DATA and END_OUTLINE_DATA, if it contains the OPTIMIZER_FEATURES_ENABLE prompt and specifies a valid optimizer version, these prompts will be recognized as OUTLINE DATA.
OPTIMIZER_FEATURES_ENABLE Hint
The OPTIMIZER_FEATURES_ENABLE hint is used to specify the optimizer version for the current query.
Syntax
/*+ OPTIMIZER_FEATURES_ENABLE ( version_number ) */
Parameter description
version_number: The optimizer version.
Examples
-- Use the OPTIMIZER_FEATURES_ENABLE hint to specify that the optimizer use features from version 4.0.0.0 when generating execution plans.
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 useful when you need more fine-grained control over specific parts of a query.
For more information, see Control query optimization using the Query Block Name and QB_NAME hint.
Syntax
/*+ QB_NAME(queryblock) */
Parameters
qb_name: case-insensitive, cannot contain special characters, and must be up to 20 characters long.
Considerations
The QB_NAME hint is invalid in the following scenarios:
- Specifying multiple valid
qb_nameswithin the same query level. - Specifying the same
qb_nameacross different query levels.
Examples
-- Use the QB_NAME hint to name the query block 'qb', and use 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';