Global hints: Other hints

Other hints under the global hint type allow developers and database administrators to exercise fine-grained control over the execution of SQL statements. The other supported types of hints are as follows:

APPEND hint

The APPEND hint specifies to enable the direct load feature for the INSERT INTO SELECT statement. For more information, see Use the INSERT INTO SELECT statement to directly load data.

Syntax

/*+ APPEND */

Example

INSERT /*+ append enable_parallel_dml parallel(16) */ INTO t2
SELECT * FROM t1;

CURSOR_SHARING_EXACT hint

The CURSOR_SHARING_EXACT hint specifies to prohibit parameterization at the query level.

OceanBase Database supports replacing literals in SQL statements with bound variables. This feature is controlled by the CURSOR_SHARING variable. Specifically, when cursor_sharing='exact', parameterization is not required. For more information, see cursor_sharing.

Syntax

/*+ CURSOR_SHARING_EXACT */

Example

In the following query example, the CURSOR_SHARING_EXACT hint is used to prohibit parameterization. After executing it with two sets of parameters four times, two query plans were generated for different parameters in the PLAN CACHE.

alter system flush plan cache global;
SELECT /*+ CURSOR_SHARING_EXACT */ * FROM t1 WHERE c1=5;
SELECT /*+ CURSOR_SHARING_EXACT */ * FROM t1 WHERE c1=5;
SELECT /*+ CURSOR_SHARING_EXACT */ * FROM t1 WHERE c1=6;
SELECT /*+ CURSOR_SHARING_EXACT */ * FROM t1 WHERE c1=6;

SELECT sql_id, plan_id, statement FROM oceanbase.gv$ob_plan_cache_plan_stat where query_sql like "SELECT /*+ CURSOR_SHARING_EXACT */ * FROM t1 WHERE c1=%";
+----------------------------------+---------+---------------------------------------------------------+
| sql_id                           | plan_id | statement                                               |
+----------------------------------+---------+---------------------------------------------------------+
| E024EB33213BF501D4CA7ABB81A195B5 |   13249 | SELECT /*+ CURSOR_SHARING_EXACT */ * FROM t1 WHERE c1=5 |
| E024EB33213BF501D4CA7ABB81A195B5 |   13250 | SELECT /*+ CURSOR_SHARING_EXACT */ * FROM t1 WHERE c1=6 |
+----------------------------------+---------+---------------------------------------------------------+

DIRECT hint

The DIRECT hint can be specified in LOAD DATA and INSERT statements to enable direct load, improving data import efficiency and performance.

Syntax

The syntax for 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. true indicates sorting is needed, and false indicates it is not.

• max_errors_allowed: specifies the maximum number of error rows allowed. If exceeded, the import process fails.

• load_mode: specifies the import mode, with the following options:

  • full: the default value, indicating full import.
  • inc: indicates incremental import, supporting INSERT and IGNORE semantics.
  • inc_replace: indicates incremental import without checking for duplicate primary keys, equivalent to REPLACE semantics for incremental import.

Examples

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) enables incremental direct load, sorts the data, and allows a maximum of 0 error 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) specifies a parallelism level of 4, and DIRECT(true, 0, full) enables full direct load, sorts the data, and allows a maximum of 0 error rows.

For more information, see Direct load.

DIRECT hint in INSERT INTO SELECT statements

To enable direct load in an INSERT INTO SELECT statement, you must use the enable_parallel_dml option. 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 a maximum of 0 error rows, enable_parallel_dml enables parallel DML, and PARALLEL(4) sets the parallelism level to 4.

Enable full direct load

-- Enable full direct load and parallel DML to insert data from old_table into 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 a maximum of 0 error rows. enable_parallel_dml enables parallel DML. PARALLEL(4) sets the parallelism level to 4.

For more information, see Use the INSERT INTO SELECT statement to enable direct load.

NO_DIRECT hint

