Outline related hints

OUTLINE DATA structure

OUTLINE DATA is used to identify hints during the query optimization process. An example of a valid OUTLINE DATA structure 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 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
*/

In the hints between BEGIN_OUTLINE_DATA and END_OUTLINE_DATA, if the OPTIMIZER_FEATURES_ENABLE hint is included and a valid optimizer version is specified, these hints are recognized as OUTLINE DATA.

OPTIMIZER_FEATURES_ENABLE Hint

The OPTIMIZER_FEATURES_ENABLE hint specifies the version of the optimizer used for the current query.

Syntax

/*+ OPTIMIZER_FEATURES_ENABLE ( version_number ) */

Parameters

• version_number: The version of the optimizer.

Example

-- 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 exert finer control over specific parts of a query.

For more information, see Use Query Block Name and QB_NAME Hint to Control Query Optimization

Syntax

/*+ QB_NAME(queryblock) */

Parameters

• qb_name: The name of the query block. It is 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_name values at the same query level.
• Specifying the same qb_name value at different query levels.

Example

-- 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';