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, TRACE_LOG, DIRECT, and OPT_PARAM hints.

CURSOR_SHARING_EXACT hint

OceanBase Database allows you to replace literals in an SQL statement with bind variables. This feature is enabled only when the CURSOR_SHARING parameter is set to 'exact'. For more information, see cursor_sharing.

You can specify the CURSOR_SHARING_EXACT hint to instruct the optimizer to disable this behavior. When this hint is specified, OceanBase Database does not attempt to replace literals with bind variables during the execution of an SQL statement.

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 forces real-time SQL monitoring for a query, including statements that are not long-running.

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 joins with bitmap filtering.

The syntax of the PX_JOIN_FILTER hint is as follows:

/*+ PX_JOIN_FILTER (tablespec) */

PX_NODE_POLICY

The PX_NODE_POLICY hint affects the candidate node pool for a pure-compute DFO. You can also set this hint by using the tenant parameter PX_NODE_POLICY.

The syntax of the PX_NODE_POLICY hint is as follows:

/*+ PX_NODE_POLICY (policy) */

policy specifies the number of machines used to accelerate the task. You can choose all machines in the current zone or all machines in the current cluster. In theory, the more machines used, the better the task acceleration. However, using all machines in the cluster may increase the load on other machines. Therefore, you need to balance the performance requirements and the overall load of the cluster when you select the machines.

The following values are available:

• DATA: specifies to select all data nodes involved in the current SQL query.
• ZONE: specifies to select all machines in the current zone for the tenant to which the current SQL query belongs.
• CLUSTER: specifies to select all machines in the cluster for the tenant to which the current SQL query belongs.

Example:

/*+parallel(20) PX_NODE_POLICY('data')*/

PX_NODE_ADDRS

The PX_NODE_ADDRS hint forces the system to distribute a pure-compute DFO across specific servers.

The syntax of the PX_NODE_ADDRS hint is as follows:

/*+ PX_NODE_ADDRS (ADDRS) */

Here, ADDRS specifies the servers to which the DFO is distributed.

Example:

/*+parallel(20) PX_NODE_POLICY('cluster') PX_NODE_ADDRS('11.124.x.xx:10xxx', '11.124.x.xx:10xxx')*/

PX_NODE_COUNT

The PX_NODE_COUNT hint forces the system to allocate a specified number of nodes for a pure computation DFO.

The syntax of the PX_NODE_COUNT hint is as follows:

/*+ PX_NODE_COUNT (COUNT) */

where COUNT specifies the number of nodes that you select. The number of nodes that you select cannot exceed the size of the candidate node pool. Here is an example: DFOs are allocated at the cluster level, and 5 machines in the cluster are used. If the cluster has only three machines, which is less than five, all machines in the cluster are used.

/*+parallel(20) PX_NODE_POLICY('cluster') PX_NODE_COUNT(5)*/

NO_PX_JOIN_FILTER hint

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

The syntax of the NO_PX_JOIN_FILTER hint is as follows:

/*+ NO_PX_JOIN_FILTER (tablespec) */

QB_NAME Hint

Use the QB_NAME hint to define the name of a query block. You can use the name in an external hint or an inline view hint to affect table queries within 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 a query block is given two different names by hints, the optimizer ignores all names and hints referring to that query block. A query block not named by the QB_NAME hint has a unique name generated by the system. These names can be displayed in the plan table or used in other hints within 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 weak consistency (by setting the

WEAK parameter) or strong consistency (by setting the STRONG parameter) for the table mode read by the SQL statement.

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 a timeout for the execution of an SQL statement, in microseconds.

The syntax of the QUERY_TIMEOUT hint is as follows:

/*+ QUERY_TIMEOUT (intnum) */

Here is an example. In this example, a timeout error is returned if the query is not completed within 1 second.:

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

LOG_LEVEL hint

The LOG_LEVEL hint specifies the log level to be used when a server executes a specific SQL statement.

The syntax of the LOG_LEVEL hint is as follows:

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

The following example shows how to execute an 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 indicates whether the server should run when executing a specific SQL statement in the plan cache mechanism. The NONE value specifies not to use the plan cache mechanism. The DEFAULT value specifies to use the plan cache mechanism based on the server settings.

The syntax of the USE_PLAN_CACHE hint is as follows:

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

Here is an example. The following statement does not use the plan cache mechanism:

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

TRANS_PARAM Hint