The NO_DIRECT hint disables direct load in 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: specifies that direct load is not used for the current SQL statement. If this hint is specified in the SQL statement, the other direct load hints in the statement are ignored, and normal load is performed.

Examples

• Use NO_DIRECT in a LOAD DATA statement

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

• Use NO_DIRECT in an INSERT INTO SELECT statement

  INSERT /*+ NO_DIRECT */ INTO  table_name select_sentence
  

• Use NO_DIRECT in a CREATE TABLE AS SELECT statement

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

DYNAMIC_SAMPLING hint

The DYNAMIC_SAMPLING hint specifies whether to enable dynamic sampling for the query.

Syntax

/*+ DYNAMIC_SAMPLING ( 0 | 1 ) */

Parameters

Parameters of the DYNAMIC_SAMPLING hint are described as follows:

• When the parameter is 0, dynamic sampling is disabled.
• When the parameter is 1, dynamic sampling is enabled.

Example

The following query example shows how to enable dynamic sampling by using the DYNAMIC_SAMPLING hint.

SELECT /*+ dynamic_sampling(1) */ *
FROM t1 WHERE c1 LIKE "%abc%" AND c2 LIKE "%abc%";

USE_PX hint

The USE_PX hint forces the server to use the PX (parallel execution) mode when executing SQL statements. The PX mode allows SQL statements to be executed in a multithreaded manner, thereby improving query performance. Typically, the USE_PX hint is used in conjunction with the PARALLEL hint to specify the number of threads for parallel execution. By default, the system uses the USE_PX hint.

Syntax

The syntax for the USE_PX hint is as follows:

/*+ USE_PX */

Example

An example of the USE_PX hint is as follows:

SELECT /*+ USE_PX PARALLEL(4)*/ e.dept_id, sum(e.salary)
FROM emp e
WHERE e.dept_id = 1001 GROUP BY e.dept_id;

ENABLE_PARALLEL_DML hint

The ENABLE_PARALLEL_DML hint specifies to enable parallel DML for the current query. Its reverse hint is DISABLE_PARALLEL_DML, which disables parallel DML.

For more information, see Parallel DML.

Considerations

• When you use /*+ENABLE_PARALLEL_DML PARALLEL(n)*/, the system prioritizes distributed parallel DML (PDML). If the current environment does not support PDML, the system reverts to DAS concurrent writes as a performance optimization.

• If parallelism is enforced at the session level, its execution behavior will be consistent with the above.

• Even if the current environment does not support parallel DML (PDML), if the system parameter _enable_parallel_das_dml is set to true using the ALTER SYSTEM SET command (with a default value of false), the system will not enable parallel write operations for DAS, even if the /*+ENABLE_PARALLEL_DML PARALLEL(n)*/ hint is used.

• If _enable_parallel_das_dml is set to true using the ALTER SYSTEM SET command (with a default value of false) and parallelism is enforced at the session level, even if the current environment does not support PDML, the /*+ENABLE_PARALLEL_DML PARALLEL(n)*/ hint will not enable parallel write operations for DAS.

Syntax

/*+ ENABLE_PARALLEL_DML */

Example

insert /*+ enable_parallel_dml parallel(8) */ into t2 select * from t1;

DISABLE_PARALLEL_DML hint

The DISABLE_PARALLEL_DML hint specifies to disable parallel DML for the current query. Its reverse hint is ENABLE_PARALLEL_DML, which is used to enable parallel DML.

For more information, see Parallel DML.

Syntax

/*+ DISABLE_PARALLEL_DML */

Example

insert /*+ disable_parallel_dml parallel(8) */ into t2 select * from t1;

ENABLE_PARALLEL_DAS_DML hint

The ENABLE_PARALLEL_DAS_DML hint specifies to forcibly enable the concurrent write optimization of Distributed Data Access Service (DAS) for the current DML statement. Its reverse hint is DISABLE_PARALLEL_DAS_DML.

Considerations

• ENABLE_PARALLEL_DAS_DML must be used in combination with ENABLE_PARALLEL_DML.

