Other hints under global hints allow developers and database administrators to exert fine-grained control over the execution of SQL statements. The following table describes the other supported hint types:
Hint name |
Explanation |
|---|---|
APPEND |
Collect statistics during INSERT operations; INSERT INTO SELECT Enable direct load mode (DIRECT INSERT). |
CURSOR_SHARING_EXACT |
Controls whether the query is parameterized. |
DIRECT |
In LOAD DATA and INSERT You can specify to enable the direct load feature in the statement to improve data import efficiency and performance. |
NO_DIRECT |
In LOAD DATA statements, INSERT INTO SELECT statements and CREATE TABLE AS SELECT statements, the direct load feature is disabled. |
USE_PX |
Use the PX mode when executing SQL statements. PX mode allows multi-threading during statement execution. |
ENABLE_PARALLEL_DML |
Enable parallel DML. |
DISABLE_PARALLEL_DML |
Disables parallel DML. |
ENABLE_PARALLEL_DAS_DML |
Specifies to forcibly enable the concurrent write optimization of Distributed Data Access Service (DAS) for the current DML statement. |
DISABLE_PARALLEL_DAS_DML |
Specifies that the current DML statement disables the Concurrent Write Optimization feature of Distributed Data Access Service (DAS). |
DYNAMIC_SAMPLING |
Enable dynamic sampling. |
LOAD_BATCH_SIZE |
Specifies the batch size for each insertion, and is used only in LOAD DATA. |
LOG_LEVEL |
Specifies the log level recorded during the execution of the current query. |
MAX_CONCURRENT |
Sets the maximum number of concurrent queries allowed. |
MONITOR |
This API is used to enable SQL Plan Monitor capture for query execution. |
MV_REWRITE |
When used alone, the MV_REWRITE hint allows you to skip the rule and cost checks for materialized view query rewrite and directly use any available rewrite. |
NO_MV_REWRITE |
Materialized view query rewrite is prohibited, and the query block can be specified. |
OPT_PARAM |
Set query-level optimizer parameters. |
PARALLEL |
Sets the query-level parallelism. The reverse operation is NO_PARALLEL. |
NO_PARALLEL |
Disable query parallelism (that is, set the degree of parallelism to 1). |
QUERY_TIMEOUT |
Sets the query execution timeout period. |
READ_CONSISTENCY |
Read consistency level setting (strong/weak). |
RESOURCE_GROUP |
Compulsorily specifies the resource group used by this statement. |
STAT |
Output statistics for tracing query operators. |
TRANS_PARAM |
Sets the values of transaction-related variables at the query level. |
TRACING |
Traces the output of query execution operators. |
USE_PLAN_CACHE |
Specifies the plan cache usage strategy for the current query. |
DISABLE_TRIGGER |
This statement is used to temporarily disable triggers for the current DML operation. You can specify one or more trigger names in parentheses. If the parentheses and name list are omitted, all triggers related to this DML statement are disabled. |
APPEND Hint
The APPEND hint enables direct load for an INSERT INTO SELECT statement. For more information, see Data import overview.
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 AI 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 of 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.truemeans sorting is required, andfalsemeans sorting is not required.max_errors_allowed: The maximum number of erroneous rows allowed. If the number of erroneous rows exceeds this value, the import will fail.load_mode: Specifies the import mode. Valid values:full: The default value, indicating a full import.inc: Indicates incremental import. Supports theINSERTandIGNOREsemantics.inc_replace: Indicates an incremental import, but does not check for duplicate primary keys. This is equivalent to an incremental import with theREPLACEsemantics.
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 's3://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 Data import overview.
Example of the DIRECT hint in INSERT INTO SELECT
In the INSERT INTO SELECT statement, to enable direct load, you must use it together with enable_parallel_dml. 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 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 Data import overview.
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 not to use direct load. As long as the input SQL contains this hint, it will ignore other direct load hints and execute as a normal 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 NO_DIRECT in INSERT INTO SELECT statements
INSERT /*+ NO_DIRECT */ INTO table_name select_sentenceUse 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 ) */
Parameters
The parameters in the DYNAMIC_SAMPLING hint are defined as follows:
- When the parameter is 0, dynamic sampling is disabled.
- When the parameter is set to 1, dynamic sampling is enabled.
Examples
In the query example below, the DYNAMIC_SAMPLING hint is used 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 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 specifies to enable parallel DML for the current query. Its counterpart 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 will prioritize distributed parallel DML (PDML). If the current environment does not support PDML, the system will fall back to DAS concurrent write as a performance optimization measure.When a forced degree of 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_dmlis set totrueusing theALTER SYSTEM SETcommand (its default value isfalse), the system will not enable DAS (Distributed Active Storage) parallel write operations even if the/*+ENABLE_PARALLEL_DML PARALLEL(n)*/hint is used.When
_enable_parallel_das_dmlis set totrueusing theALTER SYSTEM SETcommand (default isfalse) and a forced degree of parallelism has already been set at the session level, then even if the current environment does not support PDML, using the/*+ENABLE_PARALLEL_DML PARALLEL(n)*/hint will not enable DAS's parallel write feature.
Syntax
/*+ ENABLE_PARALLEL_DML */
Example
insert /*+ enable_parallel_dml parallel(8) */ into t2 select * from t1;
DISABLE_PARALLEL_DML Hint
The DISABLE_PARALLEL_DML hint specifies to disable parallel DML for the current query. Its counterpart hint is ENABLE_PARALLEL_DML, which is used to enable parallel DML.
For more information, see Parallel DML.
Syntax
/*+ DISABLE_PARALLEL_DML */
Example
insert /*+ disable_parallel_dml parallel(8) */ into t2 select * from t1;
ENABLE_PARALLEL_DAS_DML Hint
The ENABLE_PARALLEL_DAS_DML hint is used to explicitly enable concurrent write optimization for the Distributed Data Access Service (DAS) for the current DML statement. Its counterpart hint is DISABLE_PARALLEL_DAS_DML.
Considerations
ENABLE_PARALLEL_DAS_DMLmust be used together withENABLE_PARALLEL_DML.Under the influence of the hint
/*+ENABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(n)*/, the system will enforce parallel DAS writes and execute the operation with the degree of parallelism specified byPARALLEL(n).Setting the system parameter
_enable_parallel_das_dmltotrueusing theALTER SYSTEM SETstatement (default isfalse) will render the hint/*+ENABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(n)*/invalid. In this case, the system will not perform parallel write operations for DAS.
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 the concurrent write optimization of Distributed Data Access Service (DAS) for the current DML statement. Its counterpart hint is ENABLE_PARALLEL_DAS_DML.
Considerations
DISABLE_PARALLEL_DAS_DMLmust be used together withENABLE_PARALLEL_DML.Use the hint
/*+DISABLE_PARALLEL_DAS_DML ENABLE_PARALLEL_DML PARALLEL(n)*/. In this case, the system disables parallel DAS writes, even if other parallel 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 is used in LOAD DATA to specify the number of records to be inserted in each batch.
The batch_size parameter in the LOAD BATCH_SIZE hint specifies the number of records to be inserted in each batch. For more information about LOAD DATA, see LOAD DATA.
Syntax
/*+ LOAD_BATCH_SIZE ( batch_size ) */
Examples
-- Four parallel processes are used to import data, appending the new data 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 is used to specify the log level recorded during the execution of the current query.
The log_level parameter in the LOG_LEVEL hint specifies the log level. Commonly used 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 use of `TRACE` level logging.
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 queries allowed during query execution.
The intnum parameter in the MAX_CONCURRENT hint specifies the maximum number of concurrent queries allowed for the query. When the number of concurrent queries exceeds the allowed maximum, the query execution will report an error indicating that the maximum concurrency has been reached. When intnum is specified as 0, the query execution always reports an error.
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 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 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 AI 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 Real-time execution plan display.
Syntax
/*+ 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 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 for MV_REWRITE is as follows:
/*+ MV_REWRITE (@ queryblock [mv_name_list]) */
mv_name_list:
mv_name [, mv_name ...]
When used alone, the MV_REWRITE hint skips the rule and cost checks for materialized view query rewrite and directly uses any available rewrite. 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 Materialized View Query Rewrite Control Hint
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: 0Create the materialized view
mv1_tbl2and enable automatic rewrite for it.CREATE MATERIALIZED VIEW mv1_tbl2 NEVER REFRESH ENABLE QUERY REWRITE AS SELECT * FROM tbl2;Create the materialized view
mv2_tbl2and enable automatic rewrite for it.CREATE MATERIALIZED VIEW mv2_tbl2 NEVER REFRESH ENABLE QUERY REWRITE AS SELECT * FROM tbl2 WHERE tbl2.col1 > 1;Set the system variable
query_rewrite_integritytostale_tolerated.Note
The
MV_REWRITEandNO_MV_REWRITEhints have a higher priority than the system variablequery_rewrite_enabled, so you do not need to setquery_rewrite_enabled. However, you must setquery_rewrite_integritytostale_toleratedto use non-real-time materialized view-based rewrite.SET query_rewrite_integrity = 'stale_tolerated';Use the
MV_REWRITEhint 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 viewmv1_tbl2for rewriting./*+mv_rewrite*/attempts to use a materialized view that meets the rewrite conditions. Once a suitable materialized view is found, subsequent ones are no longer considered, and the cost/rule check is 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)*/attempts to rewrite usingmv2_tbl2and skips the cost/rule check.EXPLAIN SELECT /*+mv_rewrite(mv2_tbl2)*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 > 1 GROUP BY col1;The return result is as follows:
+-------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------+ | ===================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------- | | |0 |HASH GROUP BY | |1 |3 | | | |1 |└─TABLE FULL SCAN|MV2_TBL2|1 |3 | | | ===================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT(*)], [MV2_TBL2.COL1]), filter(nil), rowset=16 | | group([MV2_TBL2.COL1]), agg_func([T_FUN_COUNT(*)]) | | 1 - output([MV2_TBL2.COL1]), filter(nil), rowset=16 | | access([MV2_TBL2.COL1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([MV2_TBL2.__pk_increment]), range(MIN ; MAX)always true | +-------------------------------------------------------------------------+ 14 rows in set
Although the query specifies to use
mv2_tbl2for query rewrite, since theWHEREcondition of the query statement does not meet the requirements andmv2_tbl2cannot be used for query rewrite, 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 setThe
/*+ no_mv_rewrite*/hint prevents 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 (i.e., 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 rewrite for the current query.
Note that, unlike the NO_REWRITE hint in query block hints, using NO_QUERY_TRANSFORMATION does not allow you to enable some rewrites with query block hints.
For more information about query rewriting, see Overview.
Syntax
/*+ NO_QUERY_TRANSFORMATION */
Example
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 on the following parameters:
rowsets_enabled: enables/disables vectorization. Data type:VARCHAR. Valid values:'TRUE'and'FALSE'. Values must be enclosed in single quotes ('').rowsets_max_rows: sets the batch return row countbatch_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. Valid values:'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. Valid values:'TRUE'and'FALSE'. Values must be enclosed in single quotes ('').enable_in_range_optimization: enables/disables the in optimization in queries. Data type:VARCHAR. Valid values:'TRUE'and'FALSE'. Values must be enclosed in single quotes ('').xsolapi_generate_with_clause: enables/disables CTE extraction rewrite in queries. Data type:VARCHAR. Valid values:'TRUE'and'FALSE'. Values must be enclosed in single quotes ('').preserve_order_forPagination: addsorder byfor ordering or prohibits addingorder byfor pagination queries. Data type:VARCHAR. Valid values:'TRUE'and'FALSE'. Values must be enclosed in single quotes ('').storage_card_estimation: whether to use storage layer row estimation. Data type:VARCHAR. Valid values:'TRUE'and'FALSE'. Values must be enclosed in single quotes ('').workarea_size_policy: the policy for manually or automatically adjusting the SQL workarea size. Data type:VARCHAR. Valid values:'MANUAL'for manual adjustment and'AUTO'for automatic adjustment. Values must be enclosed in single quotes ('').enable_rich_vector_format: enables/disables vectorization 2.0 (session-level parameter). Data type:VARCHAR. Valid values:'TRUE'and'FALSE'. Values must be enclosed in single quotes ('').spill_compression_codec: specifies the compression algorithm for operators requiring temporary materialization. Data type:VARCHAR. Valid values:NONE,LZ4,SNAPPY,ZLIB, andZSTD, representing different compression algorithms. Default isNONE, meaning no compression.inlist_rewrite_threshold: the threshold number of constants that triggers rewriting fromINLISTto aVALUESstatement. Data type:INT64. Value range:[1, 2147483647].orc_filter_pushdown_level: controls the pushdown level of statement-level ORC external table conditions. Valid values forparameter_valueare as follows:0: disables filter condition pushdown.1: pushes down to the file level.2: pushes down to the stripe level.3: pushes down to the row index level.4: pushes down to the encoding level.
parquet_filter_pushdown_level: controls the pushdown level of statement-level Parquet external table conditions. Valid values forparameter_valueare as follows:0: Indicates that filtering condition pushdown is disabled.1: Push down to the file level.2: Push down to the row group level.3: Push down to the page level.4: Push down to the encoding level.
enable_hybrid_search_parallel_execution: controls whether to enable parallel execution for a single full-text or scalar query path in hybrid search. The data type isVARCHAR. Valid values are'TRUE'and'FALSE', which must be enclosed in single quotes ('').
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 parallel execution strategy for the current query.
In addition to being used as a global hint, the PARALLEL hint can also be used as a query block hint to specify the parallelism at the table level. For more information, see the Parallel hint in Access path hints under the Optimized hint type.
Syntax
/*+ PARALLEL ( AUTO | MANUAL | parallel_degree) */
Parameters
AUTOorMANUAL: When usingAUTOorMANUALas a parameter in thePARALLELhint, it only specifies the parallel execution start strategy. For details, see Parallel execution enabling methods and their priority.parallel_degree: When using thePARALLELhint, 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 ) */
Parameter description
time_usec: The query timeout period, in microseconds.
Examples
--Specify the query timeout period to be 1 second. If the query is not completed within the specified timeout period, a timeout error will be returned.
SELECT /*+ QUERY_TIMEOUT(1000000) */ *
FROM employees e
WHERE e.department_id = 1001;
READ_CONSISTENCY Hint
The READ_CONSISTENCY hint specifies the read consistency level for the current query.
Syntax
/*+ READ_CONSISTENCY(WEAK[STRONG]) */
Parameter description
WEAK: Enable weak consistency, which allows weak reads.STRONG: Enable strong consistency, which disables weak-consistency 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;
The isolation levels Repeatable Read and Serializable support weak-consistency read requests. 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 for the RESOURCE_GROUP hint is as follows:
/*+ RESOURCE_GROUP ('resource_group_name') */
Parameter description
resource_group_name: The name of the resource group to be specified.
Examples
The following example shows how to use the RESOURCE_GROUP hint:
obclient> SELECT /*+ RESOURCE_GROUP('big_group') */ * FROM t1;
In this example, if the resource group big_group does not exist, the current default resource group is used.
STAT Hint
The STAT hint specifies to trace the output of specific operators in a query plan.
After adding the STAT hint, the query plan will include an MONITORING DUMP operator. This operator directly outputs all data from the sorting operator and, after execution completes, prints information such as the operator's execution time and number of rows output to the observer log.
Syntax
/*+ STAT(TRACING_NUM_LIST) */
Parameters
TRACING_NUM_LIST: The list of IDs for the operators that need tracing.
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, only the transaction-level parameter FORCE_EARLY_LOCK_FREE is supported.
FORCE_EARLY_LOCK_FREE: A value of TRUE indicates support, while FALSE indicates no support.
Note: Here, the parameter name and value must be enclosed in single quotes (' '). If the parameter value is numeric, quotes are optional.
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 to trace the output of specific operators in a query plan.
The usage, plan structure after adding the hint, and functionality of the TRACING hint are identical to those of the STAT hint.
The difference from using the STAT hint is that when using the TRACING hint, the MONITORING DUMP operator prints all its output data to the observer log.
Syntax
/*+ TRACING(TRACING_NUM_LIST)*/
Parameters
TRACING_NUM_LIST: The list of IDs of 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 usage strategy for the current query. For more information about plan caching, see 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 plan cache strategy controlled by the system variableob_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 temporarily disables 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
DISABLE_TRIGGER can be used alone or followed by a pair of parentheses, in which you list the trigger names (separated by commas for multiple names). Parentheses must be used; brackets must not be used.
/*+ disable_trigger */
/*+ disable_trigger ( trigger_name [, trigger_name ... ] ) */
Parameters
trigger_name: Optional. Specifies the name of the trigger to be temporarily disabled. You can specify one or more trigger names, separated by,. If this parameter is 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;