Date: Mon, 23 May 2011 12:03:20 -0400
Reply-To: Ya Huang <ya.huang@AMYLIN.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Ya Huang <ya.huang@AMYLIN.COM>
Subject: Re: General considerations for efficient downloading data from
Oracle via SAS/access to Oracle?
I was wondering if I can have my own "WORK" space, so that I can
create temporary datasets/tables in the remote Oracle, then work on
them to get aggregate data to avoid transferring huge data over the network.
I guess what you recommended "myschema" is something like it. But how
do I create a schema? If the schema I'm using is a "data view", does it mean
I am not permitted to create a schema?
On Mon, 23 May 2011 07:49:37 -0700, Mary <mlhoward@AVALON.NET> wrote:
>Sometimes it is that when other users have access to the same table on
Oracle, your passthough is getting low priority. If you have rights to
write to your own schema in Oracle, sometimes making a copy of the data you
want to pull to your own schema will help. The Oracle code would be like
>create table myschema.table1 as
>select var,var from bigschema.table1;
>Then try to pull from myschema.table1, since no one else would be using
>--- ya.huang@AMYLIN.COM wrote:
>From: Ya Huang <ya.huang@AMYLIN.COM>
>Subject: Re: General considerations for efficient downloading data
from Oracle via SAS/access to Oracle?
>Date: Fri, 20 May 2011 14:00:25 -0400
>Yes, it is pulling data over a network. I would say oracle is 3000+ miles
>away from my PC. The final dataset is not very big (2 GB), and data only
>10 columns. I've tried to remove the where claus so there is nothing to
>be optimized, a straigforward downloading, it ended up taking more than one
>What's weird to me is that another dataset (0.5 GB), only took about
>2 minutes to download.
>Any possibility that different type of "table" in oracle will result in
>totally different efficiency, in terms of downloading speed?
>On Fri, 20 May 2011 13:34:37 -0400, Durrett, Cindy
>>Are you pulling it over a network? How many records are in myorac.a? How
>many in myorac.b?
>>We pull data similarly to how you've described across a network which
>sometimes slows things down. Also our larger datasets take longer than the
>smaller ones (larger = over 20 million records).
>>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Ya
>>Sent: Friday, May 20, 2011 1:29 PM
>>Subject: General considerations for efficient downloading data from Oracle
>via SAS/access to Oracle?
>>I need to "download" data from an Oracle database via SAS/Access for
>>I'm frustrated by the speed of downloading. I've tried to limited
>>the variable to be downloaded. For most of the data, time to download is
>>But a few of the dataset just take way too long (30 minutes +), even
>>I only selected 10 variables.
>>Wonder what's the general consideration to deal with this kind of
>>The code is basically like below:
>>libname myorac oracle path=&URL user=&UID pass=&PSS schema=&schema;
>> set myorac.a (keep=v1 v2 v3 .. v10);
>>create table y as
>>where id in (select id from x)