AnalyticDB:Config and hint configuration parameters

Category

Parameter

Description

Example

Documentation Link

Switchover window

REPLICATION_SWITCH_TIME_RANGE

During a switchover to a new cluster, the old cluster supports read-only operations for 5 to 10 minutes. After connecting to the old cluster, configure REPLICATION_SWITCH_TIME_RANGE to specify the switchover time window for the old and new clusters.

SET ADB_CONFIG REPLICATION_SWITCH_TIME_RANGE=`23:00, 23:30`;

Scale a Data Warehouse Edition cluster

Limit on IN conditions

MAX_IN_ITEMS_COUNT

Set the limit on the number of IN conditions. The value must be a positive integer greater than 0.

• For clusters with Milvus version 3.1.8 or earlier, the default value is 2000.

• For clusters with Milvus version 3.1.9.x to 3.1.10.x, the default value is 4000.

• For clusters with Milvus version 3.2.1 or later, the default value is 20000.

SET ADB_CONFIG MAX_IN_ITEMS_COUNT=4000;

Writes and queries

Query timeout period

QUERY_TIMEOUT

Configure the query timeout period for all queries at the cluster level. The value must be an integer greater than 0. Unit: milliseconds (ms).

SET ADB_CONFIG QUERY_TIMEOUT=1000;

Timeout period for INSERT, UPDATE, and DELETE statements

INSERT_SELECT_TIMEOUT

Modify the maximum execution time for INSERT, UPDATE, and DELETE statements at the cluster level. Default value: 24×3600000. The value must be an integer greater than 0. Unit: milliseconds (ms).

SET ADB_CONFIG INSERT_SELECT_TIMEOUT=3600000;

Limits

Disable filter condition pushdown

• For Milvus versions 3.1.4 and later: FILTER_NOT_PUSHDOWN_COLUMNS

• Milvus version earlier than 3.1.4: NO_INDEX_COLUMNS

Disable filter condition pushdown for specific fields at the cluster level.

• For kernel versions 3.1.4 and later: SET ADB_CONFIG FILTER_NOT_PUSHDOWN_COLUMNS=[Schema1.tableName1:colName1|colName2;Schema2.tableName2:colName1|colName2]

• For Milvus versions below 3.1.4: SET ADB_CONFIG NO_INDEX_COLUMNS=[tableName1.colName1;colName2,tableName2.colName1]

Filter conditions without pushdown

Query execution mode

QUERY_TYPE

Switch the query execution mode of the instance. Valid values:

• interactive

• batch

SET ADB_CONFIG QUERY_TYPE=interactive;

Query execution mode

Query queue

XIHE_ENV_QUERY_ETL_MAX_CONCURRENT_SIZE

The maximum number of queries that can run concurrently in the LOWEST queue on a single frontend node. Default value: 20.

SET ADB_CONFIG XIHE_ENV_QUERY_ETL_MAX_CONCURRENT_SIZE=20;

Priority queues and concurrency of interactive resource groups

XIHE_ENV_QUERY_ETL_MAX_QUEUED_SIZE

The maximum number of queries that can be queued in the LOWEST queue on a single frontend node. Default value: 200.

SET ADB_CONFIG XIHE_ENV_QUERY_ETL_MAX_QUEUED_SIZE=200;

XIHE_ENV_QUERY_LOW_PRIORITY_MAX_CONCURRENT_SIZE

The maximum number of queries that can run concurrently in the LOW queue on a single frontend node. Default value: 20.

SET ADB_CONFIG XIHE_ENV_QUERY_LOW_PRIORITY_MAX_CONCURRENT_SIZE=20;

XIHE_ENV_QUERY_LOW_PRIORITY_MAX_QUEUED_SIZE

The maximum number of queries that can be queued in the LOW queue on a single frontend node. Default value: 200.

SET ADB_CONFIG XIHE_ENV_QUERY_LOW_PRIORITY_MAX_QUEUED_SIZE=200;

XIHE_ENV_QUERY_NORMAL_MAX_CONCURRENT_SIZE

The maximum number of queries that can run concurrently in the NORMAL queue on a single frontend node. Default value: 20.

SET ADB_CONFIG XIHE_ENV_QUERY_NORMAL_MAX_CONCURRENT_SIZE=20;