• When the hint /*+ENABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(n)*/ is applied, the system treats DAS concurrent write as a mandatory option and executes the operation based on the specified degree of parallelism in PARALLEL(n).

• If you set the system parameter _enable_parallel_das_dml to true using the ALTER SYSTEM SET statement (with a default value of false), the hint /*+ENABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(n)*/ becomes invalid. In this case, the system will not perform parallel write operations for DAS.

Syntax

/*+ ENABLE_PARALLEL_DAS_DML */

Example

insert /*+ ENABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(10)*/
into t1 select * from t2;

DISABLE_PARALLEL_DAS_DML hint

The DISABLE_PARALLEL_DAS_DML hint specifies to forcibly disable the concurrent write optimization feature of Distributed Data Access Service (DAS) for the current DML statement. The reverse hint is ENABLE_PARALLEL_DAS_DML.

Considerations

• The DISABLE_PARALLEL_DAS_DML hint must be used in combination with the ENABLE_PARALLEL_DML hint.

• When you use the hint /*+DISABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(n)*/, the system disables DAS concurrent write optimization, even if other concurrent write optimization options such as PDML are enabled.

Syntax

/*+ DISABLE_PARALLEL_DAS_DML */

Example

insert /*+ DISABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(10)*/
into t1 select * from t2;

LOAD_BATCH_SIZE hint

The LOAD_BATCH_SIZE hint specifies the number of records to insert at a time in the LOAD DATA statement.

The batch_size parameter of the LOAD_BATCH_SIZE hint specifies the number of records to insert at a time. For more information about LOAD DATA, see Overview.

Syntax

/*+ LOAD_BATCH_SIZE ( batch_size ) */

Example

-- Import data using four parallel processes, append new data to the end of the table, and process 1,000 records per batch using the `LOAD_BATCH_SIZE` hint.
LOAD DATA /*+ PARALLEL(4) APPEND LOAD_BATCH_SIZE(1000) */
INFILE '/home/admin/test.csv' INTO TABLE t1;

LOG_LEVEL hint

The LOG_LEVEL hint specifies the log level for recording logs during the execution of the current query.

The log_level parameter in the LOG_LEVEL hint specifies the log level. Common log levels include ERROR, WARN, INFO, TRACE, and DEBUG.

For more information about log levels, see Log levels.

Syntax

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

Example

-- Use the LOG_LEVEL hint to specify the `TRACE` log level.
SELECT /*+ LOG_LEVEL(TRACE) */ *
FROM employees e
WHERE e.department_id = 1001;

MAX_CONCURRENT hint

The MAX_CONCURRENT hint specifies the maximum number of concurrent executions allowed for a query.

The intnum parameter in the MAX_CONCURRENT hint specifies the maximum number of concurrent executions allowed for the query. If the number of concurrent executions exceeds the specified maximum, an error is returned when the query is executed, indicating that the maximum number of concurrent executions has been reached. If intnum is set to 0, an error is always returned when the query is executed.

Note that the MAX_CONCURRENT hint cannot be directly used in a query. It can only be used to create an outline that includes the MAX_CONCURRENT hint for throttling specific SQL queries.

Syntax

/*+ MAX_CONCURRENT ( intnum ) */

Example

-- This query creates an outline named otl1 associated with the identifier 'EC102CB006383D732BC98797601D9B3B' and specifies that the maximum number of concurrent executions for the query is 10.
CREATE OUTLINE otl1 ON 'EC102CB006383D732BC98797601D9B3B'
USING HINT /*+ max_concurrent(10) */;

MONITOR hint

The MONITOR hint enables SQL Plan Monitor to capture and record the execution plans of queries. You can use the MONITOR hint to record the execution process of a query in SQL Plan Monitor.

OceanBase Database does not record the execution process of a query that does not use the MONITOR hint in SQL Plan Monitor.

For more information about SQL Plan Monitor, see the Real-time SQL plan monitor section in Real-time execution plan display.

