Other hints in the GLOBAL hint category

Other hints in the GLOBAL hint category allow developers and database administrators to perform fine-grained control over the execution of SQL statements. The following table describes the supported hints in this category:

APPEND Hint

APPEND Hint specifies to enable direct load in INSERT INTO SELECT statements. For more information, see Use the INSERT INTO SELECT statement to directly load data.

Syntax

/*+ APPEND */

Examples

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

CURSOR_SHARING_EXACT Hint

CURSOR_SHARING_EXACT Hint specifies to disable parameterization at the query level.

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

Syntax

/*+ CURSOR_SHARING_EXACT */

Examples

The following example shows how to disable parameterization using the CURSOR_SHARING_EXACT Hint. After executing the query with two sets of parameters four times, two query plans are generated in the plan cache 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

DIRECT Hint can be specified in LOAD DATA and INSERT statements to enable direct load, thereby improving the efficiency and performance of data import.

Syntax

The syntax of 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 that sorting is required. false indicates that sorting is not required.

• max_errors_allowed: specifies the maximum number of allowed error rows. If this number is exceeded, the import process will fail.

• load_mode: specifies the import mode. The options are as follows:

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

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, performs sorting, and allows up to 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 of 4, and DIRECT(true, 0, full) enables full direct load, performs sorting, and allows up to 0 error rows.

For more information, see Direct load.

DIRECT Hint in INSERT INTO SELECT statements

In INSERT INTO SELECT statements, to enable direct load, you must use enable_parallel_dml in the following format: /*+ 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, performs sorting, and allows up to 0 error rows. enable_parallel_dml enables parallel DML. PARALLEL(4) sets the parallelism to 4.

Enable full direct load

-- Enable full direct load and parallel DML to import 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, performs sorting, and allows up to 0 error rows. enable_parallel_dml enables parallel DML. PARALLEL(4) sets the parallelism to 4.

For more information, see Use the INSERT INTO SELECT statement to directly load data.

NO_DIRECT Hint

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

Syntax

The syntax of 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 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 a query.

Syntax

/*+ DYNAMIC_SAMPLING ( 0 | 1 ) */

Parameter description

The parameters of the DYNAMIC_SAMPLING hint are described as follows:

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

Examples

The following example shows how to use the DYNAMIC_SAMPLING hint to enable dynamic sampling.

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 an SQL statement. 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 of the USE_PX hint is as follows:

/*+ USE_PX */

Examples

The following example shows the use of the USE_PX hint:

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 is used to disable parallel DML.

For more information, see Parallel DML.

Considerations

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

• When a forced parallelism level is set at the session level, its execution behavior will remain consistent with the above behavior.

• 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 writes, even if the /*+ENABLE_PARALLEL_DML PARALLEL(n)*/ hint is used.

• When _enable_parallel_das_dml is set to true using the ALTER SYSTEM SET command (default is false), and a forced parallelism level is already set at the session level, using the /*+ENABLE_PARALLEL_DML PARALLEL(n)*/ hint will not enable DAS parallel writes.

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 force the use of Distributed Data Access Service (DAS) concurrent writes for the current DML statement. Its reverse hint is DISABLE_PARALLEL_DAS_DML.

Considerations

• The ENABLE_PARALLEL_DAS_DML hint must be used in conjunction with the ENABLE_PARALLEL_DML hint.

• When the /*+ENABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(n)*/ hint is used, the system will treat DAS concurrent writes as the forced option and execute the operation 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 (default is false), the /*+ENABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(n)*/ hint will be ineffective. In this case, the system will not perform DAS parallel writes.

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 force the disablement of Distributed Data Access Service (DAS) concurrent writes for the current DML statement. Its reverse hint is ENABLE_PARALLEL_DAS_DML.

Considerations

• The DISABLE_PARALLEL_DAS_DML hint must be used in conjunction 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 writes, even if other concurrent write optimization options like 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 is used in LOAD DATA to specify the size of the batch of records to be inserted each time.

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

Syntax

/*+ LOAD_BATCH_SIZE ( batch_size ) */

