I have a fairly complex Oracle query that I’m trying to implement in Dremio. In Oracle, the query completes in about a minute and produces 30k rows. But in Dremio, the query runs for a half hour before causing Oracle to run out of temp space and fail the query. I believe this is because Dremio is generating multiple “select *” queries on several very large tables.
Is it possible to create a VDS that uses the underlying database’s native SQL? If so, then I could use my query and create a reflection on it.
Thanks for the update. As long as Dremio is able to parse the SQL you should be fine. For example if you create a VDS based on the below SQL for example, it would fail as we do not recognize dual and sysdate in Dremio
Another example – the query I am executing will produce one row per day for the past two years. But the underlying table has several million rows. The query that Dremio is submitting to Oracle is a “select *” on all of the tables in my original query. It’s been running now for over an hour as it’s ingesting all of the source data.
Are you trying to select several million rows or you have a FILTER for selecting last 2 years that is not getting push down and doing a FTS on the Oracle table?
Why not implement your query as a view in Oracle and connect Dremio to the view?
Dremio’s query parser and supported syntax is different from that of Oracle, and so variations in SQL exist in some places. Most queries can be rewritten to use Dremio’s SQL, but not always as each DBMS has specialized features with their own syntax.
You are suggesting an interesting feature, effectively “eval” this query in the underlying DBMS. That’s something we could consider, but for now using a view in the underlying DBMS is probably the best bet.
Hi, Balaji. I’m attaching one profile for a reflection refresh that I recently ran. I reduced the time period to reduce the size of the data set. This has 52k input rows and 62 output rows.
Incidentally, this reflection is not being used, even if I do a “select *” on the VDS. But I think that’s an unrelated issue.
I’m sure that would work, but in this specific case I’m using a read only Oracle ID that does not have permission to create views. But I’m also evaluating the feasibility of using Dremio to accelerate existing queries that are very complex or contain database function calls.