IDXWHERE= Data Set Option

YES

uses indexes when processing WHERE expressions.

NO

ignores indexes when processing WHERE expressions.

Restriction:

You cannot use the IDXWHERE=NO and the WHERENOINDEX= options together.

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 supports four WHERE expression evaluation strategies. For more information, see SPDEWHEVAL= System Option. Strategies 1, 3, and 4 use available indexes and execute the indexed part of the WHERE expression. Evaluation strategy 2 executes the non-indexed part of the WHERE expression.

The first example shows that the evaluation strategy 2 is used in the WHERE expression because IDXWHERE=NO was specified. The second example shows that the evaluation strategy 1 was used because IDXWHERE=YES 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';

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';

CAUTION:

Do not arbitrarily suppress index use when using both WHERE and BY statements in combination.

When you use both a WHERE expression to filter the observations and a BY expression to order the observations, the filtered observations qualified by the WHERE expression 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 for WHERE processing greatly improves the filtering performance feeding into the sort step.  

Copyright © 2007 by SAS Institute Inc., Cary, NC, USA. All rights reserved.