In OceanBase Database V4.6.0 and later, if the tenant has configured columnstore replicas (C replicas) and enabled columnstore replica auto-selection, controlled by variables such as ap_query_route_policy, the optimizer will use fixed rules to determine whether the read-only part of a statement will attempt to plan or execute on a C replica.
For details on system variable default values, enabling or disabling this feature, and read/write consistency settings, see Deploy and use columnstore replicas.
How to understand the judgment rules
You can understand the AP query routing judgment rules as two layers of constraints: basic conditions and policy branches:
First layer: Basic conditions
All basic conditions must be satisfied for the system to consider the columnar replica for automatic selection.
If any condition is not met, the system will not attempt to use the columnar path. This is different from
ap_query_route_policy = OFF, which completely disables columnar replica automatic selection. Not meeting the basic conditions means the SQL statement is not considered for columnar selection.Second layer: Policy branches (
ap_query_route_policy)After all basic conditions are met, the value of
ap_query_route_policy(set toFORCE,AUTO, orOFF) determines whether additional conditions such as weak reads, parallel execution, or cost thresholds are required:FORCE: If all basic conditions are met, the system will directly attempt to use the columnar replica plan without requiring weak reads, parallel execution, or cost thresholds.AUTO(default): If all basic conditions are met, the system will also require at least one of the AUTO conditions to be satisfied before attempting to use the columnar replica plan.OFF: Columnar replica automatic selection is disabled, and the system will follow the default row-based path planning.
In summary, basic conditions must be met first, and then the policy branch is considered. For AUTO, additional conditions must be met after the basic conditions. For FORCE, these additional conditions are not required after the basic conditions are met.
Basic conditions (all must be met)
The statement can only proceed to the FORCE or AUTO branches if all the following conditions are met:
| Condition | Description |
|---|---|
| Single-statement transaction | The statement runs in a single-statement transaction context. For example, an independent SELECT statement that has not been explicitly started with BEGIN or START TRANSACTION, or a single DML statement when autocommit is enabled. Statements within explicit transactions typically do not meet this condition and will not be considered for columnar automatic selection. |
| Tenant has columnar replicas | The tenant's LOCALITY configuration includes C replicas, and the columnar replicas are available for read operations (including readiness for row-to-column conversion and major compactions, as described in Deploy and Use Columnar Replicas). |
| Automatic selection is not disabled | The session/global ap_query_route_policy and any hints on the current SQL statement are not set to OFF. |
| Accessing user base tables | The execution plan involves at least one user base table. Statements that do not involve user base tables, such as SELECT 1 or SELECT NOW(), do not meet this condition. |
| Consistent with fallback policy | ap_query_replica_fallback controls whether to fall back to row-based replicas when columnar replicas are unavailable or no columnar plan can be generated. It is not a standalone condition for attempting columnar selection, but it should be considered alongside replica availability to avoid situations where columnar selection is expected but always falls back. |
AUTO conditions (any one is sufficient)
If ap_query_route_policy = AUTO and all basic conditions are met, the system will attempt to use the columnar replica plan if any of the following conditions is satisfied:
| Condition | Description |
|---|---|
| Weak read | The statement uses weak-consistency reads, such as when the session-level ob_read_consistency = 'weak'. For more information, see ob_read_consistency. |
| Parallel execution | The parallelism of the SELECT part of the plan is greater than 1 (including PARALLEL hints or optimizer auto-parallelism, as per the actual plan). |
| Cost exceeds threshold | In single-parallelism mode, the estimated cost of the SELECT part exceeds ap_query_cost_threshold. For statements with multiple SELECT fragments (such as parts of UNION ALL or nested subqueries), the cost accumulation rules apply as per the current version. |
When ap_query_route_policy = FORCE, the system does not require the weak read, parallelism, or cost conditions from the previous table. As long as all basic conditions are met, the system can attempt to use the columnar path if allowed.
How these settings interact
The following settings are directly relied upon or need to be considered together during judgment:
ap_query_route_policy: Determines whether to disable, use AUTO (with trigger conditions), or use FORCE (without trigger conditions).ob_read_consistency: Weak reads are a common trigger condition for AUTO.ap_query_cost_threshold: Used for the cost-exceeds-threshold trigger condition in AUTO.ap_query_replica_fallback: Does not determine whether to attempt columnar selection, but affects whether to fallback to row-based replicas when columnar replicas are unavailable or no columnar plan can be generated.
Session-level settings like ob_route_policy that are related to replica types can be referenced in ob_route_policy and Deploy and Use Columnar Replicas.
Scenarios where columnar automatic selection is not used
| Scenario | Description |
|---|---|
ap_query_route_policy = OFF |
Columnar replica path is not selected; the system follows the default row-based path planning. |
| Any basic condition is not met | For example, within an explicit transaction, no user tables, or no C replicas. |
AUTO and none of the three trigger conditions are met |
If the statement is not a weak read, does not use parallel execution, and the cost of the SELECT part does not exceed ap_query_cost_threshold, the system will typically follow the default row-based path planning (e.g., for simple point queries or queries with small result sets). |
Examples
FORCE: Columnstore access is enabled if the basic conditions are met
obclient> SET ap_query_route_policy = 'FORCE';
obclient> EXPLAIN SELECT * FROM t1 LIMIT 1;
If the plan contains a columnstore scan operator (such as COLUMN TABLE FULL SCAN), it indicates that the columnstore path was attempted.
AUTO: Columnstore path may be triggered when parallelism is greater than 1
obclient> SET ap_query_route_policy = 'AUTO';
obclient> EXPLAIN SELECT /*+parallel(4)*/ COUNT(*) FROM t1;
AUTO: Cost and threshold relationship
If the cost of the SELECT part of the query plan exceeds the value set for the ap_query_cost_threshold system variable (default value is 200000), the query is identified as an AP query. Lowering the ap_query_cost_threshold value makes it easier for complex queries to trigger columnstore plan retries; raising it keeps more queries in the rowstore plan.
- Example of setting a lower cost threshold:
obclient> SET ap_query_route_policy = 'AUTO';
-- Set the cost threshold to 50000.
obclient> SET ap_query_cost_threshold = 50000;
-- Complex queries (with cost exceeding the threshold) are automatically routed to the columnstore replica.
obclient> EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1 = t2.c1;
- Example of setting a higher cost threshold (cost does not exceed the threshold, so columnstore path is not triggered):
obclient> SET ap_query_route_policy = 'AUTO';
-- After raising the threshold: If the estimated cost of the SELECT part is **below** the new threshold, the cost does not exceed the threshold, and the columnstore path is not triggered.
obclient> SET ap_query_cost_threshold = 1000000;
obclient> EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1 = t2.c1;
If the plan still contains rowstore scan operators (such as TABLE FULL SCAN) and no columnstore scan operators are present, it indicates that the columnstore path was not triggered due to cost.
No user table: Basic conditions are not met
obclient> EXPLAIN SELECT 1;
Such plans typically do not involve user table access and are not included in the columnstore replica auto-selection process.