LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (November 2003, week 2)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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
Comments: To: Shaun <Shaun.Carlson@WELLCARE.COM>
Content-Type: text/plain

Shaun: 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> ;

proc sql; create table <SAS dataset name> as select * from CONNECTION TO ORACLE(select <Oracle table column list> from <Oracle DB table/view reference> where .... ) ; quit;

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). Sig

-----Original Message----- From: Shaun [mailto:Shaun.Carlson@WELLCARE.COM] Sent: Tuesday, November 11, 2003 12:08 PM To: SAS-L@LISTSERV.UGA.EDU 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:

data _dataset_; set _oracletable_( keep = _fields_I'm_interested_in_ ); where _conditions_; run;

Thanks for any help you can offer.

Shaun


Back to: Top of message | Previous page | Main SAS-L page