BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
proctice
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

 

View solution in original post

3 REPLIES 3
art297
Opal | Level 21

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

 

proctice
Quartz | Level 8

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.  

art297
Opal | Level 21

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 793 views
  • 1 like
  • 2 in conversation