Syntax

/*+ MONITOR */

Example

-- Enable SQL Plan Monitor by using the MONITOR hint.
SELECT /*+monitor*/ c1, SUM(distinct c2) FROM t1 GROUP BY c1;

Hint for query rewriting with materialized views

The control of query rewriting with materialized views includes the MV_REWRITE and NO_MV_REWRITE hints, and the priority of these hints is higher than the system variable query_rewrite_enabled.

MV_REWRITE hint

The syntax of the MV_REWRITE hint is as follows:

/*+ MV_REWRITE (@ queryblock [mv_name_list]) */

mv_name_list:
    mv_name [, mv_name ...]

When you use the MV_REWRITE hint alone, the system skips the rule and cost check for materialized view query rewriting and directly applies the available rewriting. If you specify one or more materialized views after the hint, in addition to skipping the rule and cost check, the system will only use the specified materialized views for rewriting and ignore all other materialized views that are not specified.

When you use the MV_REWRITE hint to specify materialized views, you cannot forcibly use materialized views that do not have the ENABLE QUERY REWRITE clause enabled for automatic rewriting, and you cannot forcibly use non-real-time materialized views when the system variable query_rewrite_integrity is set to enforced.

NO_MV_REWRITE hint

The syntax of the NO_MV_REWRITE hint is as follows:

/*+ NO_MV_REWRITE (@ queryblock) */

Disables materialized view query rewriting and allows you to specify a query block.

Example of using hints to control materialized view-based query rewriting

1. Create the base table tbl2.

   CREATE TABLE tbl2 (col1 INT, col2 INT);
   

2. Insert two rows into the base table tbl2.

   INSERT INTO tbl2 VALUES (1,2),(3,4);
   

   The return result is as follows:

   Query OK, 2 rows affected
   Records: 2  Duplicates: 0  Warnings: 0
   

3. Create the materialized view mv1_tbl2 and enable automatic rewriting for the materialized view.

   CREATE MATERIALIZED VIEW mv1_tbl2 NEVER REFRESH ENABLE QUERY REWRITE AS SELECT * FROM tbl2;
   

4. Create the materialized view mv2_tbl2 and enable automatic rewriting for the materialized view.

   CREATE MATERIALIZED VIEW mv2_tbl2 NEVER REFRESH ENABLE QUERY REWRITE AS SELECT * FROM tbl2 WHERE tbl2.col1 > 1;
   

5. Set the system variable query_rewrite_integrity to stale_tolerated.

   Note

   The priority of the MV_REWRITE and NO_MV_REWRITE hints is higher than that of the query_rewrite_enabled system variable. Therefore, you do not need to set the query_rewrite_enabled system variable. However, you must set the query_rewrite_integrity system variable to stale_tolerated to use materialized views for rewriting queries.

   SET query_rewrite_integrity = 'stale_tolerated';
   