Example

-- Use 4 parallel processes to import data, append 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 is used to specify the log level for the current query during execution.

In the LOG_LEVEL hint, log_level 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 is used to specify the maximum number of concurrent executions for a query.

In the MAX_CONCURRENT hint, intnum specifies the maximum number of concurrent executions allowed for the query. If the number of concurrent executions exceeds the maximum allowed, 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 currently, the MAX_CONCURRENT hint cannot be directly used in a query. Instead, you can create an outline that contains only the MAX_CONCURRENT hint to limit the concurrency of specific SQL statements.

Syntax

/*+ MAX_CONCURRENT ( intnum ) */

Example

-- 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 the capture of SQL Plan Monitor records for a query. You can use the MONITOR hint to record the execution process of a query in the SQL Plan Monitor.

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

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

Syntax

/*+ MONITOR */

Example

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

Query rewrite hints

The query rewrite hints include MV_REWRITE and NO_MV_REWRITE. These hints have 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 you use the MV_REWRITE hint alone, you can skip the rule and cost checks for query rewrite and directly use the applicable rewrite. If you specify one or more materialized views after the hint, the rule and cost checks are skipped, and only the specified materialized views are used for query rewrite, ignoring all other materialized views.

When you use the MV_REWRITE hint to specify materialized views, you cannot force the use of a materialized view that does not have the ENABLE QUERY REWRITE clause. Additionally, you cannot 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 of NO_MV_REWRITE is as follows:

/*+ NO_MV_REWRITE (@ queryblock) */

The NO_MV_REWRITE hint disables query rewrite for the specified query block.

Example of using query rewrite hints

1. Create the base table tbl2.

   CREATE TABLE tbl2 (col1 INT, col2 INT);
   

2. Insert two rows of data 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 query 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 automatic query rewrite for 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 hints MV_REWRITE and NO_MV_REWRITE have higher priority than the system variable query_rewrite_enabled. Therefore, 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 query rewrite.

   SET query_rewrite_integrity = 'stale_tolerated';
   

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

   • /*+mv_rewrite*/ will attempt to use a materialized view that meets the rewrite conditions for query rewrite. 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 mv2_tbl2 for query rewrite 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 using mv2_tbl2 for query rewrite, the WHERE condition in the query does not meet the requirements, so mv2_tbl2 cannot be used for query rewrite. Therefore, no query rewrite will 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 disable 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 disables query parallelism (i.e., sets the parallelism to 1). Its opposite hint is PARALLEL.

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

Syntax

/*+ NO_PARALLEL */

Example

The following query example uses the NO_PARALLEL hint 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 disables any query rewrite for the current query.

Note that unlike the NO_REWRITE hint in the Query Block Hint, using the NO_QUERY_TRANSFORMATION hint prevents you from enabling some rewrites through the Query Block Hint.

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

Syntax

/*+ NO_QUERY_TRANSFORMATION */

Example

The following query example uses the NO_QUERY_TRANSFORMATION hint to disable any query rewrite.

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

OPT_PARAM Hint

OPT_PARAM Hint specifies some optimizer-related configurations/system variables at the query level.

Syntax

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

Parameters

• parameter_name: the name of the configuration or system variable.

• parameter_value: the value of the variable to be specified.

The OPT_PARAM Hint is effective for the following parameters:

• rowsets_enabled: specifies whether to enable or disable vectorization. The data type is VARCHAR, and the value can be 'TRUE' or 'FALSE'. The value must be enclosed in single quotation marks (').

• rowsets_max_rows: specifies the number of rows to be returned in a batch (batch_size). The data type is INT, and the value ranges from [0, 65535]. The value must not be enclosed in single quotation marks (').

• enable_newsort: specifies whether to enable or disable the newsort optimization. The data type is VARCHAR, and the value can be 'TRUE' or 'FALSE'. The value must be enclosed in single quotation marks (').

• use_part_sort_mgb: specifies whether to enable or disable the part sort merge group by. The data type is VARCHAR, and the value can be 'TRUE' or 'FALSE'. The value must be enclosed in single quotation marks (').

• enable_in_range_optimization: specifies whether to enable or disable the in optimization. The data type is VARCHAR, and the value can be 'TRUE' or 'FALSE'. The value must be enclosed in single quotation marks (').

• xsolapi_generate_with_clause: specifies whether to enable or disable the CTE extraction rewrite. The data type is VARCHAR, and the value can be '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 pagination queries to preserve order or to prohibit adding an order by clause. The data type is VARCHAR, and the value can be 'TRUE' or 'FALSE'. The value must be enclosed in single quotation marks (').

• storage_card_estimation: specifies whether to use the storage layer for row estimation. The data type is VARCHAR, and the value can be '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 can be 'MANUAL' for manual adjustment or 'AUTO' for automatic adjustment. The value must be enclosed 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'. The value must be enclosed in single quotation marks (').

• spill_compression_codec: specifies the compression algorithm to be used for operators that require temporary materialization. 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 rewriting of inlist into a values statement. The data type is INT64, and the value ranges from [1, 2147483647].

• orc_filter_pushdown_level: specifies the level at which to push down filter conditions for ORC external tables. The 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 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

The following example uses the OPT_PARAM Hint to enable IN optimization for the current query by specifying the enable_in_range_optimization value.

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 parallelism or parallelism 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 parallelism at the table level. For more information, see the Parallel hint section 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 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 parallelism.

Examples

The following example uses the PARALLEL Hint to specify a parallelism of DOP = 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: Some scenarios do not support parallel execution. Even if you specify the PARALLEL hint to enable parallel execution, the execution plan may still 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.

Examples

-- Specifies the query timeout as 1 second. If the query does not complete within the specified timeout, 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: specifies to use weak consistency, which enables weak reads.

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

Examples

-- Uses the READ_CONSISTENCY hint and sets it to WEAK to enable weak 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. Here is an 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 forces the statement to use a specified resource group.

Syntax

The syntax of the RESOURCE_GROUP hint is as follows:

/*+ RESOURCE_GROUP ('resource_group_name') */

Parameters

• resource_group_name: specifies the name of the resource group to use.

Examples

Here is an example of using the RESOURCE_GROUP hint:

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

If the resource group big_group does not exist, the default resource group is used.

STAT Hint

The STAT hint specifies to trace 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 directly outputs all data from the sort operator and prints the operator's execution time, number of 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 trace.

Examples

-- Displays the changes in the execution plan 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, only the FORCE_EARLY_LOCK_FREE parameter is supported, which specifies whether to enable early row lock release at the transaction level.

• FORCE_EARLY_LOCK_FREE: specifies whether to enable early row lock release at the transaction level. Valid values: TRUE (enables early row lock release) and FALSE (disables early row lock release).

Notice: The parameter name and value must be enclosed in single quotation marks ( ' '). If the parameter value is a number, you can omit the quotation marks.

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 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 behavior when using 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 its output data in the observer log when using the TRACING hint.

Syntax

/*+ TRACING(TRACING_NUM_LIST)*/

Parameters

• TRACING_NUM_LIST: specifies the IDs of the operators to trace.

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 specifies the plan cache strategy for the current query. For more information about plan cache, see Execution plan cache.

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 system variable ob_enable_plan_cache to control the plan cache strategy.

Examples

-- The following example specifies that the query does not use the plan cache when the `NONE` parameter is used, and uses the system variable `ob_enable_plan_cache` to control the plan cache strategy 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;

DISABLE_TRIGGER Hint

The DISABLE_TRIGGER hint disables the specified trigger in DML operations.

Syntax

/*+ disable_trigger[(trigger_name)] */

Parameters

trigger_name: an optional parameter that specifies the name of the trigger to be temporarily disabled. You can specify one or more trigger names, separated by commas. If you omit this parameter, all triggers involved in the query are disabled by default.

Examples

The following example disables the trigger named test_trigger1.

INSERT /*+ disable_trigger[(test_trigger1)] */ INTO test VALUES(2);