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.
Syntax of the CURSOR_SHARING_EXACT hint:
/*+ CURSOR_SHARING_EXACT */
Sample code 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.
Syntax of the MONITOR hint:
/*+ MONITOR */
PX_JOIN_FILTER hint
The PX_JOIN_FILTER hint forces the optimizer to use parallel join bitmap filtering.
Syntax of the PX_JOIN_FILTER hint:
/*+ PX_JOIN_FILTER (tablespec) */
NO_PX_JOIN_FILTER hint
The NO_PX_JOIN_FILTER hint prevents the optimizer from using parallel join bitmap filtering.
Syntax of the NO_PX_JOIN_FILTER hint:
/*+ 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.
Syntax of the QB_NAME hint:
/*+ 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.
Sample code 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.
Syntax of the READ_CONSISTENCY hint:
/*+ READ_CONSISTENCY(WEAK[STRONG]) */
Sample code 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.
Syntax of the QUERY_TIMEOUT hint:
/*+ 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.
Syntax of the LOG_LEVEL hint:
/*+ 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.
Syntax of the USE_PLAN_CACHE hint:
/*+ 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.
Syntax of the TRANS_PARAM hint:
/*+ TRANS_PARAM ['param' , 'param_value'] */
Sample code 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.
Syntax of the TRACING hint:
/*+ TRACING(TRACING_NUM_LIST)*/
Sample code 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.
Syntax of the STAT hint:
/*+ STAT(TRACING_NUM_LIST) */
Sample code 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.
Syntax of the TOPK hint:
/*+ TOPK(PRECISION MINIMUM_ROWS) */
Sample code 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.
Syntax of the TRACE_LOG hint:
/*+ TRACE_LOG */
Sample code of the TRACE_LOG hint:
SELECT /*+ TRACE_LOG */ *
FROM employees e
WHERE e.department_id = 1001;