6. Use the MV_REWRITE hint to attempt query rewriting using materialized views and skip the cost and rule check. The following two queries will both use the materialized view mv1_tbl2 for rewriting.

   • /*+mv_rewrite*/ will attempt to use the materialized views that meet the rewriting conditions for rewriting. Once a suitable materialized view is found, subsequent materialized views will be ignored, and the cost and rule check will be skipped.

     EXPLAIN SELECT /*+mv_rewrite*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 > 1 GROUP BY col1;
     

     The return result is as follows:

     +----------------------------------------------------------------------------------------------+
     | Query Plan                                                                                   |
     +----------------------------------------------------------------------------------------------+
     | =====================================================                                        |
     | |ID|OPERATOR         |NAME    |EST.ROWS|EST.TIME(us)|                                        |
     | -----------------------------------------------------                                        |
     | |0 |HASH GROUP BY    |        |1       |3           |                                        |
     | |1 |└─TABLE FULL SCAN|MV1_TBL2|1       |3           |                                        |
     | =====================================================                                        |
     | Outputs & filters:                                                                           |
     | -------------------------------------                                                        |
     |   0 - output([T_FUN_COUNT(*)], [MV1_TBL2.COL1]), filter(nil), rowset=16                      |
     |       group([MV1_TBL2.COL1]), agg_func([T_FUN_COUNT(*)])                                     |
     |   1 - output([MV1_TBL2.COL1]), filter([MV1_TBL2.COL1 > cast(1, NUMBER(-1, -85))]), rowset=16 |
     |       access([MV1_TBL2.COL1]), partitions(p0)                                                |
     |       is_index_back=false, is_global_index=false, filter_before_indexback[false],            |
     |       range_key([MV1_TBL2.__pk_increment]), range(MIN ; MAX)always true                      |
     +----------------------------------------------------------------------------------------------+
     14 rows in set
     

   • /*+mv_rewrite(mv1_tbl2)*/ will attempt to use mv2_tbl2 for rewriting and skip the cost and rule check.

     EXPLAIN SELECT /*+mv_rewrite(mv2_tbl2)*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 > 1 GROUP BY col1;
     

     The return result is as follows:

     +-------------------------------------------------------------------------+
     | Query Plan                                                              |
     +-------------------------------------------------------------------------+
     | =====================================================                   |
     | |ID|OPERATOR         |NAME    |EST.ROWS|EST.TIME(us)|                   |
     | -----------------------------------------------------                   |
     | |0 |HASH GROUP BY    |        |1       |3           |                   |
     | |1 |└─TABLE FULL SCAN|MV2_TBL2|1       |3           |                   |
     | =====================================================                   |
     | Outputs & filters:                                                      |
     | -------------------------------------                                   |
     |   0 - output([T_FUN_COUNT(*)], [MV2_TBL2.COL1]), filter(nil), rowset=16 |
     |       group([MV2_TBL2.COL1]), agg_func([T_FUN_COUNT(*)])                |
     |   1 - output([MV2_TBL2.COL1]), filter(nil), rowset=16                   |
     |       access([MV2_TBL2.COL1]), partitions(p0)                           |
     |       is_index_back=false, is_global_index=false,                       |
     |       range_key([MV2_TBL2.__pk_increment]), range(MIN ; MAX)always true |
     +-------------------------------------------------------------------------+
     14 rows in set
     

7. Although the query specifies to use mv2_tbl2 for rewriting, the WHERE condition in the query statement does not meet the requirements, so mv2_tbl2 cannot be used for rewriting queries. Therefore, query rewriting using materialized views will not be performed.

   EXPLAIN SELECT /*+mv_rewrite(mv2_tbl2)*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 < 1 GROUP BY col1;
   

   The return result is as follows:

   +--------------------------------------------------------------------------------------+
   | Query Plan                                                                           |
   +--------------------------------------------------------------------------------------+
   | =================================================                                    |
   | |ID|OPERATOR         |NAME|EST.ROWS|EST.TIME(us)|                                    |
   | -------------------------------------------------                                    |
   | |0 |HASH GROUP BY    |    |1       |3           |                                    |
   | |1 |└─TABLE FULL SCAN|TBL2|1       |3           |                                    |
   | =================================================                                    |
   | Outputs & filters:                                                                   |
   | -------------------------------------                                                |
   |   0 - output([T_FUN_COUNT(*)], [TBL2.COL1]), filter(nil), rowset=16                  |
   |       group([TBL2.COL1]), agg_func([T_FUN_COUNT(*)])                                 |
   |   1 - output([TBL2.COL1]), filter([TBL2.COL1 < cast(1, NUMBER(-1, -85))]), rowset=16 |
   |       access([TBL2.COL1]), partitions(p0)                                            |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false],    |
   |       range_key([TBL2.__pk_increment]), range(MIN ; MAX)always true                  |
   +--------------------------------------------------------------------------------------+
   14 rows in set
   

8. Use the /*+ no_mv_rewrite*/ hint to prevent query rewriting using materialized views.

   EXPLAIN SELECT /*+no_mv_rewrite*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 > 1 GROUP BY col1;
   

   The return result is as follows:

   +--------------------------------------------------------------------------------------+
   | Query Plan                                                                           |
   +--------------------------------------------------------------------------------------+
   | =================================================                                    |
   | |ID|OPERATOR         |NAME|EST.ROWS|EST.TIME(us)|                                    |
   | -------------------------------------------------                                    |
   | |0 |HASH GROUP BY    |    |1       |3           |                                    |
   | |1 |└─TABLE FULL SCAN|TBL2|1       |3           |                                    |
   | =================================================                                    |
   | Outputs & filters:                                                                   |
   | -------------------------------------                                                |
   |   0 - output([T_FUN_COUNT(*)], [TBL2.COL1]), filter(nil), rowset=16                  |
   |       group([TBL2.COL1]), agg_func([T_FUN_COUNT(*)])                                 |
   |   1 - output([TBL2.COL1]), filter([TBL2.COL1 > cast(1, NUMBER(-1, -85))]), rowset=16 |
   |       access([TBL2.COL1]), partitions(p0)                                            |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false],    |
   |       range_key([TBL2.__pk_increment]), range(MIN ; MAX)always true                  |
   +--------------------------------------------------------------------------------------+
   14 rows in set
   

NO_PARALLEL hint

The NO_PARALLEL hint specifies to disable query parallelism (setting the degree of parallelism to 1). Its reverse hint is the PARALLEL hint.

The NO_PARALLEL hint is equivalent to /*+ parallel(1)*/.

