I’m querying an Oracle table using three predicates:
SELECT design, FACILITY
FROM PROCESS
WHERE design_auth(design) = 1 AND facility_auth(FACILITY) = 1
AND DESIGN='ABC'
In this query design_auth and facility_auth are UDF’s, so of course need to run in Dremio, but Design='ABC' I would expect to be pushed down to Oracle. Instead, the Dremio plan runs an unrestricted SELECT * in Oracle and filters all three conditions locally.
Here is the Final Physical Transform:
00-00 Screen : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition, BIGINT FileSize): rowcount = 72978.9375, cumulative cost = {1.601401151875E7 rows, 6.410786177743543E8 cpu, 5.449094E8 io, 5.449094E8 network, 0.0 memory}, id = 1832
00-01 Project(Fragment=[$0], Records=[$1], Path=[$2], Metadata=[$3], Partition=[$4], FileSize=[$5]) : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition, BIGINT FileSize): rowcount = 72978.9375, cumulative cost = {1.6006713625E7 rows, 6.410713198806044E8 cpu, 5.449094E8 io, 5.449094E8 network, 0.0 memory}, id = 1831
00-02 WriterCommitter(final=[/dremio/results/2223ef40-a582-130a-7025-dac715bd7000]) : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition, BIGINT FileSize): rowcount = 72978.9375, cumulative cost = {1.59337346875E7 rows, 6.410713155018681E8 cpu, 5.449094E8 io, 5.449094E8 network, 0.0 memory}, id = 1830
00-03 Writer : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition, BIGINT FileSize): rowcount = 72978.9375, cumulative cost = {1.586075575E7 rows, 6.409983365643681E8 cpu, 5.449094E8 io, 5.449094E8 network, 0.0 memory}, id = 1829
00-04 Project(DESIGN=[$0], FACILITY=[$1]) : rowType = RecordType(VARCHAR(65536) DESIGN, VARCHAR(65536) FACILITY): rowcount = 72978.9375, cumulative cost = {1.57877768125E7 rows, 6.409253576268681E8 cpu, 5.449094E8 io, 5.449094E8 network, 0.0 memory}, id = 1828
00-05 Project(DESIGN=[CAST('ABC'):VARCHAR(65536) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], FACILITY=[$2]) : rowType = RecordType(VARCHAR(65536) DESIGN, VARCHAR(65536) FACILITY): rowcount = 72978.9375, cumulative cost = {1.5714797875E7 rows, 6.409253561672894E8 cpu, 5.449094E8 io, 5.449094E8 network, 0.0 memory}, id = 1827
00-06 SelectionVectorRemover : rowType = RecordType(VARCHAR(65536) FACILITY, VARCHAR(65536) DESIGN): rowcount = 72978.9375, cumulative cost = {1.56418189375E7 rows, 6.406334396875E8 cpu, 5.449094E8 io, 5.449094E8 network, 0.0 memory}, id = 1826
00-07 Filter(condition=[AND(DESIGN_AUTH($4), FACILITY_AUTH($2), =($4, 'ABC'))]) : rowType = RecordType(VARCHAR(65536) FACILITY, VARCHAR(65536) DESIGN): rowcount = 72978.9375, cumulative cost = {1.556884E7 rows, 6.4056046075E8 cpu, 5.449094E8 io, 5.449094E8 network, 0.0 memory}, id = 1825
00-08 Jdbc(sql=[SELECT *
FROM "PROCESS"]) : rowType = RecordType(VARCHAR(65536) FACILITY, VARCHAR(65536) DESIGN): rowcount = 7784420.0, cumulative cost = {7784420.0 rows, 5.449094E8 cpu, 5.449094E8 io, 5.449094E8 network, 0.0 memory}, id = 1756
If I run a query without the UDF’s, then the plan includes the predicate when building and querying Oracle. If I reorganize the query, using a sub-query for the Oracle query with Predicate, and put the UDF’s on the outside, than it also runs correctly.