Outline-related hints

OUTLINE DATA structure

OUTLINE DATA identifies hints used in query optimization. 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
*/

The optimizer version specified in the OPTIMIZER_FEATURES_ENABLE hint in the optimization hints between BEGIN_OUTLINE_DATA and END_OUTLINE_DATA is identified as the optimizer version used in the query.

OPTIMIZER_FEATURES_ENABLE hint

The OPTIMIZER_FEATURES_ENABLE hint specifies the optimizer version used in 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 use the features of Oracle Database 11g Release 2 (11.2) in the query.
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 fine-grained control over a specific part of the query.

For more information, see Use query block names and the QB_NAME hint to control query optimization.

Syntax

/*+ QB_NAME(queryblock) */

Parameters

• qb_name, the name of the query block, is case-insensitive, cannot contain special characters, and must be 20 characters or less in length.

Limitations

The QB_NAME hint is invalid in the following cases:

• When multiple valid qb_name hints are specified in the same layer of queries.
• When the same qb_name hint is specified for query blocks in different layers of queries.

Examples

-- Use the QB_NAME hint to name the query block 'qb', and use the FULL hint to specify a full table scan of the employees table.
SELECT /*+ QB_NAME(qb) FULL(@qb e) */ employee_id, last_name
FROM employees e
WHERE last_name = 'Smith';