Oracle Pass through statements vs SAS Enterprise Guide Generated Code

Experts,

Does anyone know if when building a query in SAS Enterprise Guide (Drop and Drag) reading Oracle tables; Does SAS use the Oracle indexes?

I know if you use Oracle pass-through code it does,  I was wondering about SAS Proc Sql code.
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
 
sdstuberCommented:
queries written in SAS "can" use indexes and if you are doing something simple that is effectively

select * from table where indexed_column = some_value;

then you almost certainly will use the index.

But ... the generated code is fairly generic subset that could be used on other databases as well.
So, you lose access to the more sophisticated syntax available in the database.

Things like analytics, modelling, subquery factoring, pattern matching, etc that can have much, much better response times and total throughput times.


This doesn't mean you shouldn't use SAS proc sql and generated statements but - if you want the best possible query performance then pass through will usually be it.
0
 
IanStatisticianCommented:
You can easily test if SAS will use the index.

Submit the following code  =>

data _null_;
    set  oralib.tablename;
    by   oracle_index;
run;

which will run (and tell you the number of records) if the index is used.

However if the table is already in sorted order (or has less than two rows) it will pass the test. In the case that the data happens to be in sorted order change the by statement to
   by DESCENDING oracle_index;
and run the test again.

For a more complicated situation, you could use PROC SQL to construct a view and then do the same test.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.