Date: Tue, 11 Nov 2003 13:17:09 -0500
Reply-To: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject: Re: DATA step utilizing an oracle index
You don't specify the WHERE conditions that might benefit from an index.
Assuming that Oracle index improves a SQL query invoked in the Oracle shell,
you should be able to get virtually the same improvement in a SAS SQL
'pass-thru' query of the form:
Connect to Oracle <connection string> ;
create table <SAS dataset name> as
select * from CONNECTION TO ORACLE(select <Oracle table column list>
from <Oracle DB table/view reference>
SAS SQL passes it directly to the Oracle DB system. Oracle executes it there
and returns the yield of the query. SAS SQL materializes the yield of a
query as a dataset or data stream (when executed as a view).
From: Shaun [mailto:Shaun.Carlson@WELLCARE.COM]
Sent: Tuesday, November 11, 2003 12:08 PM
Subject: DATA step utilizing an oracle index
I am fairly new SAS user, and I am querying a rather large database (tens of
millions of records) to pull based on a few criteria. The query took much
longer than I expected from a SAS process (15-20 minutes), so I sent my
query to the DBA so he could index the table. I just got a reply from him
saying the index has been created and analyzed, so I re-ran the process. It
is still taking just as long.
Do I have to give SAS a special command so that it knows there is an index
it can make use of? My data step is very simple:
set _oracletable_( keep = _fields_I'm_interested_in_ );
Thanks for any help you can offer.