The TRANS_PARAM hint indicates whether to execute transactions based on the parameters specified by the param parameter. At present, only the FORCE_EARLY_LOCK_FREE parameter at the transaction level is supported. If the value of FORCE_EARLY_LOCK_FREE is TRUE, early lock-free execution is supported. If the value is FALSE, early lock-free execution is not supported. Note that the parameter name and value must be enclosed in single quotation marks (' '). If the value is a numeric value, you can omit the quotation marks.

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 the TRACING method to trace some operators in an execution plan.

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 indicates that the STAT information should be displayed for some operators in the execution plan.

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 minimum number of rows for a fuzzy query. The PRECSION parameter accepts integer values ranging from 0 to 100, representing the percentage of rows returned by the system during a fuzzy query. The MINIMUM_ROWS parameter specifies the minimum number of rows to be 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 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;

DIRECT hint

The DIRECT hint enables direct load by specifying LOAD DATA and INSERT statements, thereby improving the data import efficiency and performance.

Syntax

The syntax of the DIRECT hint is as follows:

/*+ DIRECT (/*+ direct(need_sort, max_errors_allowed, load_mode) */)*/

Parameters

• need_sort: specifies whether to sort the imported data. The value true indicates that the data needs to be sorted, and the value false indicates that the data does not need to be sorted.

• max_errors_allowed: specifies the maximum number of error rows allowed. The import process will fail if the number of error rows exceeds this number.

• load_mode: specifies the import mode. Valid values:

  • full: indicates full load. This is the default value.
  • inc: indicates incremental load. The INSERT and IGNORE semantics are supported.
  • inc_replace: indicates incremental load without checking for duplicate primary keys. This is equivalent to the REPLACE semantics for incremental load.

Examples

Examples of using the DIRECT hint in LOAD DATA statements

Enable incremental direct load

LOAD DATA /*+ DIRECT(true, 0, inc) */
INFILE 'datafile.txt'
INTO TABLE mytable
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

In this example, DIRECT(true, 0, inc) indicates to enable incremental direct load, sort the data, and allow at most 0 row error.

Enable full direct load

LOAD DATA
/*+ PARALLEL(4) DIRECT(true, 0, full) */
REMOTE_OSS INFILE 'oss://example.com/datafile.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

In this example, PARALLEL(4) specifies the degree of parallelism as 4, and DIRECT(true, 0, full) indicates to enable full direct load, sort the data, and allow at most 0 row error.

For more information, see Full direct load.

Examples of using the DIRECT hint in INSERT INTO SELECT statements

To enable direct load in the INSERT INTO SELECT statement, you must use it with enable_parallel_dml. The format is: /*+ direct(bool, int, load_mode)} enable_parallel_dml PARALLEL(N) */.

Enable incremental direct load

-- Enable incremental direct load and parallel DML to incrementally import data from old_table to new_table.
INSERT /*+ direct(true, 0, 'inc') enable_parallel_dml PARALLEL(4) */ INTO new_table (id, name, value)
SELECT id, name, value
FROM old_table;

In this example, direct(true, 0, 'inc') enables incremental direct load, sorts the data, and allows at most 0 row error; enable_parallel_dml enables parallel DML; and PARALLEL(4) specifies the degree of parallelism as 4.

Enable full direct load

-- Enable full direct load and parallel DML to insert data from old_table to new_table.
INSERT /*+ direct(true, 0, 'full') enable_parallel_dml PARALLEL(4) */ INTO new_table (id, name, value)
SELECT id, name, value
FROM old_table;

In this example, direct(true, 0, 'full') enables full direct load, sorts the data, and allows at most 0 row error; enable_parallel_dml enables parallel DML; and PARALLEL(4) specifies the degree of parallelism as 4.

For more information, see Import data by using the INSERT INTO SELECT statement.

NO_DIRECT Hint

The NO_DIRECT hint disables direct load in the LOAD DATA, INSERT INTO SELECT, and CREATE TABLE AS SELECT statements.

Syntax

The syntax of the NO_DIRECT hint is as follows:

/*+ NO_DIRECT */

Parameters

NO_DIRECT: Forces the single SQL statement to disable direct load. Once this hint is added to an SQL statement, other hints that enable direct load in the statement are ignored, and the statement performs conventional load.

Examples

