Other hints under global hints

Other hints under global hints allow developers and database administrators to exert fine-grained control over the execution of SQL statements. The following table lists other supported hint types:

APPEND Hint

The APPEND hint enables direct load for an INSERT INTO SELECT statement. For more information, see Use the INSERT INTO SELECT statement for direct data load.

Syntax

/*+ APPEND */

Examples

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

CURSOR_SHARING_EXACT Hint

The CURSOR_SHARING_EXACT hint is used to specify that parameterization is prohibited at the query level.

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

Syntax

/*+ CURSOR_SHARING_EXACT */

Examples

In the query example below, the CURSOR_SHARING_EXACT hint is used to disable parameterization. After executing with two sets of parameters four times, PLAN CACHE generates two query plans for different parameters.

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 used in LOAD DATA and INSERT statements to enable direct load, thereby 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) */)*/

Parameter description

• need_sort: Indicates whether the imported data needs to be sorted. true indicates sorting is required, and false indicates sorting is not required.

• max_errors_allowed: The maximum number of erroneous rows allowed. If the number of erroneous rows exceeds this limit, the import will fail.

• load_mode: Specifies the import mode. Valid values:

  • full: the default value, indicating a full import.
  • inc: incremental import. Supports the INSERT and IGNORE semantics.
  • inc_replace: Indicates an incremental import, but does not check for duplicate primary keys. This is equivalent to an incremental import with the REPLACE semantics.

Examples

Example of 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) indicates to enable incremental direct load, perform sorting, and allow up to 0 rows of errors.

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 degree of parallelism of 4. DIRECT(true, 0, full) indicates to enable full direct load, perform sorting, and allow up to 0 rows of errors.

For more information, see Import data through direct load.

Example of the DIRECT hint in INSERT INTO SELECT

In the INSERT INTO SELECT statement, to enable direct load, 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 and sorts the data, allowing up to 0 rows of errors. enable_parallel_dml enables parallel DML. PARALLEL(4) sets the degree of parallelism 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 up to 0 rows of errors. enable_parallel_dml enables parallel DML. PARALLEL(4) sets the degree of parallelism to 4.

For more information, see Import data via INSERT INTO SELECT statement.

NO_DIRECT Hint

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

Syntax

The syntax of the NO_DIRECT hint is as follows:

/*+ NO_DIRECT */

Parameters

NO_DIRECT: forces a single SQL statement to not use direct load. If an SQL statement contains this hint, it ignores other direct load hints and executes a regular import.

Examples

• Use NO_DIRECT in the LOAD DATA statement

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

• Use the NO_DIRECT hint in an INSERT INTO SELECT statement

  INSERT /*+ NO_DIRECT */ INTO  table_name select_sentence

• Use the NO_DIRECT hint 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 a query.

Syntax

/*+ DYNAMIC_SAMPLING ( 0 | 1 ) */

Parameter description

The parameters in the DYNAMIC_SAMPLING hint are defined as follows:

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

Examples

The following 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 parallel execution (PX) mode when executing SQL statements. PX mode allows SQL statements to be executed using multiple threads, thereby improving query performance. Typically, the USE_PX hint is used together 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 */

Examples

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 enables parallel DML for the current query. Its counterpart, the DISABLE_PARALLEL_DML hint, is used to disable parallel DML.

For more information, see Parallel DML.

Considerations

• When /*+ENABLE_PARALLEL_DML PARALLEL(n)*/ is used, the system prioritizes distributed parallel DML (PDML). If the current environment does not support PDML, the system falls back to DAS concurrent write as a performance optimization.

• If a forced parallelism is set 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 (its default value is false), the system will not enable DAS (Distributed Active Storage) parallel write operations, 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 (default is false) and the session-level parallelism is already set to a fixed value, then even if the current environment does not support PDML, using the /*+ENABLE_PARALLEL_DML PARALLEL(n)*/ hint will not enable DAS parallel write.

Syntax

/*+ ENABLE_PARALLEL_DML */

Examples

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 */

Examples

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

ENABLE_PARALLEL_DAS_DML Hint

The ENABLE_PARALLEL_DAS_DML hint is used to explicitly enable concurrent write optimization for the Distributed Data Access Service (DAS) for the current DML statement. Its negative hint is DISABLE_PARALLEL_DAS_DML.

Considerations

• ENABLE_PARALLEL_DAS_DML must be used together with ENABLE_PARALLEL_DML.

• When the /*+ENABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(n)*/ hint is used, the system will treat DAS concurrent write as a forced option and execute operations based on the parallelism specified by PARALLEL(n).

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

Syntax

/*+ ENABLE_PARALLEL_DAS_DML */

