## In which scenario would you not use an index even though using an index would increase efficiency?

Solved
Occasional Contributor
Posts: 11

# In which scenario would you not use an index even though using an index would increase efficiency?

[ Edited ]

SAS 9.4

I am learning Advanced SAS currently. The idxwhere=no options can be used to specify not to use an index. In what possible scenario would you tell SAS not to use indexes ? SAS predicts whether to use an index or not. If the program fulfils criteria for not using indexes, it's obvious even SAS knows those scenarios. Why would you decrease efficiency by telling SAS not to use an index?

Accepted Solutions
Solution
‎02-20-2016 03:02 PM
Posts: 3,852

## Re: In which scenario would you not use an index even though using an index would increase efficienc

I don't know if this is a good example but I shows one scenario where turning off the index changes what you get back.  Notice that using the index the values are returned in INDEX order while not using it they are returned in OBS order.

```41         options msglevel=i;
42         data byage;
43            set class;
44            where age in(12 15);
INFO: Index Age selected for WHERE clause optimization.
45            run;

NOTE: There were 9 observations read from the data set WORK.CLASS.
WHERE age in (12, 15);
NOTE: The data set WORK.BYAGE has 9 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time           0.02 seconds
cpu time            0.02 seconds

46         proc print;
47            run;

NOTE: There were 9 observations read from the data set WORK.BYAGE.
NOTE: PROCEDURE PRINT used (Total process time):
real time           0.02 seconds
cpu time            0.03 seconds

48         data byage;
49            set class(idxwhere=no);
50            where age in(12 15);
INFO: Data set option (IDXWHERE=NO) forced a sequential pass of the data rather than use of an index for where-clause processing.
51            run;

NOTE: There were 9 observations read from the data set WORK.CLASS.
WHERE age in (12, 15);
NOTE: The data set WORK.BYAGE has 9 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time           0.02 seconds
cpu time            0.03 seconds```

All Replies
Super User
Posts: 10,272

## Re: In which scenario would you not use an index even though using an index would increase efficienc

Remember that computers are dumb. This option comes into play when you prove to be more intelligent than the SAS optimization algorithm.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 11

## Re: In which scenario would you not use an index even though using an index would increase efficienc

Yes but I cannot imagine any scenario in which I would need to explicitly tell SAS not to use indexes even though I risk decreasing efficiency but get same results as when index was used. Can you help me or have you come across any such scenario?

Solution
‎02-20-2016 03:02 PM
Posts: 3,852

## Re: In which scenario would you not use an index even though using an index would increase efficienc

I don't know if this is a good example but I shows one scenario where turning off the index changes what you get back.  Notice that using the index the values are returned in INDEX order while not using it they are returned in OBS order.

```41         options msglevel=i;
42         data byage;
43            set class;
44            where age in(12 15);
INFO: Index Age selected for WHERE clause optimization.
45            run;

NOTE: There were 9 observations read from the data set WORK.CLASS.
WHERE age in (12, 15);
NOTE: The data set WORK.BYAGE has 9 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time           0.02 seconds
cpu time            0.02 seconds

46         proc print;
47            run;

NOTE: There were 9 observations read from the data set WORK.BYAGE.
NOTE: PROCEDURE PRINT used (Total process time):
real time           0.02 seconds
cpu time            0.03 seconds

48         data byage;
49            set class(idxwhere=no);
50            where age in(12 15);
INFO: Data set option (IDXWHERE=NO) forced a sequential pass of the data rather than use of an index for where-clause processing.
51            run;

NOTE: There were 9 observations read from the data set WORK.CLASS.
WHERE age in (12, 15);
NOTE: The data set WORK.BYAGE has 9 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time           0.02 seconds
cpu time            0.03 seconds```

Occasional Contributor
Posts: 11

Super User
Posts: 10,784

## Re: In which scenario would you not use an index even though using an index would increase efficienc

When you get most part of a table (like 70% 80%),not using index is faster than using index.

Occasional Contributor
Posts: 11

## Re: In which scenario would you not use an index even though using an index would increase efficienc

Agreed but SAS has an inbuilt algorithm that estimates output dataset. In the scenario you specified, had we not mentioned any index option, SAS would not have used indexing as about 70%-80% of dataset is being used.

Super User
Posts: 5,884

## Re: In which scenario would you not use an index even though using an index would increase efficienc

Yes there is an algorithm. But that can't foresee everything. So I guess that there is no straight answer. I'm thinking that in a real life example find the use of index inefficient and thenot turn that off explicitly for that query.
One situation I can imagine is the the index is heavily fragmented so that even if just 10% rows is returned, let's say that 50% of the data pages is read.
Data never sleeps
Occasional Contributor
Posts: 11

## Re: In which scenario would you not use an index even though using an index would increase efficienc

Yes, even i'm trying to imagine a scenario. Maybe what you say could happen.

🔒 This topic is solved and locked.