Other hints

OceanBase Database also supports the CURSOR_SHARING_EXACT, MONITOR, PX_JOIN_FILTER, NO_PX_JOIN_FILTER, QB_NAME, READ_CONSISTENCY, QUERY_TIMEOUT, LOG_LEVEL, USE_PLAN_CACHE, TRANS_PARAM, TRACING, STAT, TOPK, and TRACE_LOG hints.

CURSOR_SHARING_EXACT hint

OceanBase Database allows you to replace literals in an SQL statement with bind variables. This feature is controlled by the cursor_sharing variable. When cursor_sharing is set to exact, parameterization is not required. For more information, see cursor_sharing.

You can specify the CURSOR_SHARING_EXACT hint to instruct the optimizer to disable this behavior. If this hint is specified, OceanBase Database will not attempt to replace literals with bind variables when executing SQL statements, thereby implementing parameter rewrites.

The syntax of the CURSOR_SHARING_EXACT hint is as follows:

/*+ CURSOR_SHARING_EXACT */

Here is an example of the CURSOR_SHARING_EXACT hint:

obclient> CREATE TABLE t(c1 INT PRIMARY KEY,c2 VARCHAR(20));
Query OK, 0 rows affected

obclient> INSERT INTO t VALUES(5,'oceanbase');
Query OK, 1 row affected

obclient> SELECT /*+ CURSOR_SHARING_EXACT*/ * FROM t WHERE c1=5 AND c2 ='oceanbase';
+----------+----------+
|    C1    |   C2     |
+----------+----------+
|    5     | oceanbase|
+---------------------+
1 row in set   

obclient> SELECT query_sql, statement FROM gv$ob_plan_cache_plan_stat; GV$OB_PLAN_CACHE_PLAN_STAT
+---------------------------------------------------------------------------+---------------------------------------------------------------+
|  query_sql                                                                |   statement                                                   |
+---------------------------------------------------------------------------+---------------------------------------------------------------+
| SELECT /*+ CURSOR_SHARING_EXACT*/ * FROM t WHERE c1=5 AND c2 ='oceanbase' |  SELECT /*+ CURSOR_SHARING_EXACT*/ * FROM t WHERE c1=5 AND c2 ='oceanbase' |     
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

MAX_CONCURRENT hint

You can use the MAX_CONCURRENT hint to specify the maximum number of concurrent execution threads for an SQL statement. For example, you can specify MAX_CONCURRENT(1) to instruct single-thread execution for the SQL statement. In this case, only one thread is processing the task at any time.

The syntax of the MAX_CONCURRENT hint is as follows:

/*+ MAX_CONCURRENT() */

Note that the value in MAX_CONCURRENT() can be a negative number, which does not take effect for concurrency control on an SQL statement. We recommend that you set the value in MAX_CONCURRENT() to a positive integer.

MONITOR hint

The MONITOR hint forcibly implements real-time SQL monitoring on queries. The hint also takes effect for statements that are not running for a long time.

The syntax of the MONITOR hint is as follows:

/*+ MONITOR */

PX_JOIN_FILTER hint

The PX_JOIN_FILTER hint forces the optimizer to use parallel join bitmap filtering.

The syntax of the PX_JOIN_FILTER hint is as follows:

/*+ PX_JOIN_FILTER (tablespec) */

NO_PX_JOIN_FILTER hint

The NO_PX_JOIN_FILTER hint prevents the optimizer from using parallel join bitmap filtering.

The syntax of the NO_PX_JOIN_FILTER hint is as follows:

/*+ NO_PX_JOIN_FILTER (tablespec) */

QB_NAME hint

You can use the QB_NAME hint to specify the name of the query block. The name specified with this hint can be used in the hint of an external query or in the hint of the inner-join view, thereby shaping the query of the table in the named query block.

The syntax of the QB_NAME hint is as follows:

/*+ QB_NAME ( queryblock ) */

If two or more query blocks have the same name, or if the same query block is hinted twice with different names, the optimizer ignores all the names and the hints referencing them. Query blocks that are not named by using the QB_NAME hint have unique names generated by the system. These names can be displayed in the plan table or used in other hints in the query block.