Examples

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 is used to explicitly disable concurrent write optimization using the Distributed Data Access Service (DAS) for the current DML statement. Its counterpart hint is ENABLE_PARALLEL_DAS_DML.

Considerations

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

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

Syntax

/*+ DISABLE_PARALLEL_DAS_DML */

Examples

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 size of the batch of records to be inserted in each LOAD DATA statement.

In the LOAD_BATCH_SIZE hint, the batch_size parameter specifies the size of the batch of records to be inserted in each LOAD DATA statement. For more information about LOAD DATA, see LOAD DATA statement.

Syntax

/*+ LOAD_BATCH_SIZE ( batch_size ) */

Examples

-- Use four parallel processes to import data, appending new data to the end of the table, and set each batch to process 1000 records 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 the current query.

In the LOG_LEVEL hint, the log_level parameter 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['] ) */

Examples

-- 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 queries allowed.

In the MAX_CONCURRENT hint, the intnum parameter specifies the maximum number of concurrent queries allowed. If the number of concurrent queries exceeds the allowed maximum, an error is returned when the query is executed. If intnum is set to 0, an error is always returned when the query is executed.

Note that the MAX_CONCURRENT hint cannot be used directly in queries. Instead, you must create an outline containing only the MAX_CONCURRENT hint to throttle query execution for specific SQL IDs.

Syntax

/*+ MAX_CONCURRENT ( intnum ) */

Examples

-- This query creates an outline named otl1 associated with the identifier 'EC102CB006383D732BC98797601D9B3B' and specifies a maximum of 10 concurrent executions for the corresponding query.
CREATE OUTLINE otl1 ON 'EC102CB006383D732BC98797601D9B3B'

USING HINT /*+ max_concurrent(10) */;

MONITOR Hint

The MONITOR hint is used to enable capturing query execution SQL Plan Monitor records. You can use the MONITOR hint to record its execution process in SQL Plan Monitor.

For queries that do not have parallel execution enabled, OceanBase Database does not record their execution process in SQL Plan Monitor.

For more information about SQL Plan Monitor, see the Real-time SQL Plan Monitor section in Display real-time execution plans.

Syntax

/*+ MONITOR */

Examples

-- Use the `MONITOR` hint to enable the SQL Plan Monitor.
SELECT /*+monitor*/ c1, SUM(distinct c2) FROM t1 GROUP BY c1;

Materialized View Query Rewrite Hint

Materialized view query rewrite control includes two hints: MV_REWRITE and NO_MV_REWRITE. These two hints have a higher priority than the system variable query_rewrite_enabled.

MV_REWRITE Hint

The syntax of MV_REWRITE is as follows:

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

mv_name_list:
mv_name [, mv_name ...]

When the MV_REWRITE hint is used alone, it skips the rule and cost checks for materialized view query rewrite and directly applies any available rewrites. When one or more materialized views are specified after the hint, in addition to skipping the rule and cost checks, the query rewrite will only attempt to use the specified materialized views, ignoring all unspecified ones.

When using the MV_REWRITE hint to specify a materialized view, you cannot force the use of a materialized view that does not have the ENABLE QUERY REWRITE clause (which enables automatic query rewrite for the current materialized view), nor can you force the use of a non-real-time materialized view when the system variable query_rewrite_integrity is set to enforced.

NO_MV_REWRITE Hint

The syntax for NO_MV_REWRITE is as follows:

/*+ NO_MV_REWRITE (@ queryblock) */

Materialized view query rewrite is prohibited, and the query block can be specified.

Examples of Using the Hint to Control Materialized View Query Rewrite

1. Create the base table tbl2.

   
   CREATE TABLE tbl2 (col1 INT, col2 INT);
   

2. Insert two rows into the tbl2 table.

   
   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 rewrite for it.

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

4. Create the materialized view mv2_tbl2 and enable query rewriting on it.

   
   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 MV_REWRITE and NO_MV_REWRITE hints take precedence over the system variable query_rewrite_enabled, so you do not need to set query_rewrite_enabled. However, you must set query_rewrite_integrity to stale_tolerated to use non-real-time materialized views for rewriting.

   
   SET query_rewrite_integrity = 'stale_tolerated';
   

6. Use the MV_REWRITE hint to attempt rewriting with a materialized view and skip the cost/rule check for the rewrite. Both of the following queries will use the materialized view mv1_tbl2 for rewriting.

   • /*+mv_rewrite*/ will attempt to use a materialized view that meets the rewrite conditions for rewriting. Once a suitable materialized view is found, subsequent materialized views will be ignored, and the cost and rule checks 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 the mv2_tbl2 materialized view for rewriting and skip the cost and rule checks.

     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 query rewrite, since the WHERE condition of the query statement does not meet the requirements, mv2_tbl2 cannot be used for query rewrite. Therefore, this query will not undergo materialized view query rewrite.

   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 materialized view query rewrite.

   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 (that is, set the degree of parallelism to 1). Its counterpart hint is the PARALLEL hint.

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

Syntax

/*+ NO_PARALLEL */

Examples

In the query example below, the NO_PARALLEL hint is used to disable query parallelism.

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

NO_QUERY_TRANSFORMATION Hint

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

Note that, unlike the NO_REWRITE hint in query block hints, using the NO_QUERY_TRANSFORMATION hint does not disable certain rewrites enabled by query block hints.

For more information about query rewriting, see Query rewrite overview.

Syntax

/*+ NO_QUERY_TRANSFORMATION */

Examples

In the query example below, the NO_QUERY_TRANSFORMATION hint is used to prohibit 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/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 variable to be specified.

The OPT_PARAM hint takes effect for the following parameters:

• rowsets_enabled: Enables/disables vectorization. Data type: VARCHAR. Value range: 'TRUE' and 'FALSE'. Values must be enclosed in single quotes ('').

• rowsets_max_rows: Sets the batch return row count batch_size. Data type: INT. Value range: [0, 65535]. Values cannot be enclosed in single quotes ('').

• enable_newsort: Enables/disables the newsort optimization in queries. Data type: VARCHAR. Value range: 'TRUE' and 'FALSE'. Values must be enclosed in single quotes ('').

• use_part_sort_mgb: Enables/disables part sort merge group by in queries. Data type: VARCHAR. Value range: 'TRUE' and 'FALSE'. Values must be enclosed in single quotes ('').

• enable_in_range_optimization: Enables/disables the in-range optimization in queries. Data type: VARCHAR. Value range: 'TRUE' and 'FALSE'. Values must be enclosed in single quotes ('').

• xsolapi_generate_with_clause: Enables/disables CTE extraction rewrite in queries. Data type: VARCHAR. Value range: 'TRUE' and 'FALSE'. Values must be enclosed in single quotes ('').

• preserve_order_for_pagination: specifies whether to add an order by clause to a pagination query to preserve order or to prohibit adding the order by clause. The data type is VARCHAR, and the value can be 'TRUE' or 'FALSE'. Enclose the value in single quotation marks (' ').

• storage_card_estimation: specifies whether to use the row estimation based on the storage layer. The data type is VARCHAR, and the value can be 'TRUE' or 'FALSE'. Enclose the value in single quotation marks (' ').

• workarea_size_policy: specifies the strategy for manually or automatically adjusting the size of the SQL work area. The data type is VARCHAR, and the value can be 'MANUAL' for manual adjustment or 'AUTO' for automatic adjustment. Enclose the value in single quotation marks (' ').

• enable_rich_vector_format: specifies whether to enable or disable vectorization 2.0 (session-level parameter). The data type is VARCHAR, and the value can be 'TRUE' or 'FALSE'. Enclose the value in single quotation marks (' ').

• spill_compression_codec: specifies the compression algorithm for the operators that need to be temporarily materialized. 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 maximum number of constants that can trigger the rewrite of an inlist clause into a values statement. The data type is INT64, and the value ranges from 1 to 2147483647.

• orc_filter_pushdown_level: specifies the level at which to push down filter conditions for ORC external tables. The value of parameter_value can be:

  Note

  The OPT_PARAM Hint in OceanBase Database supports the orc_filter_pushdown_level parameter starting from V4.4.0.

  • 0: disables filter condition pushing down.
  • 1: pushes down filter conditions to the file level.
  • 2: pushes down filter conditions to the stripe level.
  • 3: pushes down filter conditions to the row index level.
  • 4: pushes down filter conditions to the encoding level.

• parquet_filter_pushdown_level: specifies the level at which to push down filter conditions for Parquet external tables. The value of parameter_value can be:

  Note

  The OPT_PARAM Hint in OceanBase Database supports the parquet_filter_pushdown_level parameter starting from V4.4.0.

  • 0: disables filter condition pushing down.
  • 1: pushes down filter conditions to the file level.
  • 2: pushes down filter conditions to the RowGroup level.
  • 3: pushes down filter conditions to the page level.
  • 4: pushes down filter conditions to the encoding level.

Examples

For the query below, use the OPT_PARAM hint to specify the value of enable_in_range_optimization to enable IN-range 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);

