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

DIRECT hint

You can use the DIRECT hint in the LOAD DATA or INSERT statement to enable direct load to improve 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 data to be imported. true specifies to sort the data and false specifies not to sort the data.

• max_errors_allowed: the maximum number of erroneous rows allowed. If the specified value is exceeded, the import fails.

• load_mode: the import mode. Valid values:

  • full: specifies to perform full direct load. This is the default value.
  • inc: specifies to perform incremental direct load. The INSERT and IGNORE semantics are supported.
  • inc_replace: specifies to perform incremental direct load without checking whether the primary key column contains duplicate values, which is equivalent to incremental direct load by using the REPLACE semantics.

Examples

Use the DIRECT hint in LOAD DATA

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) specifies to enable incremental direct load, sort the data to be imported, and allow 0 errorneous rows.

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) sets the degree of parallelism (DOP) to 4. DIRECT(true, 0, full) specifies to enable full direct load, sort the data to be imported, and allow 0 errorneous rows.

For more information, see Import data in direct load mode by using the LOAD DATA statement.

Use the DIRECT hint in INSERT INTO SELECT

To use the INSERT INTO SELECT statement for direct load, use the DIRECT hint in combination with the enable_parallel_dml hint. The syntax is as follows: /*+ direct(bool, int, load_mode)} enable_parallel_dml PARALLEL(N) */.

Enable incremental direct load

-- Enable incremental direct load and parallel DML (PDML) to import data from the `old_table` table to the `new_table` 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') specifies to enable incremental direct load, sort the data to be imported, and allow 0 errorneous rows. enable_parallel_dml specifies to enable PDML. PARALLEL(4) sets the DOP to 4.

Enable full direct load

-- Enable full direct load and PDML to insert data from the `old_table` table to the `new_table` 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') specifies to enable full direct load, sort the data to be imported, and allow 0 errorneous rows. enable_parallel_dml specifies to enable PDML. PARALLEL(4) sets the DOP to 4.

For more information, see Import data through direct load mode by using the INSERT INTO SELECT statement.

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:
• rowsets_enabled: specifies whether to enable vectorization. The data type is VARCHAR. The value can be 'TRUE' or 'FALSE' and must be enclosed with single quotation marks ('').
• rowsets_max_rows: the number of rows returned in a batch, namely, the batch size. The data type is INT. The value range is [0, 65535]. The value cannot be enclosed with single quotation marks ('').
• enable_newsort: specifies whether to enable newsort-based optimization. The data type is VARCHAR. The value can be 'TRUE' or 'FALSE' and must be enclosed with single quotation marks ('').
• use_part_sort_mgb: specifies whether to enable the part sort merge group by feature. The data type is VARCHAR. The value can be 'TRUE' or 'FALSE' and must be enclosed with single quotation marks ('').
• enable_in_range_optimization: specifies whether to enable IN optimization. The data type is VARCHAR. The value can be 'TRUE' or 'FALSE' and must be enclosed with single quotation marks ('').
• xsolapi_generate_with_clause: specifies whether to enable common table expression (CTE) extraction for query rewrite. The data type is VARCHAR. The value can be 'TRUE' or 'FALSE' and must be enclosed with single quotation marks ('').
• preserve_order_for_pagination: specifies whether to add the ORDER BY clause for order preservation for pagination queries. The data type is VARCHAR. The value can be 'TRUE' or 'FALSE' and must be enclosed with single quotation marks ('').
• storage_card_estimation: specifies whether to enable row estimation based on the storage layer. The data type is VARCHAR. The value can be 'TRUE' or 'FALSE' and must be enclosed with single quotation marks ('').
• workarea_size_policy: specifies whether the size of an SQL workarea is manually or automatically adjusted. The data type is VARCHAR. The value 'MANUAL' indicates manual adjustment and 'AUTO' indicates automatic adjustment. The value must be enclosed with single quotation marks ('').
• enable_rich_vector_format: specifies whether to enable Vectorization 2.0. This is a session-level parameter. The data type is VARCHAR. The value can be 'TRUE' or 'FALSE' and must be enclosed with single quotation marks ('').
• spill_compression_codec: the compression algorithm for operators whose temporary results need to be materialized. The data type is VARCHAR. Valid values are NONE, LZ4, SNAPPY, ZLIB, and ZSTD, which respectively indicate different compression algorithms. The default value is NONE, which specifies not to compress the temporary results of operators.
• inlist_rewrite_threshold: the maximum number of constants for triggering the rewrite of an inlist into a values statement. The data type is INT64. The value range is [1, 2147483647].

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

RESOURCE_GROUP hint

The RESOURCE_GROUP hint forcibly specifies a resource group for a statement.

The syntax of the RESOURCE_GROUP hint is as follows:

/*+ RESOURCE_GROUP ('resource_group_name') */

resource_group_name specifies the name of the resource group.

Here is an example of the RESOURCE_GROUP hint:

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.