XIHE_ENV_QUERY_NORMAL_MAX_QUEUED_SIZE

The maximum number of queries that can be queued in the NORMAL queue on a single frontend node. Default value: 200.

SET ADB_CONFIG XIHE_ENV_QUERY_NORMAL_MAX_QUEUED_SIZE=200;

XIHE_ENV_QUERY_HIGH_MAX_CONCURRENT_SIZE

The maximum number of queries that can run concurrently in the HIGH queue on a single frontend node. Default value: 40.

SET ADB_CONFIG XIHE_ENV_QUERY_HIGH_MAX_CONCURRENT_SIZE=40;

XIHE_ENV_QUERY_HIGH_MAX_QUEUED_SIZE

The maximum number of queries that can be queued in the HIGH queue on a single frontend node. Default value: 400.

SET ADB_CONFIG XIHE_ENV_QUERY_HIGH_MAX_QUEUED_SIZE=400;

Priority of XIHE BSP jobs

QUERY_PRIORITY

The priority of a BSP job. Valid values:

• HIGH

• NORMAL (default)

• LOW

• LOWEST

SET ADB_CONFIG QUERY_PRIORITY=HIGH;

Configuration Item List

Maximum ACUs for XIHE BSP jobs

ELASTIC_JOB_MAX_ACU

Maximum number of ACUs used by a single XIHE BSP job. The default value is 9, the minimum value is 3, and the value range

is 3 (the maximum resource amount for a Job-type resource group).

SET ADB_CONFIG ELASTIC_JOB_MAX_ACU=20;

Timeout period for XIHE BSP job queries

BATCH_QUERY_TIMEOUT

The timeout period for XIHE BSP jobs. Default value: 7200000 ms. The value must be an integer greater than 0. Unit: milliseconds (ms).

SET ADB_CONFIG BATCH_QUERY_TIMEOUT=3600000 ;

Import from OSS external tables

HIVE_SPLIT_ASYNC_GENERATION_ENABLED

When Hive generates an execution plan, specify whether to asynchronously submit Hive Split tasks to the queue for execution. Valid values:

• true: Yes.

• false (default): No.

SET ADB_CONFIG HIVE_SPLIT_ASYNC_GENERATION_ENABLED=true;

None

Import from MaxCompute external tables

SQL_OUTPUT_BATCH_SIZE

The number of data entries when importing data in batches. The value must be an integer greater than 0.

SET ADB_CONFIG SQL_OUTPUT_BATCH_SIZE = 6000;

Import and export

ENABLE_ODPS_MULTI_PARTITION_PART_MATCH

Specify whether to pre-traverse to get the number of records in each MaxCompute partition.

SET ADB_CONFIG ENABLE_ODPS_MULTI_PARTITION_PART_MATCH=false;

ASYNC_GET_SPLIT

When MaxCompute generates an execution plan, specify whether to optimize split loading performance by asynchronously submitting tasks to the queue for execution. Valid values:

• true: Yes.

• The default value is false, which indicates no.

SET ADB_CONFIG ASYNC_GET_SPLIT=true;

Automatic scheduling time for BUILD jobs

RC_CSTORE_BUILD_SCHEDULE_PERIOD

Limit the scheduling time range for BUILD tasks, not the task execution time range. The value must be an integer between 0 and 24.

SET ADB_CONFIG RC_CSTORE_BUILD_SCHEDULE_PERIOD=`6,8`;

Configure automatic BUILD scheduling times

Scheduling priority for BUILD jobs

ADB_CONFIG RC_BUILD_TASK_PRIORITY_LIST

Configure the scheduling priority for BUILD jobs on single or multiple tables. The priority remains effective until you configure a new priority for the table.

The scheduling priority for BUILD jobs is controlled by the task_priority parameter. The value must be an integer. Default value: 0.

SET ADB_CONFIG RC_BUILD_TASK_PRIORITY_LIST = `<db1_name>.<table1_name>.<task_priority>;<db2_name>.<table2_name>.<task_priority>`;

BUILD

Elastic import

RC_ELASTIC_JOB_SCHEDULER_ENABLE

Manually enable the elastic import switch when importing data elastically.

SET ADB_CONFIG RC_ELASTIC_JOB_SCHEDULER_ENABLE=true;

• Use external tables to import data from OSS

