I have the following query against and Oracle table.
SELECT `YEAR`, `MONTH`, SUM(PREP_IN) as rainfall
FROM yoda.SEA_PLATFORM.WEATHER_CF
WHERE prep_in IS NOT NULL
GROUP BY `YEAR`, `MONTH`
ORDER BY rainfall DESC
LIMIT 10
I am attempting to determine what part of the query is pushed into Oracle. In
the physical plan I see the following (widen the window to see this easier):
00-00 Screen : rowType = RecordType(DECIMAL(0, 0) YEAR, DECIMAL(0, 0) MONTH,
ANY rainfall): rowcount = 10.0, cumulative cost = {219.0 rows,
2789.589411415945 cpu, 0.0 io, 0.0 network, 2376.0 memory}, id = 247714
00-01 Project(YEAR=[$0], MONTH=[$1], rainfall=[$2]) : rowType =
RecordType(DECIMAL(0, 0) YEAR, DECIMAL(0, 0) MONTH, ANY rainfall): rowcount =
10.0, cumulative cost = {218.0 rows, 2788.589411415945 cpu, 0.0 io, 0.0
network, 2376.0 memory}, id = 247713
00-02 SelectionVectorRemover : rowType = RecordType(DECIMAL(0, 0) YEAR,
DECIMAL(0, 0) MONTH, ANY rainfall): rowcount = 10.0, cumulative cost = {218.0
rows, 2788.589411415945 cpu, 0.0 io, 0.0 network, 2376.0 memory}, id = 247712
00-03 Limit(fetch=[10]) : rowType = RecordType(DECIMAL(0, 0) YEAR,
DECIMAL(0, 0) MONTH, ANY rainfall): rowcount = 10.0, cumulative cost = {208.0
rows, 2778.589411415945 cpu, 0.0 io, 0.0 network, 2376.0 memory}, id = 247711
00-04 SelectionVectorRemover : rowType = RecordType(DECIMAL(0, 0)
YEAR, DECIMAL(0, 0) MONTH, ANY rainfall): rowcount = 9.0, cumulative cost =
{198.0 rows, 2738.589411415945 cpu, 0.0 io, 0.0 network, 2376.0 memory}, id =
247710
00-05 TopN(limit=[10]) : rowType = RecordType(DECIMAL(0, 0) YEAR,
DECIMAL(0, 0) MONTH, ANY rainfall): rowcount = 9.0, cumulative cost = {189.0
rows, 2729.589411415945 cpu, 0.0 io, 0.0 network, 2376.0 memory}, id = 247709
00-06 HashAgg(group=[{0, 1}], rainfall=[SUM($2)]) : rowType =
RecordType(DECIMAL(0, 0) YEAR, DECIMAL(0, 0) MONTH, ANY rainfall): rowcount =
9.0, cumulative cost = {180.0 rows, 2610.0 cpu, 0.0 io, 0.0 network, 2376.0
memory}, id = 247708
00-07 Project(YEAR=[$2], MONTH=[$3], PREP_IN=[$5]) : rowType =
RecordType(DECIMAL(0, 0) YEAR, DECIMAL(0, 0) MONTH, DECIMAL(0, 0) PREP_IN):
rowcount = 90.0, cumulative cost = {90.0 rows, 90.0 cpu, 0.0 io, 0.0 network,
0.0 memory}, id = 247707
00-08 Jdbc(sql=[SELECT *
FROM "SEA_PLATFORM"."WEATHER_CF"
WHERE "PREP_IN" IS NOT NULL]) : rowType = RecordType(VARCHAR(10) LOCATION,
TIMESTAMP(0) RECORDDATE, DECIMAL(0, 0) YEAR, DECIMAL(0, 0) MONTH, DECIMAL(0, 0)
DAY, DECIMAL(0, 0) PREP_IN, DECIMAL(0, 0) TAVG_F, DECIMAL(0, 0) TMAX_F,
DECIMAL(0, 0) TMIN_F): rowcount = 90.0, cumulative cost = {90.0 rows, 90.0 cpu,
0.0 io, 0.0 network, 0.0 memory}, id = 247626
I think based on 00-08, that the predicate "PREP_IN IS NOT NULL" is what is
pushed to Oracle. Am I reading that correctly? The Aggregation, Grouping and
Limit is all handled by Drill?
This is drill 1.10
Dan Holmes | Architect | Revenue Analytics, Inc.
300 Galleria Parkway, Suite 1900 | Atlanta, Georgia 30339
Direct: 770.859.1255 Cell: 404.617.3444
www.revenueanalytics.com<https://webmail.revenueanalytics.com/owa/redir.aspx?SURL=RqmyOJRm3r383jV2nPQLyg9BvjWZqMX4-tL3BHj81WfaslMWau_SCGgAdAB0AHAAOgAvAC8AdwB3AHcALgByAGUAdgBlAG4AdQBlAGEAbgBhAGwAeQB0AGkAYwBzAC4AYwBvAG0A&URL=http%3a%2f%2fwww.revenueanalytics.com>
LinkedIn<https://webmail.revenueanalytics.com/owa/redir.aspx?SURL=SrcaeiXxVTCDhl49ibCO7CHhTsNynunc_8gSjHDaikXaslMWau_SCGgAdAB0AHAAcwA6AC8ALwB3AHcAdwAuAGwAaQBuAGsAZQBkAGkAbgAuAGMAbwBtAC8AYwBvAG0AcABhAG4AeQAvAHIAZQB2AGUAbgB1AGUALQBhAG4AYQBsAHkAdABpAGMAcwAtAGkAbgBjAC0A&URL=https%3a%2f%2fwww.linkedin.com%2fcompany%2frevenue-analytics-inc->
|
Twitter<https://webmail.revenueanalytics.com/owa/redir.aspx?SURL=cdePsMV8TCGx8O_Rugbj-maE9C9DVT373vSJwbUc23faslMWau_SCGgAdAB0AHAAcwA6AC8ALwB0AHcAaQB0AHQAZQByAC4AYwBvAG0ALwBSAGUAdgBfAEEAbgBhAGwAeQB0AGkAYwBzAA..&URL=https%3a%2f%2ftwitter.com%2fRev_Analytics>
