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
MONITOR hint
The MONITOR hint implements real-time SQL monitoring by force 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 that the server executes when running an SQL statement.
The syntax of the LOG_LEVEL hint is as follows:
/*+ LOG_LEVEL ([']log_level[']) */
The following example uses the DEBUG log level to execute 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 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 at the transaction level in advance. When FORCE_EARLY_LOCK_FREE is TRUE, row unlocking at the transaction level in advance 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 a minimum number of rows 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 returned rows.
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 collects trace logs. The collected trace logs are displayed when the SHOW TRACE command 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;