• Use of NO_DIRECT in LOAD DATA statements

  LOAD DATA /*+ NO_DIRECT */ [REMOTE_OSS | LOCAL] INFILE 'file_name' INTO TABLE table_name [COMPRESSION]...
  

• Use of NO_DIRECT in INSERT INTO SELECT statements

  INSERT /*+ NO_DIRECT */ INTO  table_name select_sentence
  

• Use of NO_DIRECT in CREATE TABLE AS SELECT statements

  CREATE /*+ NO_DIRECT */ TABLE table_name [AS] select_sentence
  

OPT_PARAM hint

The OPT_PARAM hint specifies optimizer-related parameters/system variables to be updated at the query level. The syntax of the OPT_PARAM hint is as follows:

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

Parameter description

• parameter_name: the name of the parameter or system variable.
• parameter_value: the value of the variable. The OPT_PARAM hint applies to the following parameters:
  • rowsets_enabled: specifies whether to enable vectorization. The parameter is of the VARCHAR type. The value can be TRUE or FALSE, which must be enclosed in single quotation marks (').
  • rowsets_max_rows: the number of rows returned in one batch. The parameter is of the INT type. The value range is [0, 65535]. Do not enclose the value in single quotation marks (').
  • enable_newsort: specifies whether to enable the newsort optimization in a query. The parameter is of the VARCHAR type. The value can be TRUE or FALSE, which must be enclosed in single quotation marks (').
  • use_part_sort_mgb: specifies whether to enable the part sort merge group by optimization in a query. The parameter is of the VARCHAR type. The value can be TRUE or FALSE, which must be enclosed in single quotation marks (').
  • enable_in_range_optimization: specifies whether to enable the IN optimization for a query. The parameter is of the VARCHAR type. The value can be TRUE or FALSE, which must be enclosed in single quotation marks (').
  • xsolapi_generate_with_clause: specifies whether to enable the CTE extraction rewrite in a query. The parameter is of the VARCHAR type. The value can be TRUE or FALSE, which must be enclosed in single quotation marks (').
  • preserve_order_for_pagination: specifies whether to add ORDER BY for a pagination query. The parameter is of the VARCHAR type. The value can be TRUE or FALSE, which must be enclosed in single quotation marks (').
  • storage_card_estimation: specifies whether to use the storage layer for cardinality estimation. The parameter is of the VARCHAR type. The value can be TRUE or FALSE, which must be enclosed in single quotation marks (').
  • workarea_size_policy: the strategy for manually or automatically adjusting the SQL work area size. The parameter is of the VARCHAR type. The value can be 'MANUAL' or 'AUTO', which must be enclosed in single quotation marks (').
  • enable_rich_vector_format: specifies whether to enable vectorization 2.0 at the session level. The parameter is of the VARCHAR type. The value can be TRUE or FALSE, which must be enclosed in single quotation marks (').
  • spill_compression_codec: the compression algorithm used for operators to be temporarily materialized. The parameter is of the VARCHAR type. Valid values are NONE, LZ4, SNAPPY, ZLIB, and ZSTD, which represent different compression algorithms. The default value is NONE, which means no compression.
  • inlist_rewrite_threshold: the threshold of the number of constants for rewriting an IN LIST expression into a VALUES clause. The parameter is of the INT64 type. The value range is [1, 2147483647].
  • enable_constant_type_demotion: specifies whether to enable constant type demotion at the statement level. The parameter is of the BOOL type. The value can be TRUE or FALSE, which must be enclosed in single quotation marks ('). The default value is TRUE.
  • non_standard_comparison_level: specifies whether to use non-standard comparison rules for condition filtering. Constants are aligned with column types for comparison. This parameter affects only comparisons between character-type columns and integer constants. The parameter is of the STRING type. Valid values are NONE, EQUAL, and RANGE. The default value is NONE, which means no standardization.

Use the OPT_PARAM hint to specify the value of enable_in_range_optimization for enabling 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);

RESOURCE_GROUP hint

The RESOURCE_GROUP hint specifies the resource group that must be used for the statement.

The syntax of the RESOURCE_GROUP hint is as follows:

/*+ RESOURCE_GROUP ('resource_group_name') */

where resource_group_name indicates the name of the resource group.

An example of using the RESOURCE_GROUP hint is as follows:

obclient> SELECT /*+ RESOURCE_GROUP('big_group') */ * FROM t1;

In this example, if the big_group resource group does not exist, the default resource group is used.