PARALLEL Hint

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

In addition to being a global hint, the PARALLEL hint can also be used as a query block hint to specify the table-level parallelism. For more information, see the related content about the Parallel hint in Access Path Hints.

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 parallelism strategy. For more information, see Parallelism strategies and their priorities.

• parallel_degree: When using the PARALLEL hint, you can directly specify the degree of parallelism as a parameter.

Examples

In the query example below, the PARALLEL hint is used to specify a degree of parallelism (DOP) of 8 or to 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: Parallel execution is not supported in some scenarios. Even if you specify to enable parallelism using the PARALLEL clause, the execution plan may still disable it.

QUERY_TIMEOUT Hint

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

Syntax

/*+ QUERY_TIMEOUT ( time_usec ) */

Parameters

• time_usec: specifies the query timeout period, in microseconds.

Examples

-- Specifies the query timeout period as 1 second. If the query is not completed within the specified timeout period, a timeout error is 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: enables weak consistency, which allows weak reads.

• STRONG: enables strong consistency, which means weak reads are disabled.

Examples

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

Weak reads are supported when the isolation level is set to Repeatable Read or Serializable. Example:

obclient> SET transaction read only;
Query OK, 0 rows affected (0.001 sec)
obclient> SELECT /*+ read_consistency(weak)*/ * FROM t WHERE a=2;
+------+------+
| ID   | A    |
+------+------+
| NULL |    2 |
+------+------+
1 row in set (0.001 sec)

