Connecting to Linux in a Direct SQL Query

[Jordan_Epi]

My organization uses SAS Enterprise Guide. The SAS system writes work tables to a Linux environment, and also has some permanent space within this environment we can write tables to. However, we write almost all final tables to an Oracle server. 

We update our data hourly, but our tables are becoming so large (millions of rows, many GB) that write times for the final tables to Oracle are impeding our ability to update at this frequency. Using a direct SQL query can improve our write times dramatically ( from 50+ minutes for a data step or indirect SQL query to sub-5 minutes using direct query) when writing tables from one oracle location to another. However, to my knowledge, a direct oracle query can only be used if all datasets are within the oracle environment. 

Is there a way to pull from tables outside of oracle in a direct SQL query?

Example below, where work is SAS work or other Linux directory: 

PROC SQL NOPRINT;
CONNECT TO ORACLE (PATH='ORACLE.PATH'
USER='USERNAME'
PASSWORD=PASSWORD);
EXECUTE (
create table ORACLE.TEST_TABLE as
select *
from WORK.TEST_TABLE
) BY ORACLE;
DISCONNECT FROM ORACLE;
QUIT;

[SASKiwi]

Obviously moving data around different tables on the same Oracle server is going to be faster than moving data between different servers like SAS and Oracle.

Have you played with the DBINSERT and DBCOMMIT SAS options? These can make a huge difference in data upload performance.

Also sounds like bulk loading would be worth exploring given your data volumes. Refer to the SAS/ACCESS to Oracle doc for this.

At the end of the day upload speed will be limited to the size of the network pipe between SAS and Oracle.

[Jordan_Epi]

Thanks for the reply. I have been working with a couple IT teams in our organization and haven't had much luck in the way of SAS support (they just tell us the issue is SAS and to stop using it). 

I will look into those DBINST and DBCOMMIT options. 

[SASKiwi]

@Jordan_Epi  - That type of response is unfortunately quite common. They blame the tool not the IT infrastructure. With some experimentation it is possible to get very good database loading performance from SAS. 

[Sajid01]

Hello @Jordan_Epi 
As a general rule, move processing to where the data is. 
As long as the data is outside Oracle loading will take some time.
You will have to take a compressive review of your processes and resources to arrive at a solution.
With the information you have given, it will be difficult to say anything.
Please have a look at this paper. It has a comprehensive review of loading data into oracle.

https://support.sas.com/resources/papers/proceedings/proceedings/sugi29/106-29.pdf