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