Oracle Pass through statements vs SAS Enterprise Guide Generated Code


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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
You can easily test if SAS will use the index.

Submit the following code  =>

data _null_;
    set  oralib.tablename;
    by   oracle_index;

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.