Join from SAS Linux x64 to Oracle db table generating additional long running query on Oracle

[KirstenM]

When running the following join in SAS Enterprise Guide, an additional query is being generated on the Oracle DB which is extremely long running and is causing production performance issues.  Does anyone have any idea how to stop this additional query being generated on Oracle?

Actual query submitted on Linux platform (modified slightly for this platform):

proc sql;
create table work.outputtable as
select t1.filename,
t1.int_cell_phone,
t1.response_dttm,
t1.country,
t2.account_id,
t2.org_id
from work.SAS_table1 t1 inner join
ci_mart.ma_account t2 /*** oracle table ***/
on t1.customer_rk = t2.customer_rk;
quit;

Additional long running query being generated on Oracle "automatically" (ie. not in the submitted code):

SELECT "ACCOUNT_ID", "ORG_ID", "CUSTOMER_RK" FROM CI_MART.MA_ACCOUNT WHERE ((("CUSTOMER_RK"=:"CUSTOMER_RK") OR (("CUSTOMER_RK" IS NULL ) AND ( :"CUSTOMER_RK" IS NULL ))))

[Sajid01]

The oracle table (ci_mart.ma_account t2) you are connecting to must be a view.

So  when a view is referenced, the query  referencing the view is executed. This is the normal behavior and cannot be avoided.

Discuss with Oracle team to find a solution.

[Sajid01]

Further to what I said earlier I would like clarify that the additional long running query pertains to the view.

Your Oracle team can confirm this. 
You can engage with them if there are issues. 

[KirstenM]

Spoiler

Hi there Thanks for your feedback.  MA_ACCOUNT is not view, it is a table. I have logged a SAS track regarding this.

[ChrisNZ]

This is happening exactly as you'd expect: You are writing a SAS query, and asking SAS to translate it into Oracle Syntax, which it does. You could write your own Oracle query if you preferred.

You also ask SAS to merge a SAS table to an Oracle table. The only way is for SAS to read the whole Oracle table. This is slow.

A better option -if you are only interested in a small subset of the Oracle table- is to upload the keys of interest to Oracle and subset there.

Here, SAS seems to have inserted bind variables (prefixed with a colon). My knowledge of Oracle reaches its limit, and I don't know why this happened. I've never seen SAS do this before. If it's a way to pass key values from SAS to Oracle, it's clever, but probably very inefficient for a large number of keys.

To recap:

- If you merge data in SAS, the Oracle data must be downloaded to SAS. Obviously. 

- If the Oracle table is much larger than what you need, you can subset by uploading the desired keys to a temp Oracle table, or by inserting the keys as part of the SAS SQL query in a where clause. The second method is limited to queries with a small number of key values.

[KirstenM]

Thanks for your feedback.  It's the null queries that Oracle is generating that I am trying to get resolved.  I have logged a SAS track.