• Elastic import of MaxCompute data through external tables

REMOTE_CALL function

XIHE_REMOTE_CALL_SERVER_ENDPOINT

The internal service endpoint of the Function Compute service used when you remotely invoke a user-defined function in the Function Compute service using the REMOTE_CALL function. For more information, see Service endpoints.

SET ADB_CONFIG XIHE_REMOTE_CALL_SERVER_ENDPOINT="1234567890000****.cn-zhangjiakou-internal.fc.aliyuncs.com"

User-defined functions (UDFs)

XIHE_REMOTE_CALL_SERVER_AK

When invoking UDFs in Function Compute using the REMOTE_CALL function, specify the AccessKey ID of the Alibaba Cloud account or Resource Access Management (RAM) user that owns Function Compute.

SET ADB_CONFIG XIHE_REMOTE_CALL_SERVER_AK=************;

XIHE_REMOTE_CALL_SERVER_SK

When invoking UDFs in Function Compute using the REMOTE_CALL function, specify the AccessKey secret of the Alibaba Cloud account or RAM user that owns Function Compute.

SET ADB_CONFIG XIHE_REMOTE_CALL_SERVER_SK=************;

XIHE_REMOTE_CALL_COMPRESS_ENABLED

When invoking UDFs in Function Compute using the REMOTE_CALL function, specify whether to compress data in GZIP format before transmitting it to Function Compute.

SET ADB_CONFIG REMOTE_CALL_COMPRESS_ENABLED=true;

XIHE_REMOTE_CALL_MAX_BATCH_SIZE

When invoking UDFs in Function Compute using the REMOTE_CALL function, specify the number of data rows sent to Function Compute.

SET ADB_CONFIG XIHE_REMOTE_CALL_MAX_BATCH_SIZE=500000;

Scan concurrency control

ADB_CONFIG SPLIT_FLOW_CONTROL_ENABLED

AnalyticDB for MySQL supports configuring scan concurrency quotas. This prevents node instability caused by excessive scan concurrency and resource consumption during queries. Valid values:

• true (default): Enable the scan concurrency control feature.

• false: Disable the scan concurrency control feature.

SET ADB_CONFIG SPLIT_FLOW_CONTROL_ENABLED=true;

scan concurrency control

ADB_CONFIG NODE_LEVEL_SPLIT_FLOW_CONTROL_ENABLED

AnalyticDB for MySQL supports dynamically adjusting the scan concurrency quota for tasks based on the overall scan concurrency quota of the node. Valid values:

• true: Enable dynamic adjustment of the task scan concurrency control feature.

• false (default): Disable dynamic adjustment of the task scan concurrency control feature.

SET ADB_CONFIG NODE_LEVEL_SPLIT_FLOW_CONTROL_ENABLED=true;

MIN_RUNNING_SPLITS_LIMIT_PER_TASK

The minimum scan concurrency quota for tasks. Default value: 1. Valid values: [1, TARGET_RUNNING_SPLITS_LIMIT_PER_TASK].

SET ADB_CONFIG MIN_RUNNING_SPLITS_LIMIT_PER_TASK=24;

TARGET_RUNNING_SPLITS_LIMIT_PER_TASK

The intermediate scan concurrency quota for tasks. Increase or decrease the scan concurrency quota based on this value. Default value: 32. Valid values: [MIN_RUNNING_SPLITS_LIMIT_PER_TASK, MAX_RUNNING_SPLITS_LIMIT_PER_TASK].

TARGET_RUNNING_SPLITS_LIMIT_PER_TASK=32;

MAX_RUNNING_SPLITS_LIMIT_PER_TASK

The maximum scan concurrency quota for tasks. Default value: 64. The value must be greater than TARGET_RUNNING_SPLITS_LIMIT_PER_TASK.

SET ADB_CONFIG MAX_RUNNING_SPLITS_LIMIT_PER_TASK=128;

ADB_CONFIG WORKER_MAX_RUNNING_SOURCE_SPLITS_PER_NODE

The default scan concurrency quota for storage nodes is 256. An excessively large or small quota affects cluster performance. Do not modify the default value.

SET ADB_CONFIG WORKER_MAX_RUNNING_SOURCE_SPLITS_PER_NODE=256;

ADB_CONFIG EXECUTOR_MAX_RUNNING_SOURCE_SPLITS_PER_NODE

