Does SAS Use Oracle Indexes

[DavidPhillips2]

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

[JBailey · Accepted solution]

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

[ChrisNZ]

Awesome paper Jeff.

[JBailey]

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

[ChrisNZ]

@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?

[JBailey]

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