Function pushdown to Oracle

Is there a reference anywhere of which database functions are supported for pushdown?

I am working with a very simple query on an Oracle datasource, and discovered that a “substr” call resulted in the query being broken into two parts, which would cause it to run forever.

@Joe There is no external documentation on what functions get pushed down into Oracle. Did the substr get pushed down causing an index in Oracle not to get used? or was it not pushed down and caused a large scan? Any chance you have the profile?

1 Like

Hi, Bali. The original query includes a join between two large tables. When I include a substr() in SELECT, then I see Dremio creating two queries to Oracle.

I can’t upload the profile to a public forum, but here’s my query:

explain plan for
select substr(event_detail_value,1,1)
from
    xxx.ads_event ae
    join xxx.ads_event_detail ad
    on ae.event_id = ad.event_id
where
    ae.start_time >= current_date()

and the two physical queries I see in the profile:

SELECT "ADS_EVENT_DETAIL"."EVENT_ID", "ADS_EVENT_DETAIL"."EVENT_DETAIL_VALUE"
FROM xxx."ADS_EVENT_DETAIL"
SELECT "EVENT_ID"
FROM (SELECT "ADS_EVENT"."EVENT_ID", "ADS_EVENT"."START_TIME"
FROM xxx."ADS_EVENT") "ADS_EVENT"
WHERE "START_TIME" >= TIMESTAMP '2024-06-13 00:00:00.000'

Removing the substr (just SELECT event_detail_value) produces a single physical query:

[SELECT "ADS_EVENT_DETAIL"."EVENT_DETAIL_VALUE" "event_detail_value"
FROM (SELECT "EVENT_ID"
FROM (SELECT "ADS_EVENT"."EVENT_ID", "ADS_EVENT"."START_TIME"
FROM xxx."ADS_EVENT") "ADS_EVENT"
WHERE "START_TIME" >= TIMESTAMP '2024-06-13 00:00:00.000') "ADS_EVENT"
INNER JOIN (SELECT "ADS_EVENT_DETAIL"."EVENT_ID", "ADS_EVENT_DETAIL"."EVENT_DETAIL_VALUE"
FROM xxx."ADS_EVENT_DETAIL") "ADS_EVENT_DETAIL" ON "ADS_EVENT"."EVENT_ID" = "ADS_EVENT_DETAIL"."EVENT_ID"])