IDXWHERE= Table Option

Specifies whether SAS uses an index search or a sequential search to match the conditions of a WHERE clause.

Category: User Control of SAS Index Usage
Restrictions: Use with input data sets only
SAS data sets: Cannot be used with IDXNAME=
SPD Engine data sets: IDXWHERE=NO cannot be used with WHERENOINDEX=
Data source: SAS data set, SPD Engine data set
Syntax
Arguments
Details
Comparisons
Examples
Example 1: Specifying Index Usage
Example 2: Specifying No Index Usage
See Also

Syntax

IDXWHERE= YES | NO

Arguments

YES

tells SAS to choose the best index to optimize a WHERE clause, and to disregard the possibility that a sequential search of the data set might be more resource-efficient. This is the default value.

NO

tells SAS to ignore all indexes and satisfy the conditions of a WHERE clause with a sequential search of the data set.

Notes You cannot use the IDXWHERE= table option to override the use of an index to process a BY statement.
You cannot use the WHERENOINDEX= table option when IDXWHERE=NO is used.

Details

By default, to satisfy the conditions of a WHERE clause for an indexed data set, the software decides whether to use an index or to read the data set sequentially. The software estimates the relative efficiency and chooses the method that is more efficient.
You might need to override the software's decision by specifying the IDXWHERE= table option because the decision is based on general rules that occasionally might not produce the best results. That is, by specifying the IDXWHERE= table option, you are able to determine the processing method.
Note: The specification is not a permanent attribute of the data set and is valid only for the current use of the data set.

Comparisons

The IDXNAME= table option (which is supported only for SAS data sets) enables you to direct SAS to use a specific index.
The WHERENOINDEX= table option enables you to specify a list of indexes to exclude when making WHERE expression evaluations.

Examples

Example 1: Specifying Index Usage

This example uses the IDXWHERE= table option to tell SAS to decide which index is the best to optimize the WHERE clause. SAS then disregards the possibility that a sequential search of the data set might be more resource-efficient: 
create table mydata.empnew
   as select * from mydata.employee {option idxwhere=yes}
   where empnum < 2000;

Example 2: Specifying No Index Usage

This example uses the IDXWHERE= table option to tell SAS to ignore any index and to satisfy the conditions of the WHERE clause with a sequential search of the data set: 
create table mydata.empnew
   as select * from mydata.employee {option idxwhere=no}
   where empnum < 2000;

See Also

Table options:
IDXNAME= Table Option
WHERENOINDEX= Table Option
Copyright © SAS Institute Inc. All rights reserved.