Function pushdown to Oracle

1

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.

2

@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
3

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"])
4

@Joe Understood, looks like the substr is not pushing down the join. Let me test this and see if I can repro

5

@Joe I was able to run a query like below and the join did get push down. What version of Dremio are you using?

SELECT substr(e.FIRST_NAME,1,1) first_letter, e.LAST_NAME, e.SALARY, e.HIRE_DATE, d.DEPARTMENT_NAME
FROM localoracle.HR.EMPLOYEES e JOIN localoracle.HR.DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
AND e.HIRE_DATE >= '1990-06-17 00:00:00'
6

Hi, Bali. I just tried it in version 25 with the same result. I realized that the underlying column in my query (event_detail_value) is a CLOB. Would that be the reason for this behavior?

7

@Joe Just to narrow it down, can you run the query without that column?