Skip to main content
VeloDB Cloud 26.x is based on Apache Doris 4.x. See Version Mapping.

Control CBO Rules With Hints

Check whether a hint is needed​

  • You understand the basic workflow of the query optimizer.
  • You need to enable a specific CBO rule (such as aggregate pushdown) for a particular query.
  • Your role is a DBA or professional tuning engineer, not a general business developer.

Warning:

The optimizer selects plans automatically. A hint can bypass a cost-based choice and can make the query slower. Apply a hint only after a plan and profile identify a specific issue, then compare repeated executions before retaining the change.

Overview​

USE_CBO_RULE requests specified cost-based rewrite rules for one SQL statement. The rewrite must still match the query and produce a valid plan. Verify the resulting plan with EXPLAIN and compare execution with Query Profile.

When generating an execution plan, the query optimizer applies two categories of rules:

Optimization TypeFull NameDecision BasisTypical Strategies
RBORule-Based OptimizerPredefined heuristic rules, independent of statisticsPredicate pushdown, projection pushdown
CBOCost-Based OptimizerData statistics, estimating and choosing the plan with the lowest costAccess path selection, join algorithm selection

In some fine-grained tuning scenarios, DBAs or developers need to manually control whether a CBO rule is enabled. In such cases, you can use a query hint to do so.

Syntax​

Purpose: Request one or more CBO rules within a single SELECT statement.

Command:

SELECT /*+ USE_CBO_RULE(rule1, rule2, ...) */ ...
  • The hint immediately follows the SELECT keyword.
  • Inside the parentheses, list the rule names to enable, separated by commas if there are multiple.
  • Rule names are case-insensitive.

Supported CBO rules​

The following cost-based rewriting rules can be requested with USE_CBO_RULE:

Rule NameEffect
PUSH_DOWN_AGG_THROUGH_JOINPushes the aggregate operation down to both sides of the join
PUSH_DOWN_AGG_THROUGH_JOIN_ONE_SIDEPushes the aggregate operation down to one side of the join
PUSH_DOWN_DISTINCT_THROUGH_JOINPushes the DISTINCT operation down through the join
PUSH_DOWN_AGG_WITH_DISTINCT_THROUGH_JOIN_ONE_SIDEPushes an aggregate containing DISTINCT down to one side of the join

Example: Push aggregation below a join​

The example requires existing tables a(event_id, device_id) and b(group_id, device_id) with compatible join-key types.

Scenario: Table a is joined with table b on device_id, and the result is aggregated by event_id and group_id. You want to aggregate table a before the join to reduce the volume of data the join must process.

SQL example:

explain shape plan
select /*+ USE_CBO_RULE(push_down_agg_through_join_one_side) */
a.event_id,
b.group_id,
COUNT(a.event_id)
from a
join b on
a.device_id = b.device_id
group by
a.event_id,
b.group_id
;

Illustrative rewritten plan (the exact plan depends on the schema, statistics, and Core version):

PhysicalResultSink
--hashAgg[GLOBAL]
----hashAgg[LOCAL]
------hashJoin[INNER_JOIN] hashCondition=((a.device_id = b.device_id)) otherCondition=()
--------hashAgg[LOCAL]
----------PhysicalOlapScan[a]
--------PhysicalOlapScan[b]

You can see that an additional hashAgg[LOCAL] is placed above the scan of table a, performing aggregation before the join. This can reduce join input when many input rows share aggregation keys. Compare the actual row counts and query time before retaining the hint.

FAQ​

Q1: When do you need to use USE_CBO_RULE?

You only need to enable a rule manually when the query optimizer does not apply a particular CBO rule by default but you have determined that the rule would be beneficial given the current data distribution. In most cases, you should trust the optimizer's automatic decisions.

Q2: Are the rule names in the hint case-sensitive?

No, they are case-insensitive. PUSH_DOWN_AGG_THROUGH_JOIN_ONE_SIDE and push_down_agg_through_join_one_side are equivalent.

Q3: Can multiple rules be enabled at the same time?

Yes. Separate the rules with commas, for example: /*+ USE_CBO_RULE(rule1, rule2) */.

Q4: What happens if the hint is placed in the wrong location or the rule name is misspelled?

The hint must immediately follow the SELECT keyword. A misspelled rule name does not match a rewrite rule. Check the hint diagnostics and resulting plan rather than assuming the rewrite occurred.

RBO and CBO comparison​

DimensionRBOCBO
Decision basisHeuristic rulesData statistics (cost estimation)
Depends on statisticsNoYes
Applicable scenariosGeneral, deterministic optimizationOptimization tightly coupled with data distribution
Controllable by USE_CBO_RULENoYes

Summary​

Used appropriately, the USE_CBO_RULE hint lets you manually enable advanced CBO optimization rules in specific scenarios to improve query performance. Using it well requires a deep understanding of query optimization and data characteristics. In most cases, relying on the query optimizer's automatic decisions is still the best choice.