IDXWHERE= is used with the SPD Engine's WHERE expression planning software called WHINIT. WHINIT tests the performance of index use with WHERE processing in various applications. Set the SAS system option MSGLEVEL=I so that the WHERE processing information is output to the SAS log.

When you use the IDXWHERE= data set option and the BYSORT=YES option, the following messages are written to the SAS log:

If IDXWHERE=YES and there is an index on the BY variable, the index is used to order the rows of the table. The following message is written to the SAS log:
```
Note: BY ordering was produced by using an index for
table tablename.
```
If IDXWHERE=NO or IDXWHERE=YES and there is no index on the BY variable, SPD Engine performs an automatic sort to order the rows of the table. The following message is written to the SAS log:
```
Note: BY ordering was produced by performing an
automatic sort on table tablename.
```

The SPD Engine uses WHINIT, a rules-based WHERE expression planner, to select the most appropriate evaluation strategy for a query. The SAS system option MSGLEVEL=I surfaces WHINIT messages to the SAS log that can help you determine whether one or more indexes are used in a query. For more details about WHINIT, see SPDEWHEVAL= System Option.

Note: Do not arbitrarily suppress index use when using WHERE and BY statements together. You use a WHERE statement to filter the observations and a BY statement to order the observations. The filtered observations qualified by the WHERE statement are fed directly into a sort step as part of the parallel WHERE expression evaluation. The final, ordered observation set is produced as the result. Index use in WHERE processing greatly improves the filtering and feeding performance into the sort step.

Examples

Example 1: Using WHINIT Log Output with IDXWHERE=NO

This example shows that evaluation strategy 2 is used in the WHERE expression because IDXWHERE=NO was specified.

IDXWHERE=NO

34   options msglevel=i;
35   proc means data=permdata.customer(idxwhere=no);
36      var sales;
37      where state="CA";
38   run;
whinit: WHERE (sstate='CA')
whinit returns: ALL EVAL2
NOTE: There were 2981 observations read from the data set
      PERMDATA.CUSTOMER. WHERE state='CA';

Example 2: Using WHINIT Log Output with IDXWHERE=YES

This example shows that evaluation strategy 1 was used because IDXWHERE=YES was specified.

IDXWHERE=YES

39   proc means data=permdata.customer(idxwhere=yes);
40      var sales;
41      where state="CA";
42   run;
whinit: WHERE (sstate='CA')
 --
whinit: SBM-INDEX STATE uses 45% of segs (WITHIN maxsegratio 75%)
whinit returns: ALL EVAL1(w/SEGLIST)
NOTE: There were 2981 observations read from the data set
      PERMDATA.CUSTOMER. WHERE state='CA';