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

[krzy32]

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?

[Kurt_Bremser]

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

[krzy32]

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?

[data_null__ · Accepted solution]

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

[krzy32]

Thank you for your answer. Can you please post the log with the fullstimer option?

[Ksharp]

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

[krzy32]

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.

[LinusH]

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.

[krzy32]

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