Outline-related hints

OUTLINE_DATA structure

OUTLINE DATA specifies the hints for query optimization. Here is an example of the 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 suggestions
  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")
  // Conversions
  MERGE(@"SEL$2" > "SEL$1")
  // Global prompt
  OPTIMIZER_FEATURES_ENABLE('4.0.0.0')
  PARALLEL(2)
  END_OUTLINE_DATA
*/

If the optimizer version in the OPTIMIZER_FEATURES_ENABLE hint specified in the hint list between BEGIN_OUTLINE_DATA and END_OUTLINE_DATA is a valid value, this hint is recognized as OUTLINE DATA.

OPTIMIZER_FEATURES_ENABLE Hint

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

Syntax

/*+ OPTIMIZER_FEATURES_ENABLE ( version_number ) */

Parameters

• version_number : Optimizer version number.

Examples

-- Use the OPTIMIZER_FEATURES_ENABLE hint to specify that the optimizer should use features from version 4.0.0.0 when generating an 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 a query block to be assigned a name that can then be referenced in other hints. This is very useful for more precise control over specific parts of the query.

For more information, see Use the Query Block Name and QB_NAME Hint to control query optimization.

Syntax

/*+ QB_NAME(queryblock) */

Parameters

• qb_name is case-insensitive and cannot contain special characters. The maximum length of qb_name is 20 characters.

Considerations

QB_NAME is not valid in the following contexts:

• Specify multiple valid qb_name values in the same query block. Specify the same qb_name value in queries from different levels.

Examples

-- Use the QB_NAME hint to name the query block 'qb' and the FULL hint to force 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';

CACHE Hint

The CACHE hint enables cache for the specified table and its indexes, and this cache remains enabled during all subsequent queries on the table.

Syntax:

/*+ CACHE ([@qb_name] table_name) */

Parameters

• @qb_name: Optional. The name of the query block that is affected by this hint. If you omit this parameter, the hint applies to the main query block of the current query.
• table_name - the name of the table.

Example:

• Manually enable the cache for tbl1.

  obclient> SELECT /*+ CACHE(tbl1) */ * FROM tbl1;
  

• Manually enable caching for tbl1, which is in the default state and automatically enabled.

  obclient> SELECT /*+ CACHE(tbl1) */ * FROM tbl1, tbl2
      WHERE tbl1.col1 = tbl2.col1;
  

NOCACHE Hint

The NOCACHE hint is used to manually disable caching for the specified table and its indexes during a query.

Syntax:

/*+ CACHE ([@qb_name] table_name) */

Parameters

• @qb_name: Optional. Specifies the name of the query block (query block) to which the hint applies. If omitted, it defaults to the main query block of the current query.
• table_name: the name of the table.

Example:

Manually disable the cache for the tbl1 table.

obclient> SELECT /*+ NOCACHE(tbl1) */ * FROM tbl1;

When multiple CACHE/NOCACHE hints are specified, all hints are merged. If a table is specified in both a CACHE hint and a NOCACHE hint, the CACHE hint takes precedence, and the cache is enabled for the specified table.

Examples are as follows:

CACHE is enabled. Manually enable cache for tbl1.

obclient> SELECT /*+ CACHE(tbl1) NOCACHE(tbl1) */ * FROM tbl1;