The default scan concurrency quota for compute nodes is 256. An excessively large or small quota affects cluster performance. Do not modify the default value.

SET ADB_CONFIG EXECUTOR_MAX_RUNNING_SOURCE_SPLITS_PER_NODE=256;

Case sensitivity for logical views

VIEW_OUTPUT_NAME_CASE_SENSITIVE

Set case sensitivity for logical views. Valid values:

• true: Case-sensitive.

• false (default): Case-insensitive.

SET ADB_CONFIG VIEW_OUTPUT_NAME_CASE_SENSITIVE=true;

CREATE VIEW

Execute multiple SQL statements consecutively

ALLOW_MULTI_QUERIES

When executing multiple SQL statements consecutively using the Multi-Statement feature, manually enable the Multi-Statement feature. Valid values:

• true: Enable.

• false (default): Disable.

SET ADB_CONFIG ALLOW_MULTI_QUERIES=true;

Multi-Statement

Enable binary logging

BINLOG_ENABLE

• For clusters running a Milvus version earlier than 3.2.0.0, you must first manually enable the binary logging attribute before you can enable the binary logging feature for tables.

• For clusters running Milvus version 3.2.0.0 or later, binary logging is enabled by default. You do not need to configure this parameter.

SET ADB_CONFIG BINLOG_ENABLE=true;

Refresh materialized view

Paging cache (performance optimization for deep paged queries)

PAGING_CACHE_SCHEMA

Specify the database that stores temporary cache tables for paged queries. If not specified, the currently connected internal database is used.

SET ADB_CONFIG PAGING_CACHE_SCHEMA=paging_cache;

Paging cache (performance optimization for deep paged queries)

PAGING_CACHE_MAX_TABLE_COUNT

Set the maximum number of temporary cache tables. If this number is exceeded, new cache creation fails. Default value: 100. The value must be an integer greater than 0.

SET ADB_CONFIG PAGING_CACHE_MAX_TABLE_COUNT=100;

PAGING_CACHE_EXPIRATION_TIME

Specify the cache time-to-live (TTL) threshold. The system automatically clears paged query caches not accessed within the specified time. Default value: 600. The value must be an integer greater than 0. Unit: seconds (s).

SET ADB_CONFIG PAGING_CACHE_EXPIRATION_TIME=600;

PAGING_CACHE_ENABLE

Specify whether to globally disable the Paging Cache feature. Valid values:

• true (default): Enable.

• false: Disable.

SET ADB_CONFIG PAGING_CACHE_ENABLE=false;

Specify table engine

RC_DDL_ENGINE_REWRITE_XUANWUV2

Globally specify the table engine for the cluster. Valid values:

• true (default): The table engines of existing tables remain unchanged. The default table engine for new tables is XUANWU_V2. Even if you explicitly specify ENGINE=XUANWU when creating a new table, it is automatically rewritten to XUANWU_V2.

• false: The table engines of existing tables remain unchanged. The default table engine for new tables is XUANWU.

SET ADB_CONFIG RC_DDL_ENGINE_REWRITE_XUANWUV2=true;

Specify a table engine

Category

Parameter

Description

Example

Documentation Link

Query timeout period

QUERY_TIMEOUT

Configure the query timeout period for a single query at the query level. The value must be an integer greater than 0. Unit: milliseconds (ms).

/*+ QUERY_TIMEOUT=1000 */select count(*) from t;

Writes and queries

Timeout period for INSERT, UPDATE, and DELETE statements

INSERT_SELECT_TIMEOUT

Modify the maximum execution time for INSERT, UPDATE, and DELETE statements at the query level. Default value: 24×3600000. The value must be an integer greater than 0. Unit: milliseconds (ms).

/*+ INSERT_SELECT_TIMEOUT=3600000 */update customer set customer_name ='adb' where customer_id ='2369';

Limits

Disable filter condition pushdown

• Milvus version 3.1.4 and later: FILTER_NOT_PUSHDOWN_COLUMNS

• For Milvus versions earlier than 3.1.4, use NO_INDEX_COLUMNS.

Disable filter condition pushdown for specific fields at the query level.

• For Milvus version 3.1.4 and later: /*+ FILTER_NOT_PUSHDOWN_COLUMNS=[Schema1.table1:colName1|colName2;Schema2.table2:colName1|colName2] */