Syntax

/*+ NO_PARALLEL */

Example

The following query example shows how to disable query parallelism by using the NO_PARALLEL hint.

SELECT /*+ no_parallel */ c1, sum(distinct c2) FROM t1 GROUP BY c1;

NO_QUERY_TRANSFORMATION hint

The NO_QUERY_TRANSFORMATION hint prevents any query rewriting for the current query.

Note that this is different from the NO_REWRITE hint in query block hints. Using NO_QUERY_TRANSFORMATION prevents the use of some rewrite features enabled by query block hints.

For more information about query rewriting, see Overview of query rewriting.

Syntax

/*+ NO_QUERY_TRANSFORMATION */

Example

The following example shows a query that uses the NO_QUERY_TRANSFORMATION hint to prevent any query rewriting.

SELECT /*+ NO_QUERY_TRANSFORMATION */ *
FROM (SELECT * FROM t1) v WHERE v.c1 = 3;

OPT_PARAM hint

The OPT_PARAM hint specifies to update some optimizer-related parameters or system variables at the query level.

Syntax

/*+ 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 is valid for the following parameters:
• rowsets_enabled: specifies whether to enable vectorization. The data type is VARCHAR, and the value must be one of 'TRUE' or 'FALSE'. The value must be enclosed in single quotation marks ('').
• rowsets_max_rows: specifies the maximum number of rows to be returned in a batch. The data type is INT, and the value must be in the range of [0, 65535]. The value cannot be enclosed in single quotation marks ('').
• enable_newsort: specifies whether to allow the newsort optimization. The data type is VARCHAR, and the value must be one of 'TRUE' or 'FALSE'. The value must be enclosed in single quotation marks ('').
• use_part_sort_mgb: specifies whether to allow the part sort merge group by optimization. The data type is VARCHAR, and the value must be one of 'TRUE' or 'FALSE'. The value must be enclosed in single quotation marks ('').
• enable_in_range_optimization: specifies whether to allow the in optimization. The data type is VARCHAR, and the value must be one of 'TRUE' or 'FALSE'. The value must be enclosed in single quotation marks ('').
• xsolapi_generate_with_clause: specifies whether to allow the CTE rewriting. The data type is VARCHAR, and the value must be one of 'TRUE' or 'FALSE'. The value must be enclosed in single quotation marks ('').
• preserve_order_for_pagination: specifies whether to add an ORDER BY clause for the pagination query to maintain the order. The data type is VARCHAR, and the value must be one of 'TRUE' or 'FALSE'. The value must be enclosed in single quotation marks ('').
• storage_card_estimation: specifies whether to use the storage layer to estimate rows. The data type is VARCHAR, and the value must be one of 'TRUE' or 'FALSE'. The value must be enclosed in single quotation marks ('').
• workarea_size_policy: specifies the strategy for manually or automatically adjusting the SQL work area size. The data type is VARCHAR, and the value must be either 'MANUAL' or 'AUTO'. The value must be enclosed in single quotation marks ('').
• enable_rich_vector_format: specifies whether to enable vectorization 2.0 (a session-level parameter). The data type is VARCHAR, and the value must be one of 'TRUE' or 'FALSE'. The value must be enclosed in single quotation marks ('').
• spill_compression_codec: specifies the compression algorithm for the spill operator. The data type is VARCHAR, and the value can be NONE, LZ4, SNAPPY, ZLIB, or ZSTD, representing different compression algorithms. The default value is NONE, indicating no compression.
• inlist_rewrite_threshold: specifies the threshold for the number of constants that can trigger the rewrite of an INLIST clause into a VALUES statement. The data type is INT64, and the value must be in the range of [1, 2147483647].

Example

In the following query, the OPT_PARAM hint is used 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);

PARALLEL hint

When used as a global hint, the PARALLEL hint specifies the degree of parallelism or the parallel execution strategy for the current query.

In addition to being used as a global hint, the PARALLEL hint can also be used as a query block hint to specify the degree of parallelism for a table. For more information, see Access path hints under "Parallel" hint.

Syntax

/*+ PARALLEL ( AUTO | MANUAL | parallel_degree) */

