Major Description
TPCH q2 is a good example of this.
select
|
s.s_acctbal,
|
s.s_name,
|
n.n_name,
|
p.p_partkey,
|
p.p_mfgr,
|
s.s_address,
|
s.s_phone,
|
s.s_comment
|
from
|
part p,
|
supplier s,
|
partsupp ps,
|
nation n,
|
region r
|
where
|
p.p_partkey = ps.ps_partkey
|
and s.s_suppkey = ps.ps_suppkey
|
and p.p_size = 15
|
and p.p_type like '%BRASS'
|
and s.s_nationkey = n.n_nationkey
|
and n.n_regionkey = r.r_regionkey
|
and r.r_name = 'ASIA'
|
and ps.ps_supplycost = (
|
select VALUE
|
min(ps2.ps_supplycost)
|
from
|
partsupp ps2, supplier s2,
|
nation n2, region r2
|
where
|
p.p_partkey = ps2.ps_partkey
|
and s2.s_suppkey = ps2.ps_suppkey
|
and s2.s_nationkey = n2.n_nationkey
|
and n2.n_regionkey = r2.r_regionkey
|
and r2.r_name = 'ASIA'
|
) [0]
|
order by
|
s.s_acctbal desc,
|
n.n_name,
|
s.s_name,
|
ps.p_partkey;
|
The current plan uses a left outer join which is not necessary. (search for "left outer join")
distribute result [$$168]
|
-- DISTRIBUTE_RESULT |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
project ([$$168])
|
-- STREAM_PROJECT |PARTITIONED|
|
assign [$$168] <- [{"s_acctbal": $$228, "s_name": $$230, "n_name": $$229, "p_partkey": $$216, "p_mfgr": $$235, "s_address": $$236, "s_phone": $$237, "s_comment": $$238}]
|
-- ASSIGN |PARTITIONED|
|
exchange
|
-- SORT_MERGE_EXCHANGE [$$228(DESC), $$229(ASC), $$230(ASC), $$231(ASC) ] |PARTITIONED|
|
order (DESC, $$228) (ASC, $$229) (ASC, $$230) (ASC, $$231)
|
-- STABLE_SORT [$$228(DESC), $$229(ASC), $$230(ASC), $$231(ASC)] |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
project ([$$228, $$230, $$229, $$216, $$235, $$236, $$237, $$238, $$231])
|
-- STREAM_PROJECT |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
join (eq($$209, $$210))
|
-- HYBRID_HASH_JOIN [$$209][$$210] |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$209] |PARTITIONED|
|
project ([$$228, $$230, $$229, $$216, $$235, $$236, $$237, $$238, $$231, $$209])
|
-- STREAM_PROJECT |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
join (eq($$211, $$212))
|
-- HYBRID_HASH_JOIN [$$211][$$212] |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$211] |PARTITIONED|
|
project ([$$228, $$230, $$216, $$235, $$236, $$237, $$238, $$231, $$211])
|
-- STREAM_PROJECT |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
join (and(eq($$216, $$213), eq($$214, $$215)))
|
-- HYBRID_HASH_JOIN [$$216, $$215][$$213, $$214] |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$216, $$215] |PARTITIONED|
|
project ([$$216, $$235, $$215])
|
-- STREAM_PROJECT |PARTITIONED|
|
assign [$$215] <- [get-item($$155, 0)]
|
-- ASSIGN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
group by ([$$241 := $$174]) decor ([$$216; $$235]) {
|
aggregate [$$155] <- [listify($$192)]
|
-- AGGREGATE |LOCAL|
|
aggregate [$$192] <- [agg-sql-min($$152)]
|
-- AGGREGATE |LOCAL|
|
select (not(is-missing($$240)))
|
-- STREAM_SELECT |LOCAL|
|
nested tuple source
|
-- NESTED_TUPLE_SOURCE |LOCAL|
|
}
|
-- PRE_CLUSTERED_GROUP_BY[$$174] |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
order (ASC, $$174)
|
-- STABLE_SORT [$$174(ASC)] |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$174] |PARTITIONED|
|
project ([$$216, $$235, $$152, $$240, $$174])
|
-- STREAM_PROJECT |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
left outer join (eq($$216, $$217))
|
-- HYBRID_HASH_JOIN [$$216][$$217] |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$216] |PARTITIONED|
|
project ([$$216, $$235, $$174])
|
-- STREAM_PROJECT |PARTITIONED|
|
select (and(eq($$225, 15), like($$224, "%BRASS")))
|
-- STREAM_SELECT |PARTITIONED|
|
assign [$$235, $$216, $$225, $$224] <- [$$p.getField("p_mfgr"), $$p.getField("p_partkey"), $$p.getField("p_size"), $$p.getField("p_type")]
|
-- ASSIGN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
data-scan []<-[$$174, $$p, $$175] <- Default.part
|
-- DATASOURCE_SCAN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
empty-tuple-source
|
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$217] |PARTITIONED|
|
project ([$$152, $$240, $$217])
|
-- STREAM_PROJECT |PARTITIONED|
|
assign [$$240] <- [TRUE]
|
-- ASSIGN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
join (eq($$220, $$221))
|
-- HYBRID_HASH_JOIN [$$221][$$220] |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$221] |PARTITIONED|
|
project ([$$152, $$217, $$221])
|
-- STREAM_PROJECT |PARTITIONED|
|
assign [$$152, $$217, $$221] <- [$$195.getField("ps_supplycost"), $$195.getField("ps_partkey"), $$195.getField("ps_suppkey")]
|
-- ASSIGN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
replicate
|
-- REPLICATE |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
data-scan []<-[$$205, $$195, $$206] <- Default.partsupp
|
-- DATASOURCE_SCAN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
empty-tuple-source
|
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$220] |PARTITIONED|
|
project ([$$220])
|
-- STREAM_PROJECT |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
join (eq($$222, $$223))
|
-- HYBRID_HASH_JOIN [$$222][$$223] |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$222] |PARTITIONED|
|
project ([$$220, $$222])
|
-- STREAM_PROJECT |PARTITIONED|
|
assign [$$222, $$220] <- [$$196.getField("s_nationkey"), $$196.getField("s_suppkey")]
|
-- ASSIGN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
replicate
|
-- REPLICATE |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
data-scan []<-[$$203, $$196, $$204] <- Default.supplier
|
-- DATASOURCE_SCAN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
empty-tuple-source
|
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$223] |PARTITIONED|
|
project ([$$223])
|
-- STREAM_PROJECT |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
join (eq($$218, $$219))
|
-- HYBRID_HASH_JOIN [$$218][$$219] |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$218] |PARTITIONED|
|
project ([$$223, $$218])
|
-- STREAM_PROJECT |PARTITIONED|
|
assign [$$218, $$223] <- [$$197.getField("n_regionkey"), $$197.getField("n_nationkey")]
|
-- ASSIGN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
replicate
|
-- REPLICATE |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
data-scan []<-[$$201, $$197, $$202] <- Default.nation
|
-- DATASOURCE_SCAN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
empty-tuple-source
|
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
replicate
|
-- REPLICATE |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$219] |PARTITIONED|
|
project ([$$219])
|
-- STREAM_PROJECT |PARTITIONED|
|
select (eq($$226, "ASIA"))
|
-- STREAM_SELECT |PARTITIONED|
|
assign [$$219, $$226] <- [$$198.getField("r_regionkey"), $$198.getField("r_name")]
|
-- ASSIGN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
data-scan []<-[$$199, $$198, $$200] <- Default.region
|
-- DATASOURCE_SCAN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
empty-tuple-source
|
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$213, $$214] |PARTITIONED|
|
project ([$$228, $$230, $$236, $$237, $$238, $$231, $$211, $$213, $$214])
|
-- STREAM_PROJECT |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
join (eq($$207, $$208))
|
-- HYBRID_HASH_JOIN [$$208][$$207] |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$208] |PARTITIONED|
|
project ([$$231, $$213, $$214, $$208])
|
-- STREAM_PROJECT |PARTITIONED|
|
assign [$$231, $$214, $$213, $$208] <- [$$ps.getField("p_partkey"), $$ps.getField("ps_supplycost"), $$ps.getField("ps_partkey"), $$ps.getField("ps_suppkey")]
|
-- ASSIGN |PARTITIONED|
|
project ([$$178, $$ps, $$179])
|
-- STREAM_PROJECT |PARTITIONED|
|
assign [$$178, $$ps, $$179] <- [$$205, $$195, $$206]
|
-- ASSIGN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
replicate
|
-- REPLICATE |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
data-scan []<-[$$205, $$195, $$206] <- Default.partsupp
|
-- DATASOURCE_SCAN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
empty-tuple-source
|
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$207] |PARTITIONED|
|
project ([$$228, $$230, $$236, $$237, $$238, $$211, $$207])
|
-- STREAM_PROJECT |PARTITIONED|
|
assign [$$238, $$237, $$236, $$230, $$228, $$211, $$207] <- [$$s.getField("s_comment"), $$s.getField("s_phone"), $$s.getField("s_address"), $$s.getField("s_name"), $$s.getField("s_acctbal"), $$s.getField("s_nationkey"), $$s.getField("s_suppkey")]
|
-- ASSIGN |PARTITIONED|
|
project ([$$176, $$s, $$177])
|
-- STREAM_PROJECT |PARTITIONED|
|
assign [$$176, $$s, $$177] <- [$$203, $$196, $$204]
|
-- ASSIGN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
replicate
|
-- REPLICATE |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
data-scan []<-[$$203, $$196, $$204] <- Default.supplier
|
-- DATASOURCE_SCAN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
empty-tuple-source
|
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$212] |PARTITIONED|
|
project ([$$229, $$209, $$212])
|
-- STREAM_PROJECT |PARTITIONED|
|
assign [$$229, $$209, $$212] <- [$$n.getField("n_name"), $$n.getField("n_regionkey"), $$n.getField("n_nationkey")]
|
-- ASSIGN |PARTITIONED|
|
project ([$$180, $$n, $$181])
|
-- STREAM_PROJECT |PARTITIONED|
|
assign [$$180, $$n, $$181] <- [$$201, $$197, $$202]
|
-- ASSIGN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
replicate
|
-- REPLICATE |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
data-scan []<-[$$201, $$197, $$202] <- Default.nation
|
-- DATASOURCE_SCAN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
empty-tuple-source
|
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
project ([$$210])
|
-- STREAM_PROJECT |PARTITIONED|
|
assign [$$210] <- [$$219]
|
-- ASSIGN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
replicate
|
-- REPLICATE |PARTITIONED|
|
exchange
|
-- HASH_PARTITION_EXCHANGE [$$219] |PARTITIONED|
|
project ([$$219])
|
-- STREAM_PROJECT |PARTITIONED|
|
select (eq($$226, "ASIA"))
|
-- STREAM_SELECT |PARTITIONED|
|
assign [$$219, $$226] <- [$$198.getField("r_regionkey"), $$198.getField("r_name")]
|
-- ASSIGN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
data-scan []<-[$$199, $$198, $$200] <- Default.region
|
-- DATASOURCE_SCAN |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
empty-tuple-source
|
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
|