• For Milvus versions earlier than 3.1.4: /*+ NO_INDEX_COLUMNS=[tableName1.colName1;colName2,tableName2.colName1] */

Filter conditions without pushdown

Scheduling priority for BUILD jobs

BUILD_TASK_PRIORITY

Modify the scheduling priority for BUILD tasks. Currently, you can only use a hint to configure the BUILD scheduling priority for a single table. Default value: 0. The value must be an integer greater than or equal to 0. A larger value indicates a higher BUILD task scheduling priority.

/*+ BUILD_TASK_PRIORITY = 2*/ BUILD TABLE db.table;

Configure BUILD scheduling priority

Elastic import

• ELASTIC_LOAD

• ELASTIC_LOAD_CONFIGS

Select elastic import and configure the Job resource group to execute elastic import tasks.

/*+ ELASTIC_LOAD=true, ELASTIC_LOAD_CONFIGS=[adb.load.resource.group.name=resource_group]*/

SUBMIT JOB INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

• Use external tables to import data from OSS

• Use external tables to import data from MaxCompute

Adjust execution plan

• Milvus version 3.1.8 and later: O_CBO_RULE_SWAP_OUTER_JOIN

• Milvus version earlier than 3.1.8: LEFT_TO_RIGHT_ENABLED

Hash Join builds using the right table, and Left Join does not reorder the left and right tables. When the right table has a large data volume, this can lead to slow execution and excessive memory resource consumption. Add a hint to instruct the optimizer to convert Left Join to Right Join based on resource consumption. Valid values:

• true: Enabled.

• false (default)

• /*+ O_CBO_RULE_SWAP_OUTER_JOIN=true*/

• /*+ LEFT_TO_RIGHT_ENABLED=true*/

Change LEFT JOIN to RIGHT JOIN

REORDER_JOINS

AnalyticDB for MySQL enables automatic Join order adjustment by default. You can manually adjust the Join order based on query data characteristics, allowing queries to execute directly according to the Join order in the SQL statement. Valid values:

• true (default): Enables the automatic join order adjustment feature.

• false: Disables the automatic join order adjustment feature.

/*+ REORDER_JOINS=false*/

Manually adjust join order

AGGREGATION_PATH_TYPE

If you use a two-step aggregation method and the number of groups in grouping and aggregation is large, it consumes many compute resources. Use this hint to skip partial aggregation and directly perform final aggregation. Valid values:

• single_agg: Skip partial aggregation.

• auto (default): The optimizer automatically determines whether to skip partial aggregation.

/*+ AGGREGATION_PATH_TYPE=single_agg*/

Grouping and aggregation query optimization

Adjust runtime policy

HASH_PARTITION_COUNT

Each compute task is split into multiple concurrent tasks for execution, fully utilizing compute resources. Use this hint to set the concurrency. Default value: 32. The value must be an integer greater than 0.

/*+ HASH_PARTITION_COUNT=32*/

None

TASK_WRITER_COUNT

The concurrency for INSERT INTO SELECT import tasks. If the worker load is too high, modify this hint to reduce the concurrency. Default value: 16. The value must be an integer greater than 0.

/*+ TASK_WRITER_COUNT=8*/

CTE_EXECUTION_MODE

Specify whether to enable CTE execution optimization. When CTE execution optimization is enabled, if a CTE subquery is referenced multiple times, it executes only once, improving the query performance for some queries. Valid values:

• shared: Enable the CTE execution optimization feature.

• inline (default): Disable the CTE execution optimization feature.

/*+ CTE_EXECUTION_MODE=shared*/

WITH

Query condition limits

QUERY_MAX_SHUFFLE_DATA_SIZE_MB

The shuffle data volume for a query. By default, no error is triggered regardless of the query data volume. If this parameter is set and the query data volume exceeds the specified threshold, the system directly terminates the query and reports an error. The value must be an integer greater than 0. Unit: MB.

/*+ QUERY_MAX_SHUFFLE_DATA_SIZE_MB=1024*/

None

MAX_SELECT_ITEMS_COUNT

Set the limit on the number of SELECT conditions. Default value: 1024. The value must be an integer greater than 0.

/*+ MAX_SELECT_ITEMS_COUNT=2048*/