Parameters

• AUTO or MANUAL: When AUTO or MANUAL is used as the parameter in the PARALLEL hint, it specifies the parallel execution strategy. For more information, see Parallel execution modes and priorities.

• parallel_degree: When parallel_degree is used as the parameter in the PARALLEL hint, it directly specifies the degree of parallelism.

Example

The following query example shows how to use the PARALLEL hint to specify a degree of parallelism (DOP) of 8 or enable auto DOP.

SELECT /*+parallel(8)*/ c1, SUM(distinct c2) FROM t1 GROUP BY c1;

SELECT /*+parallel(auto)*/ c1, SUM(distinct c2) FROM t1 GROUP BY c1;

Note: In some scenarios, parallel execution is not supported. Even if parallel execution is specified using the PARALLEL hint, the execution plan may disable parallel execution.

QUERY_TIMEOUT hint

The QUERY_TIMEOUT hint specifies the execution timeout for the current query.

Syntax

/*+ QUERY_TIMEOUT ( time_usec ) */

Parameters

• time_usec: specifies the query timeout in microseconds.

Example

-- Specify a query timeout of 1 second. If the query is not completed within the specified timeout period, a timeout error will be returned.
SELECT /*+ QUERY_TIMEOUT(1000000) */ *
FROM employees e
WHERE e.department_id = 1001;

READ_CONSISTENCY hint

The READ_CONSISTENCY hint specifies the read consistency level for the current query.

Syntax

/*+ READ_CONSISTENCY(WEAK[STRONG]) */

Parameters

• WEAK: specifies to use weak consistency, which enables weak reads.

• STRONG: specifies to use strong consistency, which disables weak reads.

Example

-- Use the READ_CONSISTENCY hint and set it to WEAK to specify that weak reads are enabled for the query.
SELECT /*+ READ_CONSISTENCY(WEAK) */ *
FROM employees
WHERE employees.department_id = 1001;

RESOURCE_GROUP hint

The RESOURCE_GROUP hint specifies the resource group for the statement.

Syntax

The syntax of the RESOURCE_GROUP hint is as follows:

/*+ RESOURCE_GROUP ('resource_group_name') */

Parameters