Here is an example of the QB_NAME hint:

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 the server to specify that the table read by an SQL statement is in

WEAK or STRONG mode.

The syntax of the READ_CONSISTENCY hint is as follows:

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

Here is an example of the READ_CONSISTENCY hint:

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

QUERY_TIMEOUT hint

The QUERY_TIMEOUT hint instructs the server to set the execution timeout period for an SQL statement, in microseconds.

The syntax of the QUERY_TIMEOUT hint is as follows:

/*+ QUERY_TIMEOUT (intnum) */

In the following example, a timeout error is returned if the execution of the statement is not completed within one second.

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

LOG_LEVEL hint

The LOG_LEVEL hint specifies the log level at which the server executes an SQL statement.

The syntax of the LOG_LEVEL hint is as follows:

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

The following example executes the SQL statement at the DEBUG log level:

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

USE_PLAN_CACHE hint

The USE_PLAN_CACHE hint specifies whether the server needs to run an SQL statement in the plan caching mechanism. The parameter NONE indicates not to execute the plan caching mechanism, whereas the parameter DEFAULT indicates to execute or not execute the plan caching mechanism based on the server settings.

The syntax of the USE_PLAN_CACHE hint is as follows:

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

The following example does not run the SQL statement by using the plan caching mechanism:

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

TRANS_PARAM hint

The TRANS_PARAM hint specifies whether the server needs to process transactions based on the parameters specified in param. OceanBase Database supports only the FORCE_EARLY_LOCK_FREE parameter for row unlocking in advance at the transaction level. When FORCE_EARLY_LOCK_FREE is TRUE, row unlocking in advance at the transaction level is supported. The value FALSE indicates that row unlocking is not supported. Note that the parameter names and values must be enclosed in single quotation marks (''), except for parameter values of a numeric type.

The syntax of the TRANS_PARAM hint is as follows:

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

Here is an example of the TRANS_PARAM hint:

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

TRACING hint

The TRACING hint instructs the server to use TRACING to trace operators in some execution plans.

The syntax of the TRACING hint is as follows:

/*+ TRACING(TRACING_NUM_LIST)*/

Here is an example of the TRACING hint:

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

STAT hint

The STAT hint instructs the server to use STAT to display information for operators in some execution plans.

The syntax of the STAT hint is as follows:

/*+ STAT(TRACING_NUM_LIST) */

Here is an example of the STAT hint:

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

TOPK hint

The TOPK hint instructs the server to set the precision and the minimum number of rows returned for fuzzy queries. The value of the PRECISION parameter is an integer type with a value range of [0, 100], which means the percentage of rows queried in a fuzzy query. MINIMUM_ROWS specifies the minimum number of rows returned.

The syntax of the TOPK hint is as follows:

/*+ TOPK(PRECISION MINIMUM_ROWS) */

Here is an example of the TOPK hint:

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

TRACE_LOG hint

The TRACE_LOG hint instructs the server to collect trace logs. The collected trace logs are displayed when the SHOW TRACE statement is executed.

The syntax of the TRACE_LOG hint is as follows:

/*+ TRACE_LOG */

Here is an example of the TRACE_LOG hint:

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

OPT_PARAM hint

You can use the OPT_PARAM hint to specify to update optimizer-related parameters or system variables at the query level. The syntax of the OPT_PARAM hint is as follows:

/*+ OPT_PARAM ( parameter_name [,] parameter_value ) */

Parameters

• parameter_name: the name of the parameter or system variable.
• parameter_value: the value of the parameter or system variable.

The OPT_PARAM hint takes effect for the following parameters:

In the following example, the OPT_PARAM hint is used to specify the value of enable_in_range_optimization to enable IN optimization for the current query.

SELECT /*+ opt_param('enable_in_range_optimization', 'true') */ *
from t1
where c1 in (1,2,3,4,5,...,1000)
and c2 in (1,2,3,4,5,...,1000);