topic Re: Does SAS Use Oracle Indexes in SAS Programming

Does SAS Use Oracle Indexes

DavidPhillips2 — Wed, 01 Apr 2020 17:08:34 GMT

Our datasets are Oracle datasets that are accessed via SAS Enterprise guide through registered Metadata. Does this method use the Oracle Indexes?

Re: Does SAS Use Oracle Indexes

JBailey — Wed, 01 Apr 2020 18:52:38 GMT

Hi @DavidPhillips2

SAS generates SQL and passes it to Oracle. The Oracle Optimizer determines whether, or not, it uses an index. SAS does not directly use Oracle indexes.

You can help the Optimizer choose use indexes by ensuring that catalog statistics are current, and passing Oracle Hints.

Years ago, Tatyana Petrova and I wrote an SGF paper on database performance tuning. You may find it useful.

The SQL Tuning Checklist: Making Slow Queries a Thing of the Past

The paper discusses most of what you need to know to use indexes. I think we used Teradata for the examples, but Oracle will be similar. I will warn you; Oracle EXPLAIN PLANs are more difficult to use.

Best wishes,

Jeff

Re: Does SAS Use Oracle Indexes

ChrisNZ — Thu, 02 Apr 2020 02:38:10 GMT

Awesome paper Jeff.

Re: Does SAS Use Oracle Indexes

JBailey — Thu, 02 Apr 2020 13:22:51 GMT

Thanks, Chris. That means a lot. I appreciate it.

Re: Does SAS Use Oracle Indexes

ChrisNZ — Sun, 05 Apr 2020 10:32:57 GMT

@JBailey No worries. Credit where credit's due! 🙂

One question, do you know why libname option sql_functions=all is not the default?

Any downside to that justifies this precaution?

Re: Does SAS Use Oracle Indexes

JBailey — Mon, 06 Apr 2020 14:10:09 GMT

Hi @ChrisNZ,

What a great question. It is best to have questions in their own topic, So, create a new post and include my name (@JBailey) because the site will ping me. I can answer the question, quickly.

Best wishes,

Jeff