• resource_group_name: the name of the resource group to be specified.

Example

Here is an example of using the RESOURCE_GROUP hint:

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

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

STAT hint

The STAT hint specifies to track the output of specific operators in the query plan.

After adding the STAT hint, the query plan will include a MONITORING DUMP operator. This operator outputs all data from its child operator and prints information such as operator execution time and output rows to the observer log upon completion.

Syntax

/*+ STAT(TRACING_NUM_LIST) */

Parameters

• TRACING_NUM_LIST: the ID list of operators to be traced.

Examples

-- The execution plan changes before and after adding the `/*+ STAT(0, 2) */` hint.
explain basic
SELECT /*+leading(t1) use_hash(t2)*/ * FROM t1, t2 WHERE t1.c1 = t2.c1;

Query Plan
===========================
|ID|OPERATOR         |NAME|
---------------------------
|0 |HASH JOIN        |    |
|1 |├─TABLE FULL SCAN|T1  |
|2 |└─TABLE FULL SCAN|T2  |
===========================

explain basic
SELECT /*+leading(t1) use_hash(t2) stat(0, 2)*/ *
FROM t1, t2 where t1.c1 = t2.c1;
Query Plan
===============================
|ID|OPERATOR             |NAME|
-------------------------------
|0 |MONITORING DUMP      |    |
|1 |└─HASH JOIN          |    |
|2 |  ├─TABLE FULL SCAN  |T1  |
|3 |  └─MONITORING DUMP  |    |
|4 |    └─TABLE FULL SCAN|T2  |
===============================

TRANS_PARAM hint

The TRANS_PARAM hint specifies transaction-related parameters at the query level.

Syntax

/*+ TRANS_PARAM ['FORCE_EARLY_LOCK_FREE' , 'TRUE'] */

Parameters

The only supported parameter is FORCE_EARLY_LOCK_FREE at the transaction level.

• FORCE_EARLY_LOCK_FREE: the value TRUE indicates that the feature is supported, and the value FALSE indicates that it is not.

Note: The parameter name and value must be enclosed in single quotation marks (' '). When the parameter value is a numeric type, quotation marks are not required.

Examples

-- Use the TRANS_PARAM hint and set the 'FORCE_EARLY_LOCK_FREE' parameter to 'TRUE' to specify transaction-level early lock freeing.
SELECT /*+ TRANS_PARAM('FORCE_EARLY_LOCK_FREE' 'TRUE') */ *
FROM employees e
WHERE e.department_id = 1001;

TRACING hint

The TRACING hint specifies to trace the output of specific operators in the query plan.

The usage of the TRACING hint, the plan after adding the hint, and the usage of the STAT hint are the same.

The difference from using the STAT hint is that when using the TRACING hint, the MONITORING DUMP operator prints all its output data to the observer log.

Syntax

/*+ TRACING(TRACING_NUM_LIST)*/

Parameters

• TRACING_NUM_LIST: the ID of the operator to be traced.

Example

-- Use the TRACING hint and set the level to 1 to enable tracing for the current query.
SELECT /*+ TRACING(1) */ *
FROM employees e
WHERE e.department_id = 1001;

USE_PLAN_CACHE hint

The USE_PLAN_CACHE hint specifies the plan cache usage strategy for the current query. For more information about plan cache, see Execution plan cache.

Syntax

/*+ USE_PLAN_CACHE ( NONE | DEFAULT ) */

Parameter

• NONE: specifies that the query does not use the plan cache.

• DEFAULT: specifies that the plan cache usage strategy for the current query is controlled by the system variable ob_enable_plan_cache.

Example

-- Specifies that the query does not use the plan cache when the `NONE` parameter is used, and specifies that the plan cache usage strategy for the current query is controlled by the system variable `ob_enable_plan_cache` when the `DEFAULT` parameter is used.
SELECT /*+ USE_PLAN_CACHE(NONE) */ *
FROM employees e
WHERE e.department_id = 1001;

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