Other hints under the global hint type|V5.0.1|OceanBase Database|docs|OceanBase Documentations

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

Hint Name	Description
`APPEND`	Collects statistics while inserting data and enables direct insert mode in `INSERT INTO SELECT` statements.
`CURSOR_SHARING_EXACT`	Controls whether queries are parameterized.
`DIRECT`	Enables direct load mode in `LOAD DATA` and `INSERT` statements to improve data import efficiency and performance.
`NO_DIRECT`	Disables direct load mode in `LOAD DATA`, `INSERT INTO SELECT`, and `CREATE TABLE AS SELECT` statements.
`USE_PX`	Executes SQL statements in PX mode, which allows multi-threaded execution.
`ENABLE_PARALLEL_DML`	Enables parallel DML.
`DISABLE_PARALLEL_DML`	Disables parallel DML.
`ENABLE_PARALLEL_DAS_DML`	Forces the current DML statement to use Distributed Data Access Service (DAS) concurrent write optimization.
`DISABLE_PARALLEL_DAS_DML`	Disables Distributed Data Access Service (DAS) concurrent write optimization for the current DML statement.
`DYNAMIC_SAMPLING`	Enables dynamic sampling.
`LOAD_BATCH_SIZE`	Specifies the batch size for each insert and is only applicable in LOAD DATA.
`LOG_LEVEL`	Specifies the log level for the current query during execution.
`MAX_CONCURRENT`	Sets the maximum allowed concurrency for the query.
`MONITOR`	Enables capturing SQL Plan Monitor records for the query.
`MV_REWRITE`	Skips materialized view query rewrite rules/ cost checks when used alone and directly uses applicable rewrites.
`NO_MV_REWRITE`	Disables materialized view query rewrite and allows specifying query blocks.
`OPT_PARAM`	Sets optimizer-related parameters at the query level.
`PARALLEL`	Sets the parallelism level for the query. Its reverse operation is `NO_PARALLEL`.
`NO_PARALLEL`	Disables query parallelism (i.e., sets the parallelism level to 1).
`QUERY_TIMEOUT`	Sets the query execution timeout.
`READ_CONSISTENCY`	Sets the read consistency level (strong/weak).
`RESOURCE_GROUP`	Forces the resource group for the current statement.
`STAT`	Tracks output statistics of query operators.
`TRANS_PARAM`	Sets transaction-related variables at the query level.
`TRACING`	Tracks output of query execution operators.
`USE_PLAN_CACHE`	Specifies the plan cache (Plan Cache) usage strategy for the current query.
`DISABLE_TRIGGER`	Temporarily disables triggers in the current DML statement; specifies one or more trigger names, or disables all triggers related to the statement if no trigger names are specified.

`APPEND` Hint

The APPEND hint enables the direct load feature for 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

The CURSOR_SHARING_EXACT hint specifies that parameterization is disabled at the query level.

OceanBase Database allows you to replace literals in an SQL statement with bound 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 use the CURSOR_SHARING_EXACT hint to disable parameterization. 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

The DIRECT hint enables the direct load feature in LOAD DATA and INSERT statements to improve the efficiency and performance of data import.

Syntax

The syntax of the DIRECT hint is as follows:

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

Parameters

need_sort: specifies whether to sort the imported data. Valid values: true and false. true indicates that sorting is required. false indicates that sorting is not required.
max_errors_allowed: specifies the maximum number of error rows allowed. If the number of error rows exceeds this value, the import process will fail.
load_mode: specifies the import mode. Valid values:
- full: specifies full import. This is the default value.
- inc: specifies incremental import. It supports the INSERT and IGNORE semantics.
- inc_replace: specifies incremental import without checking for duplicate primary keys. This is equivalent to incremental import with the REPLACE semantics.

Examples

Example 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, specifies that sorting is required, 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, specifies that sorting is required, and allows up to 0 error rows.

For more information, see Direct load.

Example in `INSERT INTO SELECT` statements

In INSERT INTO SELECT statements, to enable direct load, you must use the enable_parallel_dml option. The syntax 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, specifies that sorting is required, and allows up to 0 error rows. `enable_parallel_dml` enables parallel DML. `PARALLEL(4)` specifies a parallelism of 4.

**Enable full direct load**
```sql
-- 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;

FROM old_table;


For more information, see [Use the `INSERT INTO SELECT` statement to directly load data](../../../../../../../../../620.obap/300.obap-data-transfer/300.data-import/100.data-import-overview.md).

For more information, see [Use the `INSERT INTO SELECT` statement to directly load data](../../../../../../../../../620.obap/300.obap-data-collection/300.obap-import-data/20.bypass-import/100.overview-of-bypass-import.md).

## `NO_DIRECT` Hint

The `NO_DIRECT` hint disables the direct load feature in `LOAD DATA`, `INSERT INTO SELECT`, and `CREATE TABLE AS SELECT` statements.
### Syntax
The syntax of the `NO_DIRECT` hint is as follows:

```sql