MAX_IN_ITEMS_COUNT

Set the limit on the number of IN conditions. Default value: 4000. The value must be an integer greater than 0.

/*+ MAX_IN_ITEMS_COUNT=1000*/

MAX_WHERE_ITEMS_COUNT

Set the limit on the number of WHERE conditions. Default value: 256. The value must be an integer greater than 0.

/*+ MAX_WHERE_ITEMS_COUNT=512*/

Priority of XIHE BSP jobs

QUERY_PRIORITY

The priority of a BSP job. Valid values:

• HIGH

• NORMAL (default)

• LOW

• LOWEST

/*+ QUERY_PRIORITY=HIGH*/

Configuration Item List

Maximum ACUs for XIHE BSP jobs

ELASTIC_JOB_MAX_ACU

The maximum number of ACUs used by a single XIHE BSP job. The default value is 9, the minimum value is 3, and the valid range is [3, maximum resource amount of the Job-type resource group].

The value is [3, the maximum resource amount for a job-type resource group].

/*+ ELASTIC_JOB_MAX_ACU=20*/

Timeout period for XIHE BSP job queries

BATCH_QUERY_TIMEOUT

The timeout period for XIHE BSP jobs. Default value: 7200000 ms. The value must be an integer greater than 0. Unit: milliseconds (ms).

/*+ BATCH_QUERY_TIMEOUT=3600000*/

OSS external table

OUTPUT_FILENAME

When exporting data using an OSS external table, add this hint before the export statement to customize the file name stored in OSS.

/*+ OUTPUT_FILENAME=output.csv*/

Export to OSS

SQL_OUTPUT_OSS_FILE_HEAD_ENABLE

When exporting data using an OSS external table, the data files exported to OSS do not include column names by default; they only contain data. If you want to export files with column names, add this hint before the import statement. Valid values:

• true: Yes.

• false (default value): No.

/*+ SQL_OUTPUT_OSS_FILE_HEAD_ENABLE=true*/

HIVE_SPLIT_ASYNC_GENERATION_ENABLED

When Hive generates an execution plan, specify whether to asynchronously submit Hive Split tasks to the queue for execution. Valid values:

• true: Yes.

• false (default value): No.

/*+ HIVE_SPLIT_ASYNC_GENERATION_ENABLED=true*/

Import OSS data using external tables

FAST_PARQUET_READER_ENABLE

When the statistics information in Parquet files is incorrect,

Specifies whether to use the native Parquet reader as a fallback.

• true (default)

• false: No.

/*+ FAST_PARQUET_READER_ENABLE=true*/

OSS_VECTORIZED_PARQUET_READER_ENABLE

If the data contains no NULL values, specify whether to enable vectorized Parquet reading. Valid values:

• true: Yes, it is.

• false (default value): No.

/*+ OSS_VECTORIZED_PARQUET_READER_ENABLE=true*/

OSS_VECTORIZED_PARQUET_READER_NULL_SCENE_ENABLE

If the data contains NULL values, specify whether to enable vectorized Parquet reading. Valid values:

• true: Yes.

• false (default value): No.

/*+ OSS_VECTORIZED_PARQUET_READER_NULL_SCENE_ENABLE=true*/

HUDI_METADATA_TABLE_ENABLED

Specify whether to load Hudi metadata. Valid values:

• true: Yes, it is.

• false (default value): No.

/*+ HUDI_METADATA_TABLE_ENABLED=true*/

HUDI_QUERY_TYPE

Set the method for querying Hudi tables. Valid values:

• snapshot: Query all data submitted or compressed at a specific time.

• read_optimized (default): Query data after the most recent submission or compression.

/*+ HUDI_QUERY_TYPE=snapshot*/

HUDI_REALTIME_SKIP_MERGE

When querying Hudi table data in snapshot mode, specify whether to skip merging records from base files and log files. Valid values:

• true: Yes.

• false (default): Disabled.

/*+ HUDI_REALTIME_SKIP_MERGE=true*/

HUDI_MAX_SPLIT_SIZE=

The maximum split size. Default value: 128. Unit: MB.

/*+ HUDI_MAX_SPLIT_SIZE=128*/

HUDI_SPLIT_PARALLEL_GENERATION_ENABLED

Specify whether to generate splits in parallel. Valid values:

