| N0 |
| SELECT /*+ disable_decorrelation */ (SELECT count(*) FROM ct_n1) FROM t_n1 |
| --- |
| Fragment#2 root |
| distribution: single |
| executionNodes: [N0] |
| exchangeSourceNodes: {4=[N1]} |
| colocationGroup[-1]: {nodes=[N0], sourceIds=[-1, 4], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| colocationGroup[4]: {nodes=[N0], sourceIds=[-1, 4], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| tree: |
| Receiver |
| fieldNames: [EXPR$0] |
| sourceFragmentId: 4 |
| est: (rows=1) |
| |
| Fragment#4 |
| distribution: single |
| executionNodes: [N1] |
| targetNodes: [N0] |
| exchangeSourceNodes: {3=[N1]} |
| colocationGroup[-1]: {nodes=[N1], sourceIds=[-1, 1, 3], assignments={part_0=N1:1}, partitionsWithConsistencyTokens={N1=[part_0:1]}} |
| colocationGroup[1]: {nodes=[N1], sourceIds=[-1, 1, 3], assignments={part_0=N1:1}, partitionsWithConsistencyTokens={N1=[part_0:1]}} |
| colocationGroup[3]: {nodes=[N1], sourceIds=[-1, 1, 3], assignments={part_0=N1:1}, partitionsWithConsistencyTokens={N1=[part_0:1]}} |
| partitions: [CT_N1=[N1={0}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 2 |
| est: (rows=1) |
| Project |
| fieldNames: [EXPR$0] |
| projection: [EXPR$0] |
| est: (rows=1) |
| NestedLoopJoin |
| predicate: true |
| type: left |
| est: (rows=1) |
| Receiver |
| fieldNames: [DUMMY] |
| sourceFragmentId: 3 |
| est: (rows=1) |
| ColocatedHashAggregate |
| fieldNames: [EXPR$0] |
| group: [] |
| aggregation: [COUNT()] |
| est: (rows=1) |
| TableScan |
| table: PUBLIC.CT_N1 |
| fieldNames: [ID, C1, C2, __PARTITION_ID, __PART, __part] |
| est: (rows=100001) |
| |
| Fragment#3 |
| distribution: random |
| executionNodes: [N1] |
| targetNodes: [N1] |
| colocationGroup[0]: {nodes=[N1], sourceIds=[0], assignments={part_0=N1:1}, partitionsWithConsistencyTokens={N1=[part_0:1]}} |
| partitions: [T_N1=[N1={0}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 4 |
| est: (rows=100008) |
| TableScan |
| table: PUBLIC.T_N1 |
| fieldNames: [DUMMY] |
| projection: [0] |
| est: (rows=100008) |
| --- |
| |
| N1 |
| SELECT /*+ disable_decorrelation */ (SELECT count(*) FROM ct_n1) FROM t_n1 |
| --- |
| Fragment#2 root |
| distribution: single |
| executionNodes: [N1] |
| exchangeSourceNodes: {3=[N1]} |
| colocationGroup[-1]: {nodes=[N1], sourceIds=[-1, 1, 3], assignments={part_0=N1:1}, partitionsWithConsistencyTokens={N1=[part_0:1]}} |
| colocationGroup[1]: {nodes=[N1], sourceIds=[-1, 1, 3], assignments={part_0=N1:1}, partitionsWithConsistencyTokens={N1=[part_0:1]}} |
| colocationGroup[3]: {nodes=[N1], sourceIds=[-1, 1, 3], assignments={part_0=N1:1}, partitionsWithConsistencyTokens={N1=[part_0:1]}} |
| partitions: [CT_N1=[N1={0}]] |
| tree: |
| Project |
| fieldNames: [EXPR$0] |
| projection: [EXPR$0] |
| est: (rows=1) |
| NestedLoopJoin |
| predicate: true |
| type: left |
| est: (rows=1) |
| Receiver |
| fieldNames: [DUMMY] |
| sourceFragmentId: 3 |
| est: (rows=1) |
| ColocatedHashAggregate |
| fieldNames: [EXPR$0] |
| group: [] |
| aggregation: [COUNT()] |
| est: (rows=1) |
| TableScan |
| table: PUBLIC.CT_N1 |
| fieldNames: [ID, C1, C2, __PARTITION_ID, __PART, __part] |
| est: (rows=100001) |
| |
| Fragment#3 |
| distribution: random |
| executionNodes: [N1] |
| targetNodes: [N1] |
| colocationGroup[0]: {nodes=[N1], sourceIds=[0], assignments={part_0=N1:1}, partitionsWithConsistencyTokens={N1=[part_0:1]}} |
| partitions: [T_N1=[N1={0}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 2 |
| est: (rows=100008) |
| TableScan |
| table: PUBLIC.T_N1 |
| fieldNames: [DUMMY] |
| projection: [0] |
| est: (rows=100008) |
| --- |
| |
| N0 |
| SELECT /*+ disable_decorrelation */ (SELECT count(*) FROM ct_n1) FROM t_n2 |
| --- |
| Fragment#2 root |
| distribution: single |
| executionNodes: [N0] |
| exchangeSourceNodes: {4=[N1]} |
| colocationGroup[-1]: {nodes=[N0], sourceIds=[-1, 4], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| colocationGroup[4]: {nodes=[N0], sourceIds=[-1, 4], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| tree: |
| Receiver |
| fieldNames: [EXPR$0] |
| sourceFragmentId: 4 |
| est: (rows=1) |
| |
| Fragment#4 |
| distribution: single |
| executionNodes: [N1] |
| targetNodes: [N0] |
| exchangeSourceNodes: {3=[N2]} |
| colocationGroup[-1]: {nodes=[N1], sourceIds=[-1, 1, 3], assignments={part_0=N1:1}, partitionsWithConsistencyTokens={N1=[part_0:1]}} |
| colocationGroup[1]: {nodes=[N1], sourceIds=[-1, 1, 3], assignments={part_0=N1:1}, partitionsWithConsistencyTokens={N1=[part_0:1]}} |
| colocationGroup[3]: {nodes=[N1], sourceIds=[-1, 1, 3], assignments={part_0=N1:1}, partitionsWithConsistencyTokens={N1=[part_0:1]}} |
| partitions: [CT_N1=[N1={0}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 2 |
| est: (rows=1) |
| Project |
| fieldNames: [EXPR$0] |
| projection: [EXPR$0] |
| est: (rows=1) |
| NestedLoopJoin |
| predicate: true |
| type: left |
| est: (rows=1) |
| Receiver |
| fieldNames: [DUMMY] |
| sourceFragmentId: 3 |
| est: (rows=1) |
| ColocatedHashAggregate |
| fieldNames: [EXPR$0] |
| group: [] |
| aggregation: [COUNT()] |
| est: (rows=1) |
| TableScan |
| table: PUBLIC.CT_N1 |
| fieldNames: [ID, C1, C2, __PARTITION_ID, __PART, __part] |
| est: (rows=100001) |
| |
| Fragment#3 |
| distribution: random |
| executionNodes: [N2] |
| targetNodes: [N1] |
| colocationGroup[0]: {nodes=[N2], sourceIds=[0], assignments={part_0=N2:1}, partitionsWithConsistencyTokens={N2=[part_0:1]}} |
| partitions: [T_N2=[N2={0}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 4 |
| est: (rows=100009) |
| TableScan |
| table: PUBLIC.T_N2 |
| fieldNames: [DUMMY] |
| projection: [0] |
| est: (rows=100009) |
| --- |
| |
| N1 |
| SELECT /*+ disable_decorrelation */ (SELECT count(*) FROM ct_n1) FROM t_n2 |
| --- |
| Fragment#2 root |
| distribution: single |
| executionNodes: [N1] |
| exchangeSourceNodes: {3=[N2]} |
| colocationGroup[-1]: {nodes=[N1], sourceIds=[-1, 1, 3], assignments={part_0=N1:1}, partitionsWithConsistencyTokens={N1=[part_0:1]}} |
| colocationGroup[1]: {nodes=[N1], sourceIds=[-1, 1, 3], assignments={part_0=N1:1}, partitionsWithConsistencyTokens={N1=[part_0:1]}} |
| colocationGroup[3]: {nodes=[N1], sourceIds=[-1, 1, 3], assignments={part_0=N1:1}, partitionsWithConsistencyTokens={N1=[part_0:1]}} |
| partitions: [CT_N1=[N1={0}]] |
| tree: |
| Project |
| fieldNames: [EXPR$0] |
| projection: [EXPR$0] |
| est: (rows=1) |
| NestedLoopJoin |
| predicate: true |
| type: left |
| est: (rows=1) |
| Receiver |
| fieldNames: [DUMMY] |
| sourceFragmentId: 3 |
| est: (rows=1) |
| ColocatedHashAggregate |
| fieldNames: [EXPR$0] |
| group: [] |
| aggregation: [COUNT()] |
| est: (rows=1) |
| TableScan |
| table: PUBLIC.CT_N1 |
| fieldNames: [ID, C1, C2, __PARTITION_ID, __PART, __part] |
| est: (rows=100001) |
| |
| Fragment#3 |
| distribution: random |
| executionNodes: [N2] |
| targetNodes: [N1] |
| colocationGroup[0]: {nodes=[N2], sourceIds=[0], assignments={part_0=N2:1}, partitionsWithConsistencyTokens={N2=[part_0:1]}} |
| partitions: [T_N2=[N2={0}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 2 |
| est: (rows=100009) |
| TableScan |
| table: PUBLIC.T_N2 |
| fieldNames: [DUMMY] |
| projection: [0] |
| est: (rows=100009) |
| --- |
| |
| N0 |
| SELECT /*+ disable_decorrelation */ t.c1 FROM t_n1 t JOIN table(system_range(1, 50)) as r ON t.id = r.x WHERE mod(r.x, 10) = 0 |
| --- |
| Fragment#2 root |
| distribution: single |
| executionNodes: [N0] |
| exchangeSourceNodes: {3=[N1]} |
| colocationGroup[-1]: {nodes=[N0], sourceIds=[-1, 3], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| colocationGroup[3]: {nodes=[N0], sourceIds=[-1, 3], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| tree: |
| Project |
| fieldNames: [C1] |
| projection: [C1] |
| est: (rows=2) |
| HashJoin |
| predicate: =(ID0, X) |
| type: inner |
| est: (rows=2) |
| Receiver |
| fieldNames: [C1, ID0] |
| sourceFragmentId: 3 |
| est: (rows=1) |
| Filter |
| predicate: =(MOD(X, 10), 0) |
| est: (rows=15) |
| TableFunctionScan |
| fieldNames: [X] |
| invocation: SYSTEM_RANGE(1, 50) |
| est: (rows=100) |
| |
| Fragment#3 |
| distribution: random |
| executionNodes: [N1] |
| targetNodes: [N0] |
| colocationGroup[0]: {nodes=[N1], sourceIds=[0], assignments={part_0=N1:1}, partitionsWithConsistencyTokens={N1=[part_0:1]}} |
| partitions: [T_N1=[N1={0}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 2 |
| est: (rows=100008) |
| TableScan |
| table: PUBLIC.T_N1 |
| fieldNames: [C1, ID0] |
| projection: [C1, CAST(ID):BIGINT NOT NULL] |
| est: (rows=100008) |
| --- |
| |
| N0 |
| SELECT /*+ disable_decorrelation */ t.c1 FROM ct_n1 t WHERE t.c1 < 5 AND |
| EXISTS (SELECT x FROM table(system_range(t.c1, t.c2)) WHERE mod(x, 2) = 0) |
| --- |
| Fragment#2 root |
| distribution: single |
| executionNodes: [N0] |
| exchangeSourceNodes: {3=[N1]} |
| colocationGroup[-1]: {nodes=[N0], sourceIds=[-1, 3], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| colocationGroup[3]: {nodes=[N0], sourceIds=[-1, 3], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| tree: |
| Receiver |
| fieldNames: [C1] |
| sourceFragmentId: 3 |
| est: (rows=1) |
| |
| Fragment#3 |
| distribution: single |
| executionNodes: [N1] |
| targetNodes: [N0] |
| colocationGroup[-1]: {nodes=[N1], sourceIds=[-1, 0], assignments={part_0=N1:1}, partitionsWithConsistencyTokens={N1=[part_0:1]}} |
| colocationGroup[0]: {nodes=[N1], sourceIds=[-1, 0], assignments={part_0=N1:1}, partitionsWithConsistencyTokens={N1=[part_0:1]}} |
| partitions: [CT_N1=[N1={0}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 2 |
| est: (rows=50001) |
| Project |
| fieldNames: [C1] |
| projection: [C1] |
| est: (rows=50001) |
| CorrelatedNestedLoopJoin |
| predicate: true |
| type: inner |
| correlates: [$cor5] |
| est: (rows=50001) |
| TableScan |
| table: PUBLIC.CT_N1 |
| predicate: <(C1, 5) |
| fieldNames: [C1, C2] |
| est: (rows=50001) |
| Project |
| fieldNames: [] |
| projection: [] |
| est: (rows=1) |
| ColocatedHashAggregate |
| group: [i] |
| aggregation: [] |
| est: (rows=1) |
| Project |
| fieldNames: [i] |
| projection: [true] |
| est: (rows=15) |
| Filter |
| predicate: =(MOD(X, 2), 0) |
| est: (rows=15) |
| TableFunctionScan |
| fieldNames: [X] |
| invocation: SYSTEM_RANGE($cor5.C1, $cor5.C2) |
| est: (rows=100) |
| --- |
| |
| N0 |
| SELECT /*+ disable_decorrelation */ t.c1 FROM t_n1 t WHERE t.c1 < 5 AND |
| EXISTS (SELECT x FROM table(system_range(t.c1, t.c2)) WHERE mod(x, 2) = 0) |
| --- |
| Fragment#2 root |
| distribution: single |
| executionNodes: [N0] |
| exchangeSourceNodes: {3=[N1]} |
| colocationGroup[-1]: {nodes=[N0], sourceIds=[-1, 3], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| colocationGroup[3]: {nodes=[N0], sourceIds=[-1, 3], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| tree: |
| Project |
| fieldNames: [C1] |
| projection: [C1] |
| est: (rows=1) |
| CorrelatedNestedLoopJoin |
| predicate: true |
| type: inner |
| correlates: [$cor5] |
| est: (rows=1) |
| Receiver |
| fieldNames: [C1, C2] |
| sourceFragmentId: 3 |
| est: (rows=1) |
| Project |
| fieldNames: [] |
| projection: [] |
| est: (rows=1) |
| ColocatedHashAggregate |
| group: [i] |
| aggregation: [] |
| est: (rows=1) |
| Project |
| fieldNames: [i] |
| projection: [true] |
| est: (rows=15) |
| Filter |
| predicate: =(MOD(X, 2), 0) |
| est: (rows=15) |
| TableFunctionScan |
| fieldNames: [X] |
| invocation: SYSTEM_RANGE($cor5.C1, $cor5.C2) |
| est: (rows=100) |
| |
| Fragment#3 |
| distribution: random |
| executionNodes: [N1] |
| targetNodes: [N0] |
| colocationGroup[0]: {nodes=[N1], sourceIds=[0], assignments={part_0=N1:1}, partitionsWithConsistencyTokens={N1=[part_0:1]}} |
| partitions: [T_N1=[N1={0}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 2 |
| est: (rows=50004) |
| TableScan |
| table: PUBLIC.T_N1 |
| predicate: <(C1, 5) |
| fieldNames: [C1, C2] |
| est: (rows=50004) |
| --- |
| # Pass partition pruning metadata for correlated joins. |
| N0 |
| SELECT /*+ disable_decorrelation */ * FROM t1_n0n1n2 as cor WHERE EXISTS (SELECT 1 FROM t2_n0n1n2 as t2 WHERE t2.id = cor.id) |
| --- |
| Fragment#2 root |
| distribution: single |
| executionNodes: [N0] |
| exchangeSourceNodes: {3=[N0, N1, N2], 4=[N0]} |
| colocationGroup[-1]: {nodes=[N0], sourceIds=[-1, 3, 4], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| colocationGroup[3]: {nodes=[N0], sourceIds=[-1, 3, 4], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| colocationGroup[4]: {nodes=[N0], sourceIds=[-1, 3, 4], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| tree: |
| CorrelatedNestedLoopJoin |
| predicate: true |
| type: inner |
| correlates: [$cor3] |
| est: (rows=1) |
| Receiver |
| fieldNames: [ID, C1, C2] |
| sourceFragmentId: 3 |
| est: (rows=1) |
| Receiver |
| fieldNames: [] |
| sourceFragmentId: 4 |
| est: (rows=1) |
| |
| Fragment#3 |
| distribution: table PUBLIC.T1_N0N1N2 in zone ZONE_5 |
| executionNodes: [N0, N1, N2] |
| targetNodes: [N0] |
| colocationGroup[0]: {nodes=[N0, N1, N2], sourceIds=[0], assignments={part_0=N0:3, part_1=N1:3, part_2=N2:3}, partitionsWithConsistencyTokens={N0=[part_0:3], N1=[part_1:3], N2=[part_2:3]}} |
| partitions: [T1_N0N1N2=[N0={0}, N1={1}, N2={2}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 2 |
| est: (rows=100005) |
| TableScan |
| table: PUBLIC.T1_N0N1N2 |
| fieldNames: [ID, C1, C2] |
| est: (rows=100005) |
| |
| Fragment#4 correlated |
| distribution: random |
| executionNodes: [N0] |
| targetNodes: [N0] |
| exchangeSourceNodes: {5=[N0, N1, N2]} |
| colocationGroup[5]: {nodes=[N0], sourceIds=[5], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 2 |
| est: (rows=1) |
| Project |
| fieldNames: [] |
| projection: [] |
| est: (rows=1) |
| ColocatedHashAggregate |
| group: [i] |
| aggregation: [] |
| est: (rows=1) |
| Receiver |
| fieldNames: [i] |
| sourceFragmentId: 5 |
| est: (rows=1) |
| |
| Fragment#5 correlated |
| distribution: random |
| executionNodes: [N0, N1, N2] |
| targetNodes: [N0] |
| colocationGroup[1]: {nodes=[N0, N1, N2], sourceIds=[1], assignments={part_0=N0:3, part_1=N1:3, part_2=N2:3}, partitionsWithConsistencyTokens={N0=[part_0:3], N1=[part_1:3], N2=[part_2:3]}} |
| partitions: [T2_N0N1N2=[N0={0}, N1={1}, N2={2}]] |
| tree: |
| Sender |
| distribution: hash by [i] |
| targetFragmentId: 4 |
| est: (rows=1) |
| TableScan |
| table: PUBLIC.T2_N0N1N2 |
| predicate: =(ID, $cor3.ID) |
| fieldNames: [i] |
| projection: [true] |
| est: (rows=1) |
| --- |
| # Pass partition pruning metadata to correlated joins. |
| N0 |
| SELECT /*+ disable_decorrelation */ * FROM t3_n0n1n2 AS out |
| WHERE EXISTS (SELECT * FROM t1_n0n1n2 as cor WHERE out.id = cor.id AND EXISTS (SELECT 1 FROM t2_n0n1n2 as t2 WHERE t2.id = cor.id)) |
| --- |
| Fragment#3 root |
| distribution: single |
| executionNodes: [N0] |
| exchangeSourceNodes: {4=[N0, N1, N2], 5=[N0, N1, N2], 6=[N0]} |
| colocationGroup[-1]: {nodes=[N0], sourceIds=[-1, 4, 5, 6], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| colocationGroup[4]: {nodes=[N0], sourceIds=[-1, 4, 5, 6], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| colocationGroup[5]: {nodes=[N0], sourceIds=[-1, 4, 5, 6], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| colocationGroup[6]: {nodes=[N0], sourceIds=[-1, 4, 5, 6], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| tree: |
| CorrelatedNestedLoopJoin |
| predicate: true |
| type: inner |
| correlates: [$cor6] |
| est: (rows=1) |
| Receiver |
| fieldNames: [ID, C1, C2] |
| sourceFragmentId: 4 |
| est: (rows=1) |
| Project |
| fieldNames: [] |
| projection: [] |
| est: (rows=1) |
| ColocatedHashAggregate |
| group: [i] |
| aggregation: [] |
| est: (rows=1) |
| Project |
| fieldNames: [i] |
| projection: [true] |
| est: (rows=1) |
| CorrelatedNestedLoopJoin |
| predicate: true |
| type: inner |
| correlates: [$cor7] |
| est: (rows=1) |
| Receiver |
| fieldNames: [ID] |
| sourceFragmentId: 5 |
| est: (rows=1) |
| Receiver |
| fieldNames: [] |
| sourceFragmentId: 6 |
| est: (rows=1) |
| |
| Fragment#4 |
| distribution: table PUBLIC.T3_N0N1N2 in zone ZONE_7 |
| executionNodes: [N0, N1, N2] |
| targetNodes: [N0] |
| colocationGroup[0]: {nodes=[N0, N1, N2], sourceIds=[0], assignments={part_0=N0:3, part_1=N1:3, part_2=N2:3}, partitionsWithConsistencyTokens={N0=[part_0:3], N1=[part_1:3], N2=[part_2:3]}} |
| partitions: [T3_N0N1N2=[N0={0}, N1={1}, N2={2}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 3 |
| est: (rows=100007) |
| TableScan |
| table: PUBLIC.T3_N0N1N2 |
| fieldNames: [ID, C1, C2] |
| est: (rows=100007) |
| |
| Fragment#5 correlated |
| distribution: table PUBLIC.T1_N0N1N2 in zone ZONE_5 |
| executionNodes: [N0, N1, N2] |
| targetNodes: [N0] |
| colocationGroup[1]: {nodes=[N0, N1, N2], sourceIds=[1], assignments={part_0=N0:3, part_1=N1:3, part_2=N2:3}, partitionsWithConsistencyTokens={N0=[part_0:3], N1=[part_1:3], N2=[part_2:3]}} |
| partitions: [T1_N0N1N2=[N0={0}, N1={1}, N2={2}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 3 |
| est: (rows=1) |
| TableScan |
| table: PUBLIC.T1_N0N1N2 |
| predicate: =($cor6.ID, ID) |
| fieldNames: [ID] |
| est: (rows=1) |
| |
| Fragment#6 correlated |
| distribution: random |
| executionNodes: [N0] |
| targetNodes: [N0] |
| exchangeSourceNodes: {7=[N0, N1, N2]} |
| colocationGroup[7]: {nodes=[N0], sourceIds=[7], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 3 |
| est: (rows=1) |
| Project |
| fieldNames: [] |
| projection: [] |
| est: (rows=1) |
| ColocatedHashAggregate |
| group: [i] |
| aggregation: [] |
| est: (rows=1) |
| Receiver |
| fieldNames: [i] |
| sourceFragmentId: 7 |
| est: (rows=1) |
| |
| Fragment#7 correlated |
| distribution: random |
| executionNodes: [N0, N1, N2] |
| targetNodes: [N0] |
| colocationGroup[2]: {nodes=[N0, N1, N2], sourceIds=[2], assignments={part_0=N0:3, part_1=N1:3, part_2=N2:3}, partitionsWithConsistencyTokens={N0=[part_0:3], N1=[part_1:3], N2=[part_2:3]}} |
| partitions: [T2_N0N1N2=[N0={0}, N1={1}, N2={2}]] |
| tree: |
| Sender |
| distribution: hash by [i] |
| targetFragmentId: 6 |
| est: (rows=1) |
| TableScan |
| table: PUBLIC.T2_N0N1N2 |
| predicate: =(ID, $cor7.ID) |
| fieldNames: [i] |
| projection: [true] |
| est: (rows=1) |
| --- |
| # Pass partition pruning metadata to correlated joins one layer deep. |
| N0 |
| SELECT /*+ disable_decorrelation */ * FROM t3_n0n1n2 AS out |
| WHERE EXISTS ( |
| SELECT * FROM t1_n0n1n2 as cor |
| WHERE out.id = cor.id AND EXISTS (SELECT 1 FROM t2_n0n1n2 as t2 WHERE t2.id = out.id or t2.id=cor.id) |
| ) |
| --- |
| Fragment#3 root |
| distribution: single |
| executionNodes: [N0] |
| exchangeSourceNodes: {4=[N0, N1, N2], 5=[N0, N1, N2], 6=[N0, N1, N2]} |
| colocationGroup[-1]: {nodes=[N0], sourceIds=[-1, 4, 5, 6], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| colocationGroup[4]: {nodes=[N0], sourceIds=[-1, 4, 5, 6], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| colocationGroup[5]: {nodes=[N0], sourceIds=[-1, 4, 5, 6], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| colocationGroup[6]: {nodes=[N0], sourceIds=[-1, 4, 5, 6], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| tree: |
| CorrelatedNestedLoopJoin |
| predicate: true |
| type: inner |
| correlates: [$cor8] |
| est: (rows=1) |
| Receiver |
| fieldNames: [ID, C1, C2] |
| sourceFragmentId: 4 |
| est: (rows=1) |
| Project |
| fieldNames: [] |
| projection: [] |
| est: (rows=1) |
| ColocatedHashAggregate |
| group: [i] |
| aggregation: [] |
| est: (rows=1) |
| Project |
| fieldNames: [i] |
| projection: [true] |
| est: (rows=1) |
| CorrelatedNestedLoopJoin |
| predicate: true |
| type: inner |
| correlates: [$cor9] |
| est: (rows=1) |
| Receiver |
| fieldNames: [ID] |
| sourceFragmentId: 5 |
| est: (rows=1) |
| Project |
| fieldNames: [] |
| projection: [] |
| est: (rows=1) |
| ReduceHashAggregate |
| fieldNames: [i] |
| group: [i] |
| aggregation: [] |
| est: (rows=1) |
| Receiver |
| fieldNames: [i, _GROUP_ID] |
| sourceFragmentId: 6 |
| est: (rows=1) |
| |
| Fragment#4 |
| distribution: table PUBLIC.T3_N0N1N2 in zone ZONE_7 |
| executionNodes: [N0, N1, N2] |
| targetNodes: [N0] |
| colocationGroup[0]: {nodes=[N0, N1, N2], sourceIds=[0], assignments={part_0=N0:3, part_1=N1:3, part_2=N2:3}, partitionsWithConsistencyTokens={N0=[part_0:3], N1=[part_1:3], N2=[part_2:3]}} |
| partitions: [T3_N0N1N2=[N0={0}, N1={1}, N2={2}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 3 |
| est: (rows=100007) |
| TableScan |
| table: PUBLIC.T3_N0N1N2 |
| fieldNames: [ID, C1, C2] |
| est: (rows=100007) |
| |
| Fragment#5 correlated |
| distribution: table PUBLIC.T1_N0N1N2 in zone ZONE_5 |
| executionNodes: [N0, N1, N2] |
| targetNodes: [N0] |
| colocationGroup[1]: {nodes=[N0, N1, N2], sourceIds=[1], assignments={part_0=N0:3, part_1=N1:3, part_2=N2:3}, partitionsWithConsistencyTokens={N0=[part_0:3], N1=[part_1:3], N2=[part_2:3]}} |
| partitions: [T1_N0N1N2=[N0={0}, N1={1}, N2={2}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 3 |
| est: (rows=1) |
| TableScan |
| table: PUBLIC.T1_N0N1N2 |
| predicate: =($cor8.ID, ID) |
| fieldNames: [ID] |
| est: (rows=1) |
| |
| Fragment#6 correlated |
| distribution: random |
| executionNodes: [N0, N1, N2] |
| targetNodes: [N0] |
| colocationGroup[2]: {nodes=[N0, N1, N2], sourceIds=[2], assignments={part_0=N0:3, part_1=N1:3, part_2=N2:3}, partitionsWithConsistencyTokens={N0=[part_0:3], N1=[part_1:3], N2=[part_2:3]}} |
| partitions: [T2_N0N1N2=[N0={0}, N1={1}, N2={2}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 3 |
| est: (rows=11370) |
| MapHashAggregate |
| fieldNames: [i, _GROUP_ID] |
| group: [i] |
| aggregation: [] |
| est: (rows=11370) |
| TableScan |
| table: PUBLIC.T2_N0N1N2 |
| predicate: OR(=(ID, $cor8.ID), =(ID, $cor9.ID)) |
| fieldNames: [i] |
| projection: [true] |
| est: (rows=56850) |
| --- |
