topic Re: idxwhere in SAS Programming

idxwhere

proctice — Fri, 27 Apr 2018 02:27:45 GMT

Does idxwhere override where conditions that can be optimized and compound optimized from the link below?

Could it force an index to be used when the where includes an or condition with different variables?

Ex. where x in (some list) or y in (some list);

https://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000440261.htm

Re: idxwhere

art297 — Fri, 27 Apr 2018 02:30:55 GMT

It can, depending upon how it's used. According to the documentation you pointed to:

The IDXWHERE= data set option overrides the software's decision regarding whether to use an index to satisfy the conditions of a WHERE expression as follows:

IDXWHERE=YES tells SAS to decide which index is the best for optimizing a WHERE expression, disregarding the possibility that a sequential search of the data file might be more resource efficient.
IDXWHERE=NO tells SAS to ignore all indexes and satisfy the conditions of a WHERE expression by sequentially searching the data file.
Using an index to process a BY statement cannot be overridden with IDXWHERE=.

Art, CEO, AnalystFinder.com

Re: idxwhere

proctice — Fri, 27 Apr 2018 02:49:25 GMT

The part that is confusing about the documentation is that SAS is still deciding which index is best. So, I'm not sure what rules it uses when idxwhere=yes, if not the same rules it uses without that option.

I also wasn't sure if "the software's decision regarding whether to use an index" is referring to those optimization rules.

Re: idxwhere

art297 — Fri, 27 Apr 2018 02:49:07 GMT

The option can be used to force SAS to use an index, irrespective of whether its optimization routine suggests that it should or shouldn't. The idxname option can be used to specify a particular index to use.

Art, CEO, AnalystFinder.com