@Rocky can I specify the limit on number of scan partitions per table wise?

Yes, you can specify a limit on the number of partitions scanned per table, but it is managed via a system variable rather than a table property (metadata).

scan_olap_partition_num_limit

Introduced in v3.3.9, the system variable

scan_olap_partition_num_limit

allows you to restrict the number of partitions that can be scanned for a single table within a query's execution plan. If a table in the query exceeds this number, the query will fail. * Default:

0

(No limit). * Behavior: The limit is applied to each table individually within the execution plan. How to set the limit You can apply this limit at different levels depending on your needs: 1. Per Query (Using Hints) If you want to apply a limit only for a specific query (which is the closest way to achieve a "per-table-wise" control for a particular workload), you can use the

SET_VAR

hint:

sql
SELECT /*+ SET_VAR(scan_olap_partition_num_limit = 10) */ * 
FROM your_table 
WHERE date_col > '2023-01-01';

2. Per Session If you want all queries in your current connection to respect a certain partition limit:

sql
SET scan_olap_partition_num_limit = 50;

3. Globally To set a guardrail for the entire cluster:

sql
SET GLOBAL scan_olap_partition_num_limit = 100;

Important Notes * Not a Table Property: Currently, you cannot define this limit inside the

PROPERTIES

clause of a

CREATE TABLE

statement. It must be managed via session variables or hints. * Query Failure: If a query's partition pruning results in a number of partitions higher than this limit, StarRocks will return an error to prevent heavy resource consumption. * Version Requirement: Ensure you are on version v3.3.9 or later to use this specific variable. References • best_practices/query_tuning/query_hint.md • sql-reference/System_variable.md • knowledge/usage/best-practice.md

@Rocky can I control from table side instead of query side?

Currently, no, StarRocks does not support a "per-table" limit on scanned partitions via table properties (e.g., in the

PROPERTIES

clause of

CREATE TABLE

). This control is strictly implemented as a system variable (

scan_olap_partition_num_limit

) rather than a metadata attribute of the table itself. Why this is the case StarRocks separates table metadata (how data is stored) from query execution settings (how the engine handles the data). The limit on scanned partitions is considered a guardrail for the query engine to prevent expensive "full table scans" or broad range scans that could exhaust cluster resources. Alternative "Table-Like" Workarounds If your goal is to ensure that users of a specific table always respect a partition limit without them having to remember to set a variable, you can consider these approaches: 1. Global Default: If most of your large tables need this protection, you can set it globally for the entire cluster:

sql
    SET GLOBAL scan_olap_partition_num_limit = 50;

Note: This will apply to every query on every table in the cluster. 2. Resource Groups (Workload Management): While you can't limit partitions per table, you can use Resource Groups to limit the overall resource consumption (CPU/Memory) for specific users or workloads. This doesn't stop a many-partition scan, but it prevents that scan from starving other critical processes. 3. View Layer (Not Recommended for Hints): While you can create a View to simplify access, StarRocks does not currently support embedding

SET_VAR

hints inside a

CREATE VIEW

statement to force a session variable change for the underlying table. Summary of Limits Limit Type | Configuration Name | Scope Scanning (Max partitions per query) |

scan_olap_partition_num_limit

| Global or Session Variable Storage (Max total partitions allowed) |

max_partition_number_per_table

| FE Configuration (all tables) If you specifically need a table property for this, it would be a new feature request. For now, the

SET_VAR

hint in your query or setting it at the session level is the standard way to enforce this. References • administration/management/FE_configuration.md • best_practices/partitioning.md • sql-reference/System_variable.md • table_design/data_distribution/expression_partitioning.md • best_practices/query_tuning/schema_tuning.md