RESOURCE_GROUP hint

The RESOURCE_GROUP hint forcibly specifies the resource group to be used by 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.

Examples

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 the operators whose outputs are to be traced in the query plan.

After the STAT hint is added, the query plan includes a MONITORING DUMP operator that directly outputs all data from the sort operator and prints the operator's execution time, output rows, and other information in the observer log after execution.

Syntax

/*+ STAT(TRACING_NUM_LIST) */

Parameters

• TRACING_NUM_LIST: specifies the IDs of the operators to be traced.

Examples

-- Execution plan changes before and after adding the hint `/*+ STAT(0, 2) */`
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 is used to specify transaction-related parameters at the query level.

Syntax

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

Parameters

Currently, the only supported parameter is the transaction-level pre-row lock release parameter FORCE_EARLY_LOCK_FREE.

• FORCE_EARLY_LOCK_FREE: When the value is TRUE, early lock release is supported; when the value is FALSE, it is not supported.

Note: Parameter names and values must be enclosed in single quotes (' '). If a parameter value is numeric, quotes are optional.

Examples

-- Uses the TRANS_PARAM hint and sets the parameter 'FORCE_EARLY_LOCK_FREE' to 'TRUE' to enable early row lock release at the transaction level.
SELECT /*+ TRANS_PARAM('FORCE_EARLY_LOCK_FREE' 'TRUE') */ *

FROM employees e

WHERE e.department_id = 1001;

TRACING Hint

The TRACING hint specifies the operators whose outputs are to be traced 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 between using the TRACING hint and the STAT hint is that the MONITORING DUMP operator prints all of its output data in the observer log when the TRACING hint is used.

Syntax

/*+ TRACING(TRACING_NUM_LIST)*/

Parameters

• TRACING_NUM_LIST: specifies the IDs of the operators to be traced.

Examples

-- Uses the TRACING hint and sets 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 is used to specify the plan cache strategy for the current query. For more information about plan caching, see Execution plan caching.

Syntax

/*+ USE_PLAN_CACHE ( NONE | DEFAULT ) */

Parameters

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

• DEFAULT: Specifies that the current query uses the plan cache strategy controlled by the system variable ob_enable_plan_cache.

Examples

-- When the parameter `NONE` is used, it specifies that the query does not use the plan cache. When the parameter `DEFAULT` is used, it specifies that the current query uses the system variable `ob_enable_plan_cache` to control the plan cache strategy.
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;

DISABLE_TRIGGER Hint

The DISABLE_TRIGGER hint is used to temporarily disable triggers during the execution of the current DML statement. It applies to INSERT, UPDATE, DELETE, and DML operations based on views (including INSTEAD OF triggers).

Syntax

You can use DISABLE_TRIGGER alone or follow it with a pair of parentheses, listing the trigger names (separated by commas) inside. Use parentheses, not brackets.

/*+ disable_trigger */
/*+ disable_trigger ( trigger_name [, trigger_name ... ] ) */

Parameters

trigger_name: an optional parameter specifying the name of the trigger to temporarily disable. You can specify one or more trigger names, separated by commas. If omitted, all triggers involved in the query are disabled by default.

Examples

-- Disable only the trigger named test_trigger1.
INSERT /*+ DISABLE_TRIGGER (test_trigger1) */ INTO test VALUES (2);

-- Disable all triggers related to this DML statement.
INSERT /*+ DISABLE_TRIGGER */ INTO test VALUES (3);

-- Specify multiple triggers.
UPDATE /*+ DISABLE_TRIGGER (tg_a, tg_b) */ t1 SET c = c + 1 WHERE id = 1;