Your interpretation of the physical plan is correct. The "IS NOT NULL" predicate is pushed down to Oracle, while aggregation / sort / Limit is handled by Drill, although they could be pushed down as well. The JDBC storage plugin rules may need some enhancements.
On Thu, Jul 27, 2017 at 5:15 AM, Dan Holmes <[email protected]> wrote: > 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=RqmyOJRm3r383jV2nPQLyg9BvjWZqM > X4-tL3BHj81WfaslMWau_SCGgAdAB0AHAAOgAvAC8AdwB3AHcAL > gByAGUAdgBlAG4AdQBlAGEAbgBhAGwAeQB0AGkAYwBzAC4AYwBvAG0A&URL= > http%3a%2f%2fwww.revenueanalytics.com> > LinkedIn<https://webmail.revenueanalytics.com/owa/redir.aspx?SURL= > SrcaeiXxVTCDhl49ibCO7CHhTsNynunc_8gSjHDaikXaslMWau_ > SCGgAdAB0AHAAcwA6AC8ALwB3AHcAdwAuAGwAaQBuAGsAZQBkAGkAbgAuAGM > AbwBtAC8AYwBvAG0AcABhAG4AeQAvAHIAZQB2AGUAbgB1AGUALQBhAG4AYQB > sAHkAdABpAGMAcwAtAGkAbgBjAC0A&URL=https%3a%2f%2fwww. > linkedin.com%2fcompany%2frevenue-analytics-inc-> | Twitter< > https://webmail.revenueanalytics.com/owa/redir.aspx?SURL= > cdePsMV8TCGx8O_Rugbj-maE9C9DVT373vSJwbUc23faslMWau_ > SCGgAdAB0AHAAcwA6AC8ALwB0AHcAaQB0AHQAZQByAC4AYwBvAG0ALwBSAGU > AdgBfAEEAbgBhAGwAeQB0AGkAYwBzAA..&URL=https%3a%2f%2ftwitter. > com%2fRev_Analytics> > >