/*+ NO_DIRECT */

Parameters

NO_DIRECT: specifies that a single SQL statement must not use direct load. If an SQL statement contains this hint, the entire statement ignores other direct load hints and executes a regular import.

Examples

Use the NO_DIRECT hint in a 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

DYNAMIC_SAMPLING Hint specifies whether to enable dynamic sampling.

Syntax


/*+ DYNAMIC_SAMPLING ( 0 | 1 ) */

Parameter description

The parameters in 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.

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

USE_PX Hint forces the server to use the 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 of the USE_PX Hint is as follows:


/*+ USE_PX */

Examples

The following example shows how to use 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

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 using Parallel DML (PDML). If PDML is not supported in the current environment, the system will fall back to DAS concurrent writes for performance optimization.
If a forced parallelism level is set at the session level, the execution behavior will remain consistent with the above behavior.
Even if parallel DML (PDML) is not supported in the current environment, 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.
If _enable_parallel_das_dml is set to true using the ALTER SYSTEM SET command (default is false) and a forced parallelism level is set at the session level, then even if PDML is not supported in the current environment, 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

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

ENABLE_PARALLEL_DAS_DML Hint specifies to enable concurrent writes using the 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 together with ENABLE_PARALLEL_DML.
When using the Hint /*+ENABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(n)*/, the system will treat DAS concurrent writes as the 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 (default is false), the Hint /*+ENABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(n)*/ 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

DISABLE_PARALLEL_DAS_DML Hint specifies to disable concurrent writes using the Distributed Data Access Service (DAS) for the current DML statement. Its reverse Hint is ENABLE_PARALLEL_DAS_DML.

Considerations

DISABLE_PARALLEL_DAS_DML must be used together with ENABLE_PARALLEL_DML.
When using the Hint /*+DISABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(n)*/, 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 specifies the number of records inserted in each batch during LOAD DATA. In the LOAD_BATCH_SIZE hint, batch_size specifies the number of records inserted in each batch. For more information about LOAD DATA, see LOAD DATA statement.

Syntax


/*+ LOAD_BATCH_SIZE ( batch_size ) */

Examples

-- Four parallel processes are used to import data, appending new records to the end of the table. The `LOAD_BATCH_SIZE` hint specifies that each batch processes 1,000 records.

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 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](../../../../../../../../../600.manage/800.logging/200.log-level.md).

### Syntax

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

Examples

--Use the LOG_LEVEL hint to specify the use of `TRACE` level logging.

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.
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 allowed maximum, an error is returned when the query is executed. If `intnum` is specified as 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 containing only the `MAX_CONCURRENT` hint to limit the concurrency for specific SQL_IDs.

### Syntax
```sql
/*+ MAX_CONCURRENT ( intnum ) */

Examples


-- This query creates an outline named `otl1` associated with the query identified by the identifier `'EC102CB006383D732BC98797601D9B3B'`, and specifies a maximum concurrent execution count of 10 for that query.

CREATE OUTLINE otl1 ON 'EC102CB006383D732BC98797601D9B3B'

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

## `MONITOR` Hint
The `MONITOR` hint enables the capture of SQL Plan Monitor records for the query. You can use the `MONITOR` hint to record the execution process 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 plans](../../../../../../../../1000.performance-tuning-guide/500.sql-optimization/200.sql-execution-plan/500.real-time-execution-plan-display.md).
### Syntax
```sql
/*+ MONITOR */

Examples


-- Use the MONITOR hint to enable SQL Plan Monitor logging.

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

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

### `MV_REWRITE` Hint

The syntax of the `MV_REWRITE` hint is as follows:

```sql

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

mv_name_list:
mv_name [, mv_name ...]

When you use the MV_REWRITE hint alone, it skips the rule and cost checks for materialized view query rewriting and directly uses the applicable rewrite. If you specify one or more materialized views after the hint, it skips the rule and cost checks and only uses the specified materialized views for rewriting, ignoring all unspecified materialized views.

When you use the MV_REWRITE hint to specify materialized views, you cannot force the use of materialized views that do not have the ENABLE QUERY REWRITE clause. Additionally, you cannot force the use of 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) */

The NO_MV_REWRITE hint disables materialized view query rewriting and allows you to specify a query block.

Examples of using materialized view query rewrite control hints

Create the base table tbl2.


CREATE TABLE tbl2 (col1 INT, col2 INT);

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

Create the materialized view mv1_tbl2 and enable query rewriting 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 for it.
    ```sql

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

