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 SQL statements with bind variables. This feature is controlled by the CURSOR_SHARING parameter. That is, when cursor_sharing='exact', you do not need to parameterize. For more information, see cursor_sharing.

You can specify the CURSOR_SHARING_EXACT hint to indicate the optimizer to disable this behavior. When you specify this hint, OceanBase Database does not attempt to replace literals with bind variables during the execution of SQL statements, thereby enabling parameter rewriting.

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

MAX_CONCURRENT Hint specifies the maximum number of threads allowed to execute the SQL statement concurrently. For example, setting it to MAX_CONCURRENT(1) specifies that the SQL statement runs in single-threaded mode. This means only one thread is processing at a time.

The syntax of the MAX_CONCURRENT hint is as follows:

/*+ MAX_CONCURRENT() */

Note: MAX_CONCURRENT() can be set to a negative value, but this does not affect the concurrent control of SQL statements. It is recommended that you set MAX_CONCURRENT() to a positive integer greater than 0.

MONITOR Hint

The MONITOR hint forces real-time SQL monitoring on a query. It is also effective for non-OLTP statements.

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 bit map filtering.

The syntax for 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 the parallel join filter bitmap.

The syntax for 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 define a name for the query block. This name can be used in the hints of the outer query or in the inline views to affect the queries on the named query blocks.

The QB_NAME hint has the following syntax:

/*+ QB_NAME ( queryblock ) */

The Optimizer ignores all references to the name and Hints of a query block that has the same name as another query block or has been specified with different names by different Hints. Query blocks that have not been named by using the QB_NAME Hint have uniquely generated names by the system. These names can be displayed in the plan table and used in other Hints in the query block.

Examples of the QB_NAME hint are shown here.

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 specifies the read consistency mode for the tables accessed by the specified SQL statement.

( WEAK or STRONG )

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;

Weak reads are supported when the isolation level is set to Repeatable Read or Serializable. For example:

obclient> SET transaction read only;
Query OK, 0 rows affected (0.001 sec)
obclient> SELECT /*+ read_consistency(weak)*/ * FROM t WHERE a=2;
+------+------+
| ID   | A    |
+------+------+
| NULL |    2 |
+------+------+
1 row in set (0.001 sec)

QUERY_TIMEOUT Hint

QUERY_TIMEOUT hint specifies the maximum time (in microseconds) allowed to elapse before the server terminates execution of the corresponding statement.

The syntax of the QUERY_TIMEOUT hint is as follows:

/*+ QUERY_TIMEOUT (intnum) */

An example is as follows. If a query does not return a result within 1 second, it returns an "out of time" error.

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

LOG_LEVEL Hint

The LOG_LEVEL hint indicates which log level to use when the server runs the specified SQL statement.

The LOG_LEVEL hint follows this syntax:

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

For example, you can run the following SQL statements with 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 indicates whether the server should run a specific SQL statement under the plan cache mechanism. The parameter NONE means to not execute the plan cache mechanism, and the parameter DEFAULT means to follow the server's settings to determine whether to execute the plan cache mechanism.

The syntax of the USE_PLAN_CACHE hint is as follows:

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

The following example shows a statement that does not use the plan cache:

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

TRANS_PARAM Hint

The TRANS_PARAM hint indicates whether the server should perform transactions based on the value of the param parameter, which currently only supports the transaction-level parameter FORCE_EARLY_LOCK_FREE. When the FORCE_EARLY_LOCK_FREE value is TRUE, it indicates support for this parameter; FALSE indicates no support. Note that the parameter name and value must be enclosed in single quotes ( ' ') in the statement. Numerical parameter values do not require single quotes.

Syntax The syntax of the TRANS_PARAM hint is as follows:

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

The TRANS_PARAM hint is given in the following example.

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

TRACING Hint

The TRACING hint indicates that the server should trace operators in certain execution plans using the TRACING mechanism.

Syntax of the TRACING hint:

/*+ TRACING(TRACING_NUM_LIST)*/

The TRACING hint is described as follows:

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

STAT Hint

The STAT Hint indicates that the server displays 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 indicates that the server sets the precision and minimum number of rows for fuzzy queries. The PRECISION parameter is an integer in the range [0, 100], representing the percentage of rows to return for a fuzzy query. The MINIMUM_ROWS parameter specifies the minimum number of rows to return.

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 indicates that the server collects trace logs. The trace logs collected by the server are displayed when you run the SHOW TRACE command.

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

The OPT_PARAM hint specifies some optimizer-related configuration items 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 configuration item or system variable.
• parameter_value: the value of the variable.

The OPT_PARAM hint applies to the following parameters:

The following example uses the OPT_PARAM hint to enable the IN optimization for the current query by setting the enable_in_range_optimization parameter.

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