WHERENOINDEX= Table Option

Specifies a list of indexes to exclude when making WHERE clause evaluations.

Valid in:	SPD Server
Default:	No

Syntax

Required Argument

Example

Syntax

WHERENOINDEX=(name(s) )

Required Argument

name(s)

a list of index names that you want to exclude from the WHERE planner. If more than one name, separate with a space.

Example: Using the WHERNOINDEX= Table Option

You have a table PRECS with indexes defined as follows:

PROC DATASETS lib=spdscen;
modify precs(index=(hour89));
index create
  stser=(state serialno)
  occind=(occup industry)
  hour89;
quit;

When evaluating the next query, you want the server to exclude from consideration indexes for both the STATE and HOUR89 columns.

In this case, you know that the AND combination of the predicates for the OCCUP and INDUSTRY columns will produce a very small yield. Few rows satisfy the respective predicates. To avoid the extra index I/O (machine time) that the query requires for a full-indexed evaluation, use the following SAS code:

PROC SQL;
create table hr80spds
  as select
    state,
    age,
    sex,
    hour89,
    industry,
    occup
  from spdscen.precs(wherenoindex=(stser hour89))
    where occup='022'
    and state in('37','03','06','36')
    and industry='012'
    and hour89 > 40;
quit;

Note: Specify index names in the WHERENOINDEX= list, not the column names. The example excludes both the composite index for the STATE column STSER and the simple index HOUR89 from consideration by the WHINIT WHERE planner.

Last updated: February 8, 2017