Set the system variable query_rewrite_integrity to stale_tolerated.

Note

 <p>The <code>MV_REWRITE</code> and <code>NO_MV_REWRITE</code> hints have a higher priority than the system variable <code>query_rewrite_enabled</code>. Therefore, you do not need to set <code>query_rewrite_enabled</code>. However, you need to set <code>query_rewrite_integrity</code> to <code>stale_tolerated</code> to use non-real-time materialized views for rewriting.</p>
 </main>
 ```sql

 SET query_rewrite_integrity = 'stale_tolerated';

 ```
 6. Use the `MV_REWRITE` hint to attempt rewriting using materialized views and skip the cost and rule checks. The following two queries will use the materialized view `mv1_tbl2` for rewriting.
 * `/*+mv_rewrite*/` will attempt to use a materialized view that meets the rewriting conditions. Once a suitable materialized view is found, subsequent materialized views will be ignored, and the cost and rule checks will be skipped.
 ```sql
 EXPLAIN SELECT /*+mv_rewrite*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 > 1 GROUP BY col1;
 ```
 The return result is as follows:
 ```shell
 +----------------------------------------------------------------------------------------------+
 | 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 checks.
 ```sql
 EXPLAIN SELECT /*+mv_rewrite(mv2_tbl2)*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 > 1 GROUP BY col1;
 ```
 The return result is as follows:
 ```shell
 +-------------------------------------------------------------------------+
 | 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 rewriting, the `WHERE` condition in the query does not meet the requirements, so `mv2_tbl2` cannot be used for rewriting. Therefore, no materialized view query rewriting will be performed.
```sql
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

Use the /*+ no_mv_rewrite*/ hint to disable materialized view query rewriting.

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 rewriting for the current query.

It is important to note that unlike the NO_REWRITE hint in Query Block hints, using NO_QUERY_TRANSFORMATION prevents the use of Query Block hints to enable certain types of rewriting.

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

Syntax

/*+ NO_QUERY_TRANSFORMATION */

Example

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

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

`OPT_PARAM` Hint

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 to be specified for the variable.

The OPT_PARAM Hint is effective for the following parameters:

rowsets_enabled: specifies whether to enable or disable vectorization. Data type: VARCHAR. Valid values: 'TRUE' and 'FALSE'. Enclose the value in single quotation marks (' ').
rowsets_max_rows: specifies the number of rows to be returned in a batch, batch_size. Data type: INT. Valid value: [0, 65535]. Do not enclose the value in single quotation marks (' ').
enable_newsort: specifies whether to enable or disable the newsort optimization in queries. Data type: VARCHAR. Valid values: 'TRUE' and 'FALSE'. Enclose the value in single quotation marks (' ').
use_part_sort_mgb: specifies whether to enable or disable the part sort merge group by in queries. Data type: VARCHAR. Valid values: 'TRUE' and 'FALSE'. Enclose the value in single quotation marks (' ').
enable_in_range_optimization: specifies whether to enable or disable the IN optimization in queries. Data type: VARCHAR. Valid values: 'TRUE' and 'FALSE'. Enclose the value in single quotation marks (' ').
xsolapi_generate_with_clause: specifies whether to enable or disable the CTE extraction rewrite in queries. Data type: VARCHAR. Valid values: 'TRUE' and 'FALSE'. Enclose the value in single quotation marks (' ').
- preserve_order_for_pagination: specifies whether to add an order by clause for pagination queries. Data type: VARCHAR. Valid values: 'TRUE' and 'FALSE'. Enclose the value in single quotation marks (' ').
  - storage_card_estimation: specifies whether to use the storage layer for row estimation. Data type: VARCHAR. Valid values: 'TRUE' and 'FALSE'. Enclose the value in single quotation marks (' ').
  - workarea_size_policy: specifies the strategy for manually or automatically adjusting the SQL workarea size. Data type: VARCHAR. Valid values: 'MANUAL' for manual adjustment and '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). Data type: VARCHAR. Valid values: 'TRUE' and 'FALSE'. Enclose the value in single quotation marks (' ').
- spill_compression_codec: specifies the compression algorithm to be used for operators that require temporary materialization. Data type: VARCHAR. Valid values: NONE, LZ4, SNAPPY, ZLIB, and ZSTD, representing different compression algorithms. Default value: NONE, indicating no compression.
- inlist_rewrite_threshold: specifies the threshold for triggering the rewrite of inlist to a values statement. Data type: INT64. Valid value: [1, 2147483647].
- orc_filter_pushdown_level: specifies the level at which to push down filter conditions for ORC external tables. Valid values for parameter_value:
Note

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

</main>
  * `0`: disables filter condition pushdown.
  * `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. Valid values for parameter_value:

Note

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

0: disables filter condition pushdown.
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 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 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 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 strategies 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 automatic 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 parallel execution using the PARALLEL hint. 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


--Specify the query timeout period to be 1 second. If the query is not completed within this 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
```sql
/*+ READ_CONSISTENCY(WEAK[STRONG]) */

Parameters

WEAK: enables weak consistency, i.e., weak reads.

STRONG: enables strong consistency, i.e., disables weak reads.

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:
```sql
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 specifies the resource group for the current statement.

### Syntax
The syntax for the `RESOURCE_GROUP` hint is as follows:
```sql

/*+ RESOURCE_GROUP ('resource_group_name') */

Parameters

resource_group_name: specifies the name of the resource group.

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 the operators in the query plan whose outputs are to be traced. After adding the STAT hint, the query plan includes the MONITORING DUMP operator. This operator 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  |

===============================

|4 | └─TABLE FULL SCAN|T2 |

===============================

## `TRANS_PARAM` Hint
The `TRANS_PARAM` hint specifies transaction-related parameters at the query level.
### Syntax
```sql
/*+ 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 (enabled) and FALSE (disabled).

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

Examples

-- Use the TRANS_PARAM hint and set the parameter 'FORCE_EARLY_LOCK_FREE' to 'TRUE' to specify 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 in the query plan whose outputs are to be traced.
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 when you use the `TRACING` hint, the `MONITORING DUMP` operator prints all its output data in the observer log.

### Syntax

```sql

/*+ TRACING(TRACING_NUM_LIST)*/

Parameters

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

Examples


-- Use the TRACING hint and set its 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 the plan cache, see [Execution plan cache](../../../../../../../../1000.performance-tuning-guide/500.sql-optimization/200.sql-execution-plan/300.execution-plan-cache.md).
### Syntax
```sql
/*+ 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

-- 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 temporarily disables triggers during the execution of the current DML statement. It applies to `INSERT`, `UPDATE`, `DELETE`, and DML operations on views (including `INSTEAD OF` triggers).
### Syntax

```sql
/*+ disable_trigger */

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

APPEND Hint

Syntax

Examples

CURSOR_SHARING_EXACT Hint

Syntax

Examples

DIRECT Hint

Syntax

Parameters

Examples

Example in LOAD DATA statements

Example in INSERT INTO SELECT statements

Parameters

Examples

DYNAMIC_SAMPLING Hint

Syntax

Parameter description

Examples

USE_PX Hint

Syntax

Examples

ENABLE_PARALLEL_DML Hint

Considerations

Syntax

Examples

DISABLE_PARALLEL_DML Hint

Syntax

Examples

ENABLE_PARALLEL_DAS_DML Hint

Considerations

Syntax

Examples

DISABLE_PARALLEL_DAS_DML Hint

Considerations

Syntax

Examples

LOAD_BATCH_SIZE Hint

Syntax

Examples

Examples

Examples

Examples

NO_MV_REWRITE Hint

Examples of using materialized view query rewrite control hints

Note

NO_PARALLEL Hint

Syntax

Example

NO_QUERY_TRANSFORMATION Hint

Syntax

Example

OPT_PARAM Hint

Syntax

Parameters

Note

Note

Examples

PARALLEL Hint

Syntax

Parameters

Examples

QUERY_TIMEOUT Hint

Syntax

Parameters

Examples

Parameters

Examples

Parameters

Examples

STAT Hint

Syntax

Parameters

Examples

Parameters

Examples

Parameters

Examples

Parameters

Examples

Parameters

`APPEND` Hint

`CURSOR_SHARING_EXACT` Hint

`DIRECT` Hint

Example in `LOAD DATA` statements

Example in `INSERT INTO SELECT` statements

`DYNAMIC_SAMPLING` Hint

`USE_PX` Hint

`ENABLE_PARALLEL_DML` Hint

`DISABLE_PARALLEL_DML` Hint

`ENABLE_PARALLEL_DAS_DML` Hint

`DISABLE_PARALLEL_DAS_DML` Hint

`LOAD_BATCH_SIZE` Hint

`NO_MV_REWRITE` Hint

`NO_PARALLEL` Hint

`NO_QUERY_TRANSFORMATION` Hint

`OPT_PARAM` Hint

`PARALLEL` Hint

`QUERY_TIMEOUT` Hint

`STAT` Hint