| # Pruning from a simple scan |
| N1 |
| SELECT * FROM t1_n1n2n3 WHERE id = 0 |
| --- |
| Fragment#1 root |
| distribution: single |
| executionNodes: [N1] |
| exchangeSourceNodes: {2=[N2]} |
| colocationGroup[-1]: {nodes=[N1], sourceIds=[-1, 2], assignments={}, partitionsWithConsistencyTokens={N1=[]}} |
| colocationGroup[2]: {nodes=[N1], sourceIds=[-1, 2], assignments={}, partitionsWithConsistencyTokens={N1=[]}} |
| tree: |
| Receiver |
| fieldNames: [ID, C1, C2] |
| sourceFragmentId: 2 |
| est: (rows=1) |
| |
| Fragment#2 |
| distribution: table PUBLIC.T1_N1N2N3 in zone ZONE_1 |
| executionNodes: [N2] |
| targetNodes: [N1] |
| colocationGroup[0]: {nodes=[N2], sourceIds=[0], assignments={part_1=N2:3}, partitionsWithConsistencyTokens={N2=[part_1:3]}} |
| partitions: [T1_N1N2N3=[N2={1}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 1 |
| est: (rows=1) |
| TableScan |
| table: PUBLIC.T1_N1N2N3 |
| predicate: =(ID, 0) |
| fieldNames: [ID, C1, C2] |
| est: (rows=1) |
| --- |
| # Partition pruning of joined tables (relies on predicate push down) |
| N1 |
| SELECT /*+ DISABLE_RULE('NestedLoopJoinConverter', 'HashJoinConverter', 'CorrelatedNestedLoopJoin') */ * FROM t1_n1n2n3 as t1, t2_n4n5 as t2 WHERE t1.c1 = t2.c1 and t1.id = 1 and t2.id = 42 |
| --- |
| Fragment#2 root |
| distribution: single |
| executionNodes: [N1] |
| exchangeSourceNodes: {3=[N2], 4=[N4]} |
| colocationGroup[-1]: {nodes=[N1], sourceIds=[-1, 3, 4], assignments={}, partitionsWithConsistencyTokens={N1=[]}} |
| colocationGroup[3]: {nodes=[N1], sourceIds=[-1, 3, 4], assignments={}, partitionsWithConsistencyTokens={N1=[]}} |
| colocationGroup[4]: {nodes=[N1], sourceIds=[-1, 3, 4], assignments={}, partitionsWithConsistencyTokens={N1=[]}} |
| tree: |
| MergeJoin |
| predicate: =(C1, C1$0) |
| fieldNames: [ID, C1, C2, ID$0, C1$0, C2$0] |
| type: inner |
| est: (rows=1) |
| Receiver |
| fieldNames: [ID, C1, C2] |
| sourceFragmentId: 3 |
| est: (rows=1) |
| Receiver |
| fieldNames: [ID, C1, C2] |
| sourceFragmentId: 4 |
| est: (rows=1) |
| |
| Fragment#3 |
| distribution: table PUBLIC.T1_N1N2N3 in zone ZONE_1 |
| executionNodes: [N2] |
| targetNodes: [N1] |
| colocationGroup[0]: {nodes=[N2], sourceIds=[0], assignments={part_1=N2:3}, partitionsWithConsistencyTokens={N2=[part_1:3]}} |
| partitions: [T1_N1N2N3=[N2={1}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 2 |
| est: (rows=1) |
| Sort |
| collation: [C1 ASC] |
| est: (rows=1) |
| TableScan |
| table: PUBLIC.T1_N1N2N3 |
| predicate: =(ID, 1) |
| fieldNames: [ID, C1, C2] |
| est: (rows=1) |
| |
| Fragment#4 |
| distribution: table PUBLIC.T2_N4N5 in zone ZONE_2 |
| executionNodes: [N4] |
| targetNodes: [N1] |
| colocationGroup[1]: {nodes=[N4], sourceIds=[1], assignments={part_0=N4:2}, partitionsWithConsistencyTokens={N4=[part_0:2]}} |
| partitions: [T2_N4N5=[N4={0}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 2 |
| est: (rows=1) |
| Sort |
| collation: [C1 ASC] |
| est: (rows=1) |
| TableScan |
| table: PUBLIC.T2_N4N5 |
| predicate: =(ID, 42) |
| fieldNames: [ID, C1, C2] |
| est: (rows=1) |
| --- |
| # Self join, different predicates that produce same set of partitions |
| N1 |
| SELECT /*+ DISABLE_RULE('NestedLoopJoinConverter', 'HashJoinConverter', 'CorrelatedNestedLoopJoin') */ * FROM t1_n1n2n3 as t1, t1_n1n2n3 as t2 WHERE t1.c1 = t2.c1 and t1.id = 1 and t2.id = 17 |
| --- |
| Fragment#2 root |
| distribution: single |
| executionNodes: [N1] |
| exchangeSourceNodes: {3=[N2], 4=[N2]} |
| colocationGroup[-1]: {nodes=[N1], sourceIds=[-1, 3, 4], assignments={}, partitionsWithConsistencyTokens={N1=[]}} |
| colocationGroup[3]: {nodes=[N1], sourceIds=[-1, 3, 4], assignments={}, partitionsWithConsistencyTokens={N1=[]}} |
| colocationGroup[4]: {nodes=[N1], sourceIds=[-1, 3, 4], assignments={}, partitionsWithConsistencyTokens={N1=[]}} |
| tree: |
| MergeJoin |
| predicate: =(C1, C1$0) |
| fieldNames: [ID, C1, C2, ID$0, C1$0, C2$0] |
| type: inner |
| est: (rows=1) |
| Receiver |
| fieldNames: [ID, C1, C2] |
| sourceFragmentId: 3 |
| est: (rows=1) |
| Receiver |
| fieldNames: [ID, C1, C2] |
| sourceFragmentId: 4 |
| est: (rows=1) |
| |
| Fragment#3 |
| distribution: table PUBLIC.T1_N1N2N3 in zone ZONE_1 |
| executionNodes: [N2] |
| targetNodes: [N1] |
| colocationGroup[0]: {nodes=[N2], sourceIds=[0], assignments={part_1=N2:3}, partitionsWithConsistencyTokens={N2=[part_1:3]}} |
| partitions: [T1_N1N2N3=[N2={1}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 2 |
| est: (rows=1) |
| Sort |
| collation: [C1 ASC] |
| est: (rows=1) |
| TableScan |
| table: PUBLIC.T1_N1N2N3 |
| predicate: =(ID, 1) |
| fieldNames: [ID, C1, C2] |
| est: (rows=1) |
| |
| Fragment#4 |
| distribution: table PUBLIC.T1_N1N2N3 in zone ZONE_1 |
| executionNodes: [N2] |
| targetNodes: [N1] |
| colocationGroup[1]: {nodes=[N2], sourceIds=[1], assignments={part_1=N2:3}, partitionsWithConsistencyTokens={N2=[part_1:3]}} |
| partitions: [T1_N1N2N3=[N2={1}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 2 |
| est: (rows=1) |
| Sort |
| collation: [C1 ASC] |
| est: (rows=1) |
| TableScan |
| table: PUBLIC.T1_N1N2N3 |
| predicate: =(ID, 17) |
| fieldNames: [ID, C1, C2] |
| est: (rows=1) |
| --- |
| # Self join, different predicates that produce disjoint set of partitions |
| N1 |
| SELECT /*+ DISABLE_RULE('NestedLoopJoinConverter', 'HashJoinConverter', 'CorrelatedNestedLoopJoin') */ * |
| FROM t1_n1n2n3 as t1, t1_n1n2n3 as t2 |
| WHERE t1.id = t2.id and t1.id IN (1, 3) and t2.id IN (42, 44) |
| --- |
| Fragment#2 root |
| distribution: single |
| executionNodes: [N1] |
| exchangeSourceNodes: {3=[N2, N3]} |
| colocationGroup[-1]: {nodes=[N1], sourceIds=[-1, 3], assignments={}, partitionsWithConsistencyTokens={N1=[]}} |
| colocationGroup[3]: {nodes=[N1], sourceIds=[-1, 3], assignments={}, partitionsWithConsistencyTokens={N1=[]}} |
| tree: |
| Receiver |
| fieldNames: [ID, C1, C2, ID$0, C1$0, C2$0] |
| sourceFragmentId: 3 |
| est: (rows=1) |
| |
| Fragment#3 |
| distribution: table PUBLIC.T1_N1N2N3 in zone ZONE_1 |
| executionNodes: [N2, N3] |
| targetNodes: [N1] |
| colocationGroup[0]: {nodes=[N2], sourceIds=[0], assignments={part_1=N2:3}, partitionsWithConsistencyTokens={N2=[part_1:3]}} |
| colocationGroup[1]: {nodes=[N3], sourceIds=[1], assignments={part_2=N3:3}, partitionsWithConsistencyTokens={N3=[part_2:3]}} |
| partitions: [T1_N1N2N3=[N2={1}, N3={2}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 2 |
| est: (rows=32316) |
| MergeJoin |
| predicate: =(ID, ID$0) |
| fieldNames: [ID, C1, C2, ID$0, C1$0, C2$0] |
| type: inner |
| est: (rows=32316) |
| Sort |
| collation: [ID ASC] |
| est: (rows=56847) |
| TableScan |
| table: PUBLIC.T1_N1N2N3 |
| predicate: SEARCH(ID, Sarg[1, 3]) |
| fieldNames: [ID, C1, C2] |
| est: (rows=56847) |
| Sort |
| collation: [ID ASC] |
| est: (rows=56847) |
| TableScan |
| table: PUBLIC.T1_N1N2N3 |
| predicate: SEARCH(ID, Sarg[42, 44]) |
| fieldNames: [ID, C1, C2] |
| est: (rows=56847) |
| --- |
| # Correlated |
| # Prune partitions from left arm statically, and pass meta to the right arm. |
| # Same set of nodes. |
| N0 |
| SELECT /*+ disable_decorrelation */ * FROM t1_n1n2n3 as cor WHERE cor.id = 42 and EXISTS (SELECT 1 FROM t3_n1n2n3 as t2 WHERE t2.id = cor.id) |
| --- |
| Fragment#2 root |
| distribution: single |
| executionNodes: [N0] |
| exchangeSourceNodes: {3=[N3], 4=[N1, N2, N3]} |
| 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_N1N2N3 in zone ZONE_1 |
| executionNodes: [N3] |
| targetNodes: [N0] |
| colocationGroup[0]: {nodes=[N3], sourceIds=[0], assignments={part_2=N3:3}, partitionsWithConsistencyTokens={N3=[part_2:3]}} |
| partitions: [T1_N1N2N3=[N3={2}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 2 |
| est: (rows=1) |
| TableScan |
| table: PUBLIC.T1_N1N2N3 |
| predicate: =(ID, 42) |
| fieldNames: [ID, C1, C2] |
| est: (rows=1) |
| |
| Fragment#4 correlated |
| distribution: random |
| executionNodes: [N1, N2, N3] |
| targetNodes: [N0] |
| exchangeSourceNodes: {5=[N1, N2, N3]} |
| colocationGroup[5]: {nodes=[N1, N2, N3], sourceIds=[5], assignments={}, partitionsWithConsistencyTokens={N1=[], N2=[], N3=[]}} |
| 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: [N1, N2, N3] |
| targetNodes: [N1, N2, N3] |
| colocationGroup[1]: {nodes=[N1, N2, N3], sourceIds=[1], assignments={part_0=N1:3, part_1=N2:3, part_2=N3:3}, partitionsWithConsistencyTokens={N1=[part_0:3], N2=[part_1:3], N3=[part_2:3]}} |
| partitions: [T3_N1N2N3=[N1={0}, N2={1}, N3={2}]] |
| tree: |
| Sender |
| distribution: hash by [i] |
| targetFragmentId: 4 |
| est: (rows=1) |
| TableScan |
| table: PUBLIC.T3_N1N2N3 |
| predicate: =(ID, $cor3.ID) |
| fieldNames: [i] |
| projection: [true] |
| est: (rows=1) |
| --- |
| # Correlated. |
| # Prune partitions from left arm statically, and pass meta to the right arm. |
| # Different sets of nodes. |
| N0 |
| SELECT /*+ disable_decorrelation */ * FROM t1_n1n2n3 as cor WHERE cor.id = 42 and EXISTS (SELECT 1 FROM t2_n4n5 as t2 WHERE t2.id = cor.id) |
| --- |
| Fragment#2 root |
| distribution: single |
| executionNodes: [N0] |
| exchangeSourceNodes: {3=[N3], 4=[N4, N5]} |
| 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_N1N2N3 in zone ZONE_1 |
| executionNodes: [N3] |
| targetNodes: [N0] |
| colocationGroup[0]: {nodes=[N3], sourceIds=[0], assignments={part_2=N3:3}, partitionsWithConsistencyTokens={N3=[part_2:3]}} |
| partitions: [T1_N1N2N3=[N3={2}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 2 |
| est: (rows=1) |
| TableScan |
| table: PUBLIC.T1_N1N2N3 |
| predicate: =(ID, 42) |
| fieldNames: [ID, C1, C2] |
| est: (rows=1) |
| |
| Fragment#4 correlated |
| distribution: random |
| executionNodes: [N4, N5] |
| targetNodes: [N0] |
| exchangeSourceNodes: {5=[N4, N5]} |
| colocationGroup[5]: {nodes=[N4, N5], sourceIds=[5], assignments={}, partitionsWithConsistencyTokens={N4=[], N5=[]}} |
| 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: [N4, N5] |
| targetNodes: [N4, N5] |
| colocationGroup[1]: {nodes=[N4, N5], sourceIds=[1], assignments={part_0=N4:2, part_1=N5:2}, partitionsWithConsistencyTokens={N4=[part_0:2], N5=[part_1:2]}} |
| partitions: [T2_N4N5=[N4={0}, N5={1}]] |
| tree: |
| Sender |
| distribution: hash by [i] |
| targetFragmentId: 4 |
| est: (rows=1) |
| TableScan |
| table: PUBLIC.T2_N4N5 |
| predicate: =(ID, $cor3.ID) |
| fieldNames: [i] |
| projection: [true] |
| est: (rows=1) |
| --- |
| |
| N0 |
| SELECT id, c1 FROM (SELECT id, c1 FROM t1_n1n2n3 EXCEPT ALL SELECT id, c1 FROM t1_n1n2n3 WHERE id IN (1, 1, 3, 3)) tmp ORDER BY id ASC |
| --- |
| Fragment#2 root |
| distribution: single |
| executionNodes: [N0] |
| exchangeSourceNodes: {3=[N1, N2, N3]} |
| colocationGroup[-1]: {nodes=[N0], sourceIds=[-1, 3], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| colocationGroup[3]: {nodes=[N0], sourceIds=[-1, 3], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| tree: |
| Receiver |
| fieldNames: [ID, C1] |
| sourceFragmentId: 3 |
| est: (rows=1) |
| |
| Fragment#3 |
| distribution: table PUBLIC.T1_N1N2N3 in zone ZONE_1 |
| executionNodes: [N1, N2, N3] |
| targetNodes: [N0] |
| colocationGroup[0]: {nodes=[N1, N2, N3], sourceIds=[0], assignments={part_0=N1:3, part_1=N2:3, part_2=N3:3}, partitionsWithConsistencyTokens={N1=[part_0:3], N2=[part_1:3], N3=[part_2:3]}} |
| colocationGroup[1]: {nodes=[N2], sourceIds=[1], assignments={part_1=N2:3}, partitionsWithConsistencyTokens={N2=[part_1:3]}} |
| partitions: [T1_N1N2N3=[N1={0}, N2={1}, N3={2}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 2 |
| est: (rows=71577) |
| Sort |
| collation: [ID ASC] |
| est: (rows=71577) |
| ColocatedMinus |
| all: true |
| est: (rows=71577) |
| TableScan |
| table: PUBLIC.T1_N1N2N3 |
| fieldNames: [ID, C1] |
| est: (rows=100001) |
| TableScan |
| table: PUBLIC.T1_N1N2N3 |
| predicate: SEARCH(ID, Sarg[1, 3]) |
| fieldNames: [ID, C1] |
| est: (rows=56847) |
| --- |
| |
| N0 |
| SELECT id, c1 FROM (SELECT id, c1 FROM t1_n1n2n3 WHERE id IN (1, 1, 3, 3) EXCEPT ALL SELECT id, c1 FROM t1_n1n2n3 WHERE id IN (1, 1, 3, 3)) tmp ORDER BY id ASC |
| --- |
| Fragment#2 root |
| distribution: single |
| executionNodes: [N0] |
| exchangeSourceNodes: {3=[N2]} |
| colocationGroup[-1]: {nodes=[N0], sourceIds=[-1, 3], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| colocationGroup[3]: {nodes=[N0], sourceIds=[-1, 3], assignments={}, partitionsWithConsistencyTokens={N0=[]}} |
| tree: |
| Receiver |
| fieldNames: [ID, C1] |
| sourceFragmentId: 3 |
| est: (rows=1) |
| |
| Fragment#3 |
| distribution: table PUBLIC.T1_N1N2N3 in zone ZONE_1 |
| executionNodes: [N2] |
| targetNodes: [N0] |
| colocationGroup[0]: {nodes=[N2], sourceIds=[0], assignments={part_1=N2:3}, partitionsWithConsistencyTokens={N2=[part_1:3]}} |
| colocationGroup[1]: {nodes=[N2], sourceIds=[1], assignments={part_1=N2:3}, partitionsWithConsistencyTokens={N2=[part_1:3]}} |
| partitions: [T1_N1N2N3=[N2={1}]] |
| tree: |
| Sender |
| distribution: single |
| targetFragmentId: 2 |
| est: (rows=28424) |
| Sort |
| collation: [ID ASC] |
| est: (rows=28424) |
| ColocatedMinus |
| all: true |
| est: (rows=28424) |
| TableScan |
| table: PUBLIC.T1_N1N2N3 |
| predicate: SEARCH(ID, Sarg[1, 3]) |
| fieldNames: [ID, C1] |
| est: (rows=56847) |
| TableScan |
| table: PUBLIC.T1_N1N2N3 |
| predicate: SEARCH(ID, Sarg[1, 3]) |
| fieldNames: [ID, C1] |
| est: (rows=56847) |
| --- |