• true (default)

• false: No.

/*+ HUDI_SPLIT_PARALLEL_GENERATION_ENABLED=true*/

HUDI_DATA_SKIPPING_ENABLED

When reading data, use statistics from the Hudi MetaTable to skip unnecessary shards, improving query performance. Valid values:

• true: Yes.

• false (default value): No.

/*+ HUDI_DATA_SKIPPING_ENABLED=true*/

HUDI_SPLIT_ASYNC_GENERATION_ENABLED

During the execution plan generation phase, specify whether to asynchronously generate Hudi splits. Valid values:

• true (default)

• false: No.

/*+ HUDI_SPLIT_ASYNC_GENERATION_ENABLED=true*/

ApsaraDB RDS for MySQL external tables

JDBC_SCAN_SPLITS

The number of splits during a Java Database Connectivity (JDBC)-based TableScan. Default value: 1. Valid values: 1 to 100.

/*+ JDBC_SCAN_SPLITS=1*/

Use external tables to import data from RDS for MySQL

JDBC_SPLIT_COLUMN

The column used to split when reading an ApsaraDB RDS for MySQL external table.

/*+ JDBC_SPLIT_COLUMN=column1*/

JDBC_INSERT_DIRECTLY

When writing data to an ApsaraDB RDS for MySQL external table, data is written to a temporary table first, then to the target table by default. You can modify the hint to write data directly to the target table. However, any errors during the write process may result in dirty data in the target table. Valid values:

• true: Yes.

• false (default value): Disabled.

/*+ JDBC_INSERT_DIRECTLY=true*/

Tablestore external tables

OTS-INSERT-AS-UPDATE

Specify whether to use INSERT statements instead of UPDATE statements. Valid values:

• true: Yes.

• false (default): No.

/*+ OTS-INSERT-AS-UPDATE=true*/

Query and import Tablestore data

MaxCompute external tables

ODPS_SPLIT_LIMIT

The maximum number of splits when reading data via Tunnel. Default value: 1000. Valid values: 1 to 1000.

/*+ ODPS_SPLIT_LIMIT=1000*/

Import MaxCompute data using external tables

IGNORE_PARTITION_CACHE

When executing a query with partition conditions, query partition metadata directly from the MaxCompute table instead of using cached partition metadata.

• true: Yes.

• false (default)

/*+ IGNORE_PARTITION_CACHE=true*/

ROWS_PER_SPLIT

The maximum number of data rows a single split can read when reading data via Tunnel. Default value: 500000. Valid values: 10000 to 500000.

/*+ ROWS_PER_SPLIT=500000*/

STORAGE_API_ENABLED

Specifies whether to use the MaxCompute Storage API to read MaxCompute data. The MaxCompute Storage API greatly improves read performance compared with the traditional Tunnel service.

Value:

• true: Yes.

• false (default value): No.

/*+ STORAGE_API_ENABLED=true*/

APLIT_BYTE_SIZE

When reading data via MaxCompute Storage API, the maximum size of a single split. Default value: 256. Unit: MB.

/*+ APLIT_BYTE_SIZE=256*/

MAX_BATCH_ROW_COUNT

When reading data via MaxCompute Storage API, the maximum number of rows in a single split. Default value: 1024.

/*+ MAX_BATCH_ROW_COUNT=1024*/

PAGE_BATCH_SIZE

When reading data via MaxCompute Storage API, the maximum number of rows each page contains. Default value: 1024.

/*+ PAGE_BATCH_SIZE=1024*/

MAX_ALLOCATION_PER_SPLIT

When reading data via MaxCompute Storage API, the maximum peak memory allocated at the split level. Default value: 300 MB. Unit: MB.

/*+ MAX_ALLOCATION_PER_SPLIT=300*/

ASYNC_GET_SPLIT

When MaxCompute generates an execution plan, specify whether to optimize split loading performance by asynchronously submitting tasks to the queue for execution. Valid values:

• true: Yes.

• The default value is false, indicating no.

/*+ ASYNC_GET_SPLIT=true*/

Scan concurrency control

SPLIT_FLOW_CONTROL_ENABLED

AnalyticDB for MySQL supports configuring scan concurrency quotas. This prevents node instability caused by excessive scan concurrency and resource consumption during queries. Valid values:

• true (default): Enable the scan concurrency control feature.

• false: Disable the scan concurrency control feature.

/*+ SPLIT_FLOW_CONTROL_ENABLED=true*/ SELECT * FROM table;

Scan Concurrency Control

MIN_RUNNING_SPLITS_LIMIT_PER_TASK

The minimum scan concurrency quota for tasks. Default value: 1. Valid values: [1, TARGET_RUNNING_SPLITS_LIMIT_PER_TASK].

/*+ MIN_RUNNING_SPLITS_LIMIT_PER_TASK=10*/SELECT * FROM orders;

TARGET_RUNNING_SPLITS_LIMIT_PER_TASK

The intermediate scan concurrency quota for tasks. Increase or decrease the scan concurrency quota based on this value. Default value: 32. Valid values: [MIN_RUNNING_SPLITS_LIMIT_PER_TASK, MAX_RUNNING_SPLITS_LIMIT_PER_TASK].

/*+ TARGET_RUNNING_SPLITS_LIMIT_PER_TASK=32*/SELECT * FROM adb_test;

MAX_RUNNING_SPLITS_LIMIT_PER_TASK

The maximum scan concurrency quota for tasks. Default value: 64. The value must be greater than TARGET_RUNNING_SPLITS_LIMIT_PER_TASK.

/*+ MAX_RUNNING_SPLITS_LIMIT_PER_TASK=100*/SELECT * FROM adb_test;

Paging cache (performance optimization for deep paged queries)

PAGING_ID

Enable the Paging Cache feature and manually specify paging_id to identify a set of related paged query caches (i.e., a set of paged queries with the same pattern, differing only in LIMIT and OFFSET parameters).

/*PAGING_ID=paging123*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;

Paging cache (performance optimization for deep paged queries)

PAGING_CACHE_ENABLED

Enable the Paging Cache feature and automatically generate paging_id based on the SQL pattern to identify a set of related paged query caches (i.e., a set of paged queries with the same pattern, differing only in LIMIT and OFFSET parameters). Valid values:

• true: Enable the Paging Cache feature.

• false (default): Disable the Paging Cache feature.

/*PAGING_CACHE_ENABLED=true*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;

PAGING_CACHE_VALIDITY_INTERVAL

Set the cache validity period. After this period, the cache becomes invalid. If the cache is not accessed within one hour after invalidation, the system automatically deletes the corresponding temporary cache table. The value must be an integer greater than 0. Unit: seconds (s). Use this parameter with paging_cache_enabled.

/*PAGING_CACHE_ENABLED=true,PAGING_CACHE_VALIDITY_INTERVAL=300*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;

INVALIDATE_PAGING_CACHE

Specify whether to clear the paged query cache for a specified pattern. Valid values:

• true: Yes.

• false (default value): No.

This parameter must be used together with the paging_id or paging_cache_enabled parameter.

/*PAGING_CACHE_ENABLED=true,INVALIDATE_PAGING_CACHE=true*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;

Specify table engine

RC_DDL_ENGINE_REWRITE_XUANWUV2

Specify the table engine for the table created by the current SQL statement. Valid values:

• true (default): The table engines of existing tables remain unchanged. The default table engine for the table created by the current SQL statement is XUANWU_V2. Even if you explicitly specify ENGINE=XUANWU when creating this table, it is automatically rewritten to XUANWU_V2.

• false: The table engines of existing tables remain unchanged. The default table engine for the table created by the current SQL statement is XUANWU.

/*+ RC_DDL_ENGINE_REWRITE_XUANWUV2=true */

Specify table engine

Others

RESOURCE_GROUP

Specify the resource group used by the query. If you do not specify a resource group, the resource group bound to the database account is used by default. If the database account is not bound to a resource group, the default resource group is used.

/*+ RESOURCE_GROUP=my_group_name*/

None

DML_EXCEPTION_TO_NULL_ENABLED

If invalid values appear in data written by an INSERT SELECT statement, specify whether to throw an exception. Valid values:

• true: Do not throw an exception; write NULL values.

• false (default): Throw an exception.

/*+ DML_EXCEPTION_TO_NULL_ENABLED=true*/

DISPLAY_HINT

Used to mark corresponding queries, such as those from different business parties, for easy identification.

/*+ DISPLAY_HINT=from_user_123*/