All Products
Search
Document Center

Other hints

Last Updated: Jun 18, 2021

QB_NAME Hint

Use the QB_NAME hint to specify a name for a query block. Then, you can use this name in the hint of an outer query or in the hint of an inline view to affect how queries are run on the tables that appear in the named query block. For more information about query block names, see the section "Specify query blocks in hints" in Hint overview.

The QB_NAME hint uses the following syntax:

/*+ QB_NAME ( queryblock ) */

Assume that two or more query blocks have the same name or different names are specified by two hints for the same query block. In this case, the optimizer ignores all of the names and hints that reference the query blocks. The system generates unique names for the query blocks that are not named by using the QB_NAME hint. These names can be displayed in plan tables and can also be used in the other query block hints.

In the following example, the QB_NAME hint is used:

SELECT /*+ QB_NAME(qb) FULL(@qb e) */ employee_id, last_name
  FROM employees e
  WHERE last_name = 'Smith';

READ_CONSISTENCY Hint

The READ_CONSISTENCY hint instructs a server to specify the mode of table reading for an SQL statement as weak consistency (specified by the

WEAK parameter) or strong consistency (specified by the STRONG parameter).

The READ_CONSISTENCY hint uses the following syntax:

/*+ READ_CONSISTENCY(WEAK[STRONG]) */

Example:

SELECT /*+ READ_CONSISTENCY(WEAK) */ * 
  FROM employees 
  WHERE employees.department_id = 1001;

FROZEN_VERSION Hint

The FROZEN_VERSION hint instructs a server to read the baseline data of a specified version.

The FROZEN_VERSION hint uses the following syntax:

/*+ FROZEN_VERSION (intnum) */

Example:

SELECT /*+ FROZEN_VERSION(1000) */ *
  FROM employees e
  WHERE e.department_id = 1001;

QUERY_TIMEOUT Hint

The QUERY_TIMEOUT hint instructs a server to specify the time-out period of executing an SQL statement. The unit for the time-out period is microseconds.

The QUERY_TIMEOUT hint uses the following syntax:

/*+ QUERY_TIMEOUT (intnum) */

In the following example, a time-out error is returned when the statement in the query fails to be fully executed within a second:

SELECT /*+ QUERY_TIMEOUT(1000000) */ *
  FROM employees e
  WHERE e.department_id = 1001;

LOG_LEVEL Hint

The LOG_LEVEL hint instructs a server to use a specified log level when an SQL statement is executed.

The LOG_LEVEL hint uses the following syntax:

/*+ LOG_LEVEL ([']log_level[']) */

In the following example, the DEBUG log level is used for executing the SQL statement:

SELECT /*+ LOG_LEVEL(DEBUG) */ *
  FROM employees e
  WHERE e.department_id = 1001;

USE_PLAN_CACHE Hint

The USE_PLAN_CACHE hint specifies whether to use the plan cache mechanism when a server executes an SQL statement. The NONE parameter specifies that the plan cache mechanism is not used. The DEFAULT parameter specifies that the server settings determine whether to use the plan cache mechanism.

The USE_PLAN_CACHE hint uses the following syntax:

/*+ USE_PLAN_CACHE (NONE[DEFAULT]) */

In the following example, the plan cache mechanism is not implemented for the statement:

SELECT /*+ USE_PLAN_CACHE(NONE) */ *
  FROM employees e
  WHERE e.department_id = 1001;

TRANS_PARAM Hint

The TRANS_PARAM hint specifies whether to use the parameters that are specified by the param parameter when a server performs a transaction. Only the FORCE_EARLY_LOCK_FREE parameter at the transaction level is supported. The FORCE_EARLY_LOCK_FREE parameter specifies the setting of releasing row locks in advance. When FORCE_EARLY_LOCK_FREE is set to TRUE, the parameters that are specified by param are used. When FORCE_EARLY_LOCK_FREE is set to FALSE, the parameters that are specified by param are not used. Take note of the following point: The parameter names must be enclosed in single quotation marks ('). The parameter values must be also enclosed in single quotation marks ('), except when the parameter values are numeric values.

The TRANS_PARAM hint uses the following syntax:

/*+ TRANS_PARAM ['param' , 'param_value'] */

Example:

SELECT /*+ TRANS_PARAM('FORCE_EARLY_LOCK_FREE' 'TRUE') */ *
  FROM employees e
  WHERE e.department_id = 1001;

TRACING Hint

The TRACING hint instructs a server to implement TRACING for some operators in execution plans.

The TRACING hint uses the following syntax:

/*+ TRACING(TRACING_NUM_LIST)*/

Example:

SELECT /*+ TRACING(1) */ *
  FROM employees e
  WHERE e.department_id = 1001;

STAT Hint

The STAT hint specifies that STAT is used to display the information about some operators in execution plans.

The STAT hint uses the following syntax:

/*+ STAT(TRACING_NUM_LIST) */

Example:

SELECT /*+ STAT(1) */ *
  FROM employees e
  WHERE e.department_id = 1001;

TOPK Hint

The TOPK hint instructs a server to set the precision and the minimum number of rows for a fuzzy match. The value of the PRECISION parameter is an integer that ranges from 0 to 100. This parameter specifies the percentage of rows that are returned for a fuzzy match. The MINIMUM_ROWS parameter specifies the minimum number of rows that are returned.

The TOPK hint uses the following syntax:

/*+ TOPK(PRECISION MINIMUM_ROWS) */

Example:

SELECT /*+ TOPK(1,10) */ *
  FROM employees e
  WHERE e.department_id = 1001;

TRACE_LOG Hint

The TRACE_LOG hint instructs a server to collect trace logs. The trace logs are displayed when you run the SHOW TRACE command.

The TRACE_LOG hint uses the following syntax:

/*+ TRACE_LOG */

Example:

SELECT /*+ TRACE_LOG */ *
  FROM employees e
  WHERE e.department_id = 1001;