IDXWHERE= Data Set Option

Specifies to use indexes when processing WHERE expressions in the SPD Engine.

Valid in:	DATA step and PROC step
Default:	YES
Restriction:	WHERENOINDEX= option cannot be used with IDXWHERE=NO option
Engine:	SPD Engine only

Syntax

Details

Example

Syntax

IDXWHERE=YES | NO

Required Arguments

YES: uses indexes when processing WHERE expressions.

NO: ignores indexes when processing WHERE expressions.

Restriction: You cannot use the IDXWHERE=NO option and the WHERENOINDEX= option together.

Details

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

Example: WHINIT Log Output (MSGLEVEL=I)

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

Note: 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.