This is an automated email from the ASF dual-hosted git repository.
yashmayya pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/pinot.git
The following commit(s) were added to refs/heads/master by this push:
new f865accd778 Support forcing colocated set operations to avoid data
shuffle using query hint (#18804)
f865accd778 is described below
commit f865accd77820505953cfbb106523b34ef5c9a5e
Author: Yash Mayya <[email protected]>
AuthorDate: Thu Jul 16 11:11:33 2026 -0700
Support forcing colocated set operations to avoid data shuffle using query
hint (#18804)
---
.../pinot/calcite/rel/hint/PinotHintOptions.java | 28 +++++
.../calcite/rel/hint/PinotHintStrategyTable.java | 1 +
.../rules/PinotSetOpExchangeNodeInsertRule.java | 32 +++++-
.../apache/pinot/query/QueryCompilationTest.java | 115 +++++++++++++++++++++
.../resources/queries/ExplainPhysicalPlans.json | 42 ++++++++
.../src/test/resources/queries/QueryHints.json | 25 +++++
6 files changed, 242 insertions(+), 1 deletion(-)
diff --git
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/hint/PinotHintOptions.java
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/hint/PinotHintOptions.java
index 05258e3b000..dcdf30d6235 100644
---
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/hint/PinotHintOptions.java
+++
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/hint/PinotHintOptions.java
@@ -18,6 +18,7 @@
*/
package org.apache.pinot.calcite.rel.hint;
+import java.util.List;
import java.util.Map;
import javax.annotation.Nullable;
import org.apache.calcite.rel.RelDistribution;
@@ -42,6 +43,7 @@ public class PinotHintOptions {
public static final String JOIN_HINT_OPTIONS = "joinOptions";
public static final String TABLE_HINT_OPTIONS = "tableOptions";
public static final String WINDOW_HINT_OPTIONS = "windowOptions";
+ public static final String SET_OP_HINT_OPTIONS = "setOpOptions";
public static class AggregateOptions {
public static final String IS_PARTITIONED_BY_GROUP_BY_KEYS =
"is_partitioned_by_group_by_keys";
@@ -91,6 +93,32 @@ public class PinotHintOptions {
}
}
+ /// Hint options for set operations (UNION / UNION ALL / INTERSECT / EXCEPT).
+ public static class SetOpHintOptions {
+ /// Forces (or disables) a colocated, pre-partitioned exchange on every
input of a set operation
+ /// (UNION / UNION ALL / INTERSECT / EXCEPT), so the inputs are processed
in place without a network shuffle.
+ ///
+ /// This is opt-in and honored only by the default (V1) query planner; the
V2 physical planner determines
+ /// colocation on its own. Like the join hint {@link
JoinHintOptions#IS_COLOCATED_BY_JOIN_KEYS}, it trusts the user:
+ /// {@code 'true'} forces a pre-partitioned exchange and bypasses the
planner's automatic detection, while
+ /// {@code 'false'} forces a normal shuffle even when the planner would
otherwise detect pre-partitioning. A set
+ /// operation matches rows on the entire output row, so forcing {@code
'true'} is only correct when all inputs are
+ /// partitioned the same way (same partition function and count) on one or
more of the projected columns, so that
+ /// rows which are equal across all projected columns are guaranteed to
land on the same worker. Forcing it when
+ /// that does not hold silently produces wrong results for INTERSECT,
EXCEPT and distinct UNION; UNION ALL only
+ /// concatenates and is unaffected.
+ public static final String IS_COLOCATED_BY_SET_OP_KEYS =
"is_colocated_by_set_op_keys";
+
+ /// Reads the hint from a hint list. Unlike {@link
JoinHintOptions#isColocatedByJoinKeys}, this takes the hint list
+ /// directly because the hint can attach either to the set operation
itself or to one of its inputs (see
+ /// {@code PinotSetOpExchangeNodeInsertRule}), and the caller resolves
which one applies.
+ @Nullable
+ public static Boolean isColocatedBySetOpKeys(List<RelHint> hints) {
+ String hint = PinotHintStrategyTable.getHintOption(hints,
SET_OP_HINT_OPTIONS, IS_COLOCATED_BY_SET_OP_KEYS);
+ return hint != null ? Boolean.parseBoolean(hint) : null;
+ }
+ }
+
public static class JoinHintOptions {
public static final String JOIN_STRATEGY = "join_strategy";
// "hash" is the default strategy for non-SEMI joins
diff --git
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/hint/PinotHintStrategyTable.java
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/hint/PinotHintStrategyTable.java
index 8837d1d15ce..79dc6e5ca2c 100644
---
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/hint/PinotHintStrategyTable.java
+++
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/hint/PinotHintStrategyTable.java
@@ -41,6 +41,7 @@ public class PinotHintStrategyTable {
.hintStrategy(PinotHintOptions.JOIN_HINT_OPTIONS, HintPredicates.JOIN)
.hintStrategy(PinotHintOptions.TABLE_HINT_OPTIONS,
HintPredicates.TABLE_SCAN)
.hintStrategy(PinotHintOptions.WINDOW_HINT_OPTIONS,
HintPredicates.WINDOW)
+ .hintStrategy(PinotHintOptions.SET_OP_HINT_OPTIONS, HintPredicates.SETOP)
.build();
/**
diff --git
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotSetOpExchangeNodeInsertRule.java
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotSetOpExchangeNodeInsertRule.java
index 3db58c048c0..7ecfac362f0 100644
---
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotSetOpExchangeNodeInsertRule.java
+++
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotSetOpExchangeNodeInsertRule.java
@@ -20,13 +20,16 @@ package org.apache.pinot.calcite.rel.rules;
import java.util.ArrayList;
import java.util.List;
+import javax.annotation.Nullable;
import org.apache.calcite.plan.RelOptRule;
import org.apache.calcite.plan.RelOptRuleCall;
import org.apache.calcite.rel.RelDistributions;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.SetOp;
+import org.apache.calcite.rel.hint.Hintable;
import org.apache.calcite.tools.RelBuilderFactory;
import org.apache.calcite.util.ImmutableIntList;
+import org.apache.pinot.calcite.rel.hint.PinotHintOptions;
import org.apache.pinot.calcite.rel.logical.PinotLogicalExchange;
@@ -51,12 +54,39 @@ public class PinotSetOpExchangeNodeInsertRule extends
RelOptRule {
public void onMatch(RelOptRuleCall call) {
SetOp setOp = call.rel(0);
List<RelNode> inputs = setOp.getInputs();
+ // When the colocation hint is set, force a pre-partitioned (direct,
no-shuffle) exchange on every input; otherwise
+ // leave it null so the planner auto-detects pre-partitioning from the
inputs' distribution. See
+ // PinotHintOptions.SetOpHintOptions.IS_COLOCATED_BY_SET_OP_KEYS for the
correctness contract.
+ Boolean prePartitioned = resolveColocationHint(setOp);
List<RelNode> newInputs = new ArrayList<>(inputs.size());
for (RelNode input : inputs) {
RelNode exchange = PinotLogicalExchange.create(input,
- RelDistributions.hash(ImmutableIntList.range(0,
setOp.getRowType().getFieldCount())));
+ RelDistributions.hash(ImmutableIntList.range(0,
setOp.getRowType().getFieldCount())), prePartitioned);
newInputs.add(exchange);
}
call.transformTo(setOp.copy(setOp.getTraitSet(), newInputs));
}
+
+ /// Resolves the colocation hint for a set operation. Calcite attaches the
hint to the set operation itself only when
+ /// the query wraps it in an outer {@code SELECT} that carries the hint; in
the natural inline form (the hint on the
+ /// first {@code SELECT} of a {@code UNION}/{@code INTERSECT}/{@code
EXCEPT}) it lands on the first branch instead.
+ /// Precedence: the set operation's own hint wins, otherwise the first input
carrying the hint wins. The resolved
+ /// value is applied to all inputs, so conflicting per-branch values are not
supported.
+ @Nullable
+ private static Boolean resolveColocationHint(SetOp setOp) {
+ Boolean fromSetOp =
PinotHintOptions.SetOpHintOptions.isColocatedBySetOpKeys(setOp.getHints());
+ if (fromSetOp != null) {
+ return fromSetOp;
+ }
+ for (RelNode input : setOp.getInputs()) {
+ RelNode unboxed = PinotRuleUtils.unboxRel(input);
+ if (unboxed instanceof Hintable) {
+ Boolean fromInput =
PinotHintOptions.SetOpHintOptions.isColocatedBySetOpKeys(((Hintable)
unboxed).getHints());
+ if (fromInput != null) {
+ return fromInput;
+ }
+ }
+ }
+ return null;
+ }
}
diff --git
a/pinot-query-planner/src/test/java/org/apache/pinot/query/QueryCompilationTest.java
b/pinot-query-planner/src/test/java/org/apache/pinot/query/QueryCompilationTest.java
index 1bf31abd431..1512b0e5558 100644
---
a/pinot-query-planner/src/test/java/org/apache/pinot/query/QueryCompilationTest.java
+++
b/pinot-query-planner/src/test/java/org/apache/pinot/query/QueryCompilationTest.java
@@ -41,6 +41,7 @@ import
org.apache.pinot.query.planner.plannode.MailboxReceiveNode;
import org.apache.pinot.query.planner.plannode.MailboxSendNode;
import org.apache.pinot.query.planner.plannode.PlanNode;
import org.apache.pinot.query.planner.plannode.ProjectNode;
+import org.apache.pinot.query.planner.plannode.SetOpNode;
import org.apache.pinot.query.planner.plannode.WindowNode;
import org.apache.pinot.query.routing.QueryServerInstance;
import org.testng.annotations.DataProvider;
@@ -1052,6 +1053,120 @@ public class QueryCompilationTest extends
QueryEnvironmentTestBase {
return (MailboxSendNode) senderRoot;
}
+ /// The {@code setOpOptions(is_colocated_by_set_op_keys='true')} hint forces
a pre-partitioned (direct) exchange on
+ /// every input of a set operation, avoiding the shuffle. Here the inputs
project {@code col3}, which is neither
+ /// table's partition column, so without the hint the planner would shuffle.
Covers UNION ALL, INTERSECT and EXCEPT,
+ /// which all share {@link
org.apache.pinot.calcite.rel.rules.PinotSetOpExchangeNodeInsertRule}.
+ @Test(dataProvider = "setOpColocationHintQueries")
+ public void testSetOpColocationHintForcesPrePartitionedExchange(String
query) {
+ List<MailboxSendNode> sendNodes =
findSetOpInputSendNodes(_queryEnvironment.planQuery(query));
+ for (MailboxSendNode sendNode : sendNodes) {
+ assertEquals(sendNode.getDistributionType(),
RelDistribution.Type.HASH_DISTRIBUTED);
+ assertTrue(sendNode.isPrePartitioned(),
+ "setOpOptions(is_colocated_by_set_op_keys='true') should force a
pre-partitioned exchange on every input");
+ }
+ }
+
+ @DataProvider(name = "setOpColocationHintQueries")
+ private Object[][] setOpColocationHintQueries() {
+ String hint = "/*+ setOpOptions(is_colocated_by_set_op_keys='true') */";
+ return new Object[][]{
+ {"SELECT " + hint + " col3 FROM a UNION ALL SELECT col3 FROM b"},
+ {"SELECT " + hint + " col3 FROM a INTERSECT SELECT col3 FROM b"},
+ {"SELECT " + hint + " col3 FROM a EXCEPT SELECT col3 FROM b"},
+ };
+ }
+
+ /// The hint also lands on the set operation when it is wrapped in an outer
{@code SELECT} that carries the hint (the
+ /// hint then attaches to the set operation directly rather than to a
branch).
+ @Test
+ public void testSetOpColocationHintViaOuterSelectWrap() {
+ String query = "SELECT /*+
setOpOptions(is_colocated_by_set_op_keys='true') */ * FROM "
+ + "(SELECT col3 FROM a UNION ALL SELECT col3 FROM b)";
+ for (MailboxSendNode sendNode :
findSetOpInputSendNodes(_queryEnvironment.planQuery(query))) {
+ assertTrue(sendNode.isPrePartitioned(), "Hint on the wrapping SELECT
should force a pre-partitioned exchange");
+ }
+ }
+
+ /// When branches carry conflicting hints, the first input that specifies
the hint wins and its value is applied to
+ /// all inputs (the documented precedence of the rule). Here the first
branch forces it on, so both branches are
+ /// pre-partitioned even though the second branch sets it to {@code 'false'}.
+ @Test
+ public void testSetOpColocationHintFirstInputWins() {
+ String query = "SELECT /*+
setOpOptions(is_colocated_by_set_op_keys='true') */ col3 FROM a "
+ + "UNION ALL SELECT /*+
setOpOptions(is_colocated_by_set_op_keys='false') */ col3 FROM a";
+ for (MailboxSendNode sendNode :
findSetOpInputSendNodes(_queryEnvironment.planQuery(query))) {
+ assertTrue(sendNode.isPrePartitioned(),
+ "The first input's hint value should win and apply to all inputs
when branches conflict");
+ }
+ }
+
+ /// Without the hint and with inputs that are not partitioned by the
projected column, the exchanges below the set
+ /// operation must be regular (shuffled) exchanges.
+ @Test
+ public void testSetOpWithoutHintIsNotPrePartitioned() {
+ String query = "SELECT col3 FROM a UNION ALL SELECT col3 FROM b";
+ for (MailboxSendNode sendNode :
findSetOpInputSendNodes(_queryEnvironment.planQuery(query))) {
+ assertFalse(sendNode.isPrePartitioned(),
+ "Without the hint and matching partitioning, the set op exchanges
should be a full shuffle");
+ }
+ }
+
+ /// When each input is declared partitioned by the projected column, the
single-column set-op exchange matches the
+ /// input partitioning, so the planner auto-detects a pre-partitioned
exchange even without the hint. This is the
+ /// baseline that {@link #testSetOpColocationHintFalseDisablesAutoDetected}
overrides.
+ @Test
+ public void testSetOpAutoDetectsPrePartitioningWithoutHint() {
+ for (MailboxSendNode sendNode :
findSetOpInputSendNodes(_queryEnvironment.planQuery(AUTO_DETECTED_SET_OP))) {
+ assertTrue(sendNode.isPrePartitioned(),
+ "A single-column set op over each table's partition column should
auto-detect a pre-partitioned exchange");
+ }
+ }
+
+ /// Setting the hint to {@code 'false'} overrides the planner's automatic
detection of pre-partitioning (see
+ /// {@link #testSetOpAutoDetectsPrePartitioningWithoutHint} for the same
query without the hint).
+ @Test
+ public void testSetOpColocationHintFalseDisablesAutoDetected() {
+ String query = AUTO_DETECTED_SET_OP.replaceFirst("SELECT",
+ "SELECT /*+ setOpOptions(is_colocated_by_set_op_keys='false') */");
+ for (MailboxSendNode sendNode :
findSetOpInputSendNodes(_queryEnvironment.planQuery(query))) {
+ assertFalse(sendNode.isPrePartitioned(),
+ "setOpOptions(is_colocated_by_set_op_keys='false') should disable
auto-detected pre-partitioning");
+ }
+ }
+
+ // A set op whose inputs are each declared partitioned by the (single)
projected column, so the exchange below the set
+ // op matches the input partitioning and the planner auto-detects
pre-partitioning.
+ private static final String AUTO_DETECTED_SET_OP =
+ "SELECT col2 FROM a /*+ tableOptions(partition_function='hashcode',
partition_key='col2', partition_size='4') */ "
+ + "UNION ALL "
+ + "SELECT col1 FROM b /*+
tableOptions(partition_function='hashcode', partition_key='col1', "
+ + "partition_size='4') */";
+
+ /// Finds the {@link MailboxSendNode}s feeding each input exchange of the
(single) set-op stage. A set operation has
+ /// one mailbox exchange per branch, and the {@code prePartitioned} flag
lives on each branch's send node.
+ private List<MailboxSendNode> findSetOpInputSendNodes(DispatchableSubPlan
dispatchableSubPlan) {
+ SetOpNode setOpNode = null;
+ for (DispatchablePlanFragment fragment :
dispatchableSubPlan.getQueryStages()) {
+ setOpNode = findNodeOfType(fragment.getPlanFragment().getFragmentRoot(),
SetOpNode.class);
+ if (setOpNode != null) {
+ break;
+ }
+ }
+ assertNotNull(setOpNode, "Expected a SetOp node in the plan");
+ List<MailboxSendNode> sendNodes = new ArrayList<>();
+ for (PlanNode input : setOpNode.getInputs()) {
+ assertTrue(input instanceof MailboxReceiveNode, "Each SetOp input should
be a mailbox exchange");
+ int senderStageId = ((MailboxReceiveNode) input).getSenderStageId();
+ PlanNode senderRoot =
+
dispatchableSubPlan.getQueryStageMap().get(senderStageId).getPlanFragment().getFragmentRoot();
+ assertTrue(senderRoot instanceof MailboxSendNode, "Sender fragment root
should be a MailboxSendNode");
+ sendNodes.add((MailboxSendNode) senderRoot);
+ }
+ assertFalse(sendNodes.isEmpty(), "Expected the SetOp to have input send
nodes");
+ return sendNodes;
+ }
+
/**
* Finds the DispatchablePlanFragment containing a JoinNode (non-leaf,
non-root stage).
*/
diff --git
a/pinot-query-planner/src/test/resources/queries/ExplainPhysicalPlans.json
b/pinot-query-planner/src/test/resources/queries/ExplainPhysicalPlans.json
index 641603e9468..20ef9aed09e 100644
--- a/pinot-query-planner/src/test/resources/queries/ExplainPhysicalPlans.json
+++ b/pinot-query-planner/src/test/resources/queries/ExplainPhysicalPlans.json
@@ -741,6 +741,48 @@
"\n └── [2]@localhost:1|[0] TABLE SCAN (a)
null",
"\n"
]
+ },
+ {
+ "description": "set op (UNION ALL) without the colocation hint
shuffles (hash redistributes) its inputs to all set-op-stage workers",
+ "sql": "EXPLAIN IMPLEMENTATION PLAN FOR SELECT col3 FROM a UNION ALL
SELECT col3 FROM a",
+ "output": [
+ "[0]@localhost:3|[0] MAIL_RECEIVE(BROADCAST_DISTRIBUTED)",
+ "\n├── [1]@localhost:2|[1]
MAIL_SEND(BROADCAST_DISTRIBUTED)->{[0]@localhost:3|[0]} (Subtree Omitted)",
+ "\n└── [1]@localhost:1|[0]
MAIL_SEND(BROADCAST_DISTRIBUTED)->{[0]@localhost:3|[0]}",
+ "\n └── [1]@localhost:1|[0] UNION_ALL",
+ "\n └── [1]@localhost:1|[0] MAIL_RECEIVE(HASH_DISTRIBUTED)",
+ "\n ├── [2]@localhost:2|[1]
MAIL_SEND(HASH_DISTRIBUTED)->{[1]@localhost:1|[0],[1]@localhost:2|[1]} (Subtree
Omitted)",
+ "\n └── [2]@localhost:1|[0]
MAIL_SEND(HASH_DISTRIBUTED)->{[1]@localhost:1|[0],[1]@localhost:2|[1]}",
+ "\n └── [2]@localhost:1|[0] PROJECT",
+ "\n └── [2]@localhost:1|[0] TABLE SCAN (a) null",
+ "\n └── [1]@localhost:1|[0] MAIL_RECEIVE(HASH_DISTRIBUTED)",
+ "\n ├── [3]@localhost:2|[1]
MAIL_SEND(HASH_DISTRIBUTED)->{[1]@localhost:1|[0],[1]@localhost:2|[1]} (Subtree
Omitted)",
+ "\n └── [3]@localhost:1|[0]
MAIL_SEND(HASH_DISTRIBUTED)->{[1]@localhost:1|[0],[1]@localhost:2|[1]}",
+ "\n └── [3]@localhost:1|[0] PROJECT",
+ "\n └── [3]@localhost:1|[0] TABLE SCAN (a) null",
+ "\n"
+ ]
+ },
+ {
+ "description": "set op (UNION ALL) with the
is_colocated_by_set_op_keys hint uses a pre-partitioned (direct, 1-to-1)
exchange on every input to avoid the shuffle",
+ "sql": "EXPLAIN IMPLEMENTATION PLAN FOR SELECT /*+
setOpOptions(is_colocated_by_set_op_keys='true') */ col3 FROM a UNION ALL
SELECT col3 FROM a",
+ "output": [
+ "[0]@localhost:3|[0] MAIL_RECEIVE(BROADCAST_DISTRIBUTED)",
+ "\n├── [1]@localhost:2|[1]
MAIL_SEND(BROADCAST_DISTRIBUTED)->{[0]@localhost:3|[0]} (Subtree Omitted)",
+ "\n└── [1]@localhost:1|[0]
MAIL_SEND(BROADCAST_DISTRIBUTED)->{[0]@localhost:3|[0]}",
+ "\n └── [1]@localhost:1|[0] UNION_ALL",
+ "\n └── [1]@localhost:1|[0] MAIL_RECEIVE(HASH_DISTRIBUTED)",
+ "\n ├── [2]@localhost:2|[1]
MAIL_SEND(HASH_DISTRIBUTED)[PARTITIONED]->{[1]@localhost:2|[1]} (Subtree
Omitted)",
+ "\n └── [2]@localhost:1|[0]
MAIL_SEND(HASH_DISTRIBUTED)[PARTITIONED]->{[1]@localhost:1|[0]}",
+ "\n └── [2]@localhost:1|[0] PROJECT",
+ "\n └── [2]@localhost:1|[0] TABLE SCAN (a) null",
+ "\n └── [1]@localhost:1|[0] MAIL_RECEIVE(HASH_DISTRIBUTED)",
+ "\n ├── [3]@localhost:2|[1]
MAIL_SEND(HASH_DISTRIBUTED)[PARTITIONED]->{[1]@localhost:2|[1]} (Subtree
Omitted)",
+ "\n └── [3]@localhost:1|[0]
MAIL_SEND(HASH_DISTRIBUTED)[PARTITIONED]->{[1]@localhost:1|[0]}",
+ "\n └── [3]@localhost:1|[0] PROJECT",
+ "\n └── [3]@localhost:1|[0] TABLE SCAN (a) null",
+ "\n"
+ ]
}
]
}
diff --git a/pinot-query-runtime/src/test/resources/queries/QueryHints.json
b/pinot-query-runtime/src/test/resources/queries/QueryHints.json
index 8b8bcd6f0bb..c8f872bd3ee 100644
--- a/pinot-query-runtime/src/test/resources/queries/QueryHints.json
+++ b/pinot-query-runtime/src/test/resources/queries/QueryHints.json
@@ -162,6 +162,27 @@
{
"description": "Colocated, Dynamic broadcast SEMI-JOIN with partially
empty right table result for some servers",
"sql": "SELECT /*+ joinOptions(join_strategy='dynamic_broadcast') */
{tbl1}.name, COUNT(*) FROM {tbl1} /*+
tableOptions(partition_function='hashcode', partition_key='num',
partition_size='4') */ WHERE {tbl1}.num IN (SELECT {tbl2}.num FROM {tbl2} /*+
tableOptions(partition_function='hashcode', partition_key='num',
partition_size='4') */ WHERE {tbl2}.val = 'z') GROUP BY {tbl1}.name"
+ },
+ {
+ "description": "INTERSECT on the partition column num with
is_colocated_by_set_op_keys hint forces a pre-partitioned (direct) exchange;
tbl1 and tbl2 are both partitioned by num so results stay correct",
+ "sql": "SELECT /*+ setOpOptions(is_colocated_by_set_op_keys='true') */
{tbl1}.num FROM {tbl1} INTERSECT SELECT {tbl2}.num FROM {tbl2}"
+ },
+ {
+ "description": "EXCEPT on the partition column num with
is_colocated_by_set_op_keys hint forces a pre-partitioned (direct) exchange;
tbl1 and tbl2 are both partitioned by num so results stay correct",
+ "sql": "SELECT /*+ setOpOptions(is_colocated_by_set_op_keys='true') */
{tbl1}.num FROM {tbl1} EXCEPT SELECT {tbl2}.num FROM {tbl2}"
+ },
+ {
+ "description": "UNION ALL on the partition column num with
is_colocated_by_set_op_keys hint forces a pre-partitioned (direct) exchange;
UNION ALL only concatenates so a direct exchange is always correct",
+ "sql": "SELECT /*+ setOpOptions(is_colocated_by_set_op_keys='true') */
{tbl1}.num FROM {tbl1} UNION ALL SELECT {tbl2}.num FROM {tbl2}"
+ },
+ {
+ "description": "Multi-column INTERSECT with
is_colocated_by_set_op_keys hint; both tables are partitioned by num, a subset
of the projected columns, so equal rows still colocate and results stay
correct. V1 only: the V2 optimizer derives set-op distributions itself and does
not yet handle multi-column set ops over subset-partitioned inputs",
+ "sql": "SELECT /*+ setOpOptions(is_colocated_by_set_op_keys='true') */
{tbl1}.num, {tbl1}.name FROM {tbl1} INTERSECT SELECT {tbl2}.num, {tbl2}.val
FROM {tbl2}",
+ "ignoreV2Optimizer": true
+ },
+ {
+ "description": "INTERSECT with is_colocated_by_set_op_keys='false'
disables auto-detected pre-partitioning and falls back to a shuffle; results
stay correct",
+ "sql": "SELECT /*+ setOpOptions(is_colocated_by_set_op_keys='false')
*/ {tbl1}.num FROM {tbl1} /*+ tableOptions(partition_function='hashcode',
partition_key='num', partition_size='4') */ INTERSECT SELECT {tbl2}.num FROM
{tbl2} /*+ tableOptions(partition_function='hashcode', partition_key='num',
partition_size='4') */"
}
]
},
@@ -239,6 +260,10 @@
{
"description": "Colocated, Dynamic broadcast SEMI-JOIN with partially
empty right table result for some servers",
"sql": "SELECT /*+ joinOptions(join_strategy='dynamic_broadcast') */
{tbl1}.name, COUNT(*) FROM {tbl1} /*+
tableOptions(partition_function='hashcode', partition_key='num',
partition_size='2') */ WHERE {tbl1}.num IN (SELECT {tbl2}.num FROM {tbl2} /*+
tableOptions(partition_function='hashcode', partition_key='num',
partition_size='4') */ WHERE {tbl2}.val = 'z') GROUP BY {tbl1}.name"
+ },
+ {
+ "description": "Forcing is_colocated_by_set_op_keys='true' when the
inputs have mismatched partition counts (tbl1=2, tbl2=4): the planner cannot
form a direct exchange, so it still shuffles and results stay correct",
+ "sql": "SELECT /*+ setOpOptions(is_colocated_by_set_op_keys='true') */
{tbl1}.num FROM {tbl1} /*+ tableOptions(partition_function='hashcode',
partition_key='num', partition_size='2') */ INTERSECT SELECT {tbl2}.num FROM
{tbl2} /*+ tableOptions(partition_function='hashcode', partition_key='num',
partition_size='4') */"
}
]
},
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
