SPDSNIDX Macro Variable

Specifies whether to use the table's indexes when processing WHERE clauses. SPDSNIDX can also be used to disable index use for BY order determination.

Valid in:	SPD Server
Default:	NO
Restrictions:	SPDSNIDX affects index usage for BY ordering only if index usage for BY sorts is enabled on the server. The corresponding server parameter is BYINDEX.
Restrictions:	Assignments for macro variables with character input (for example, YES \| NO \| BINARY arguments) must be entered in uppercase (capitalized).
Interaction:	Corresponding table option is NOINDEX=.

Syntax

Required Arguments

Details

Example

Syntax

SPDSNIDX=YES | NO

Required Arguments

YES

ignores indexes when processing WHERE clauses.

NO

uses indexes when processing WHERE clauses.

Details

Set SPDSNIDX=YES to test the effect of indexes on performance or for specific processing. Do not use YES routinely for normal processing.

Example

You havecreated an index for the Type column of table TempData.AudiCars but decide to test whether it is necessary for your processing. You set SPDSNIDX=YES and a PROC PRINT statement, then set SPDSNIDX=NO and a PROC PRINT statement, so that you can compare processing. You also set the SPDSWDEB macro variable.

Set SPDSNIDX to YES to ignore index:

libname tempdata sasspds "conversion_area" server=husky.5105
    user="siteusr1" password="secret";

proc sql;
drop table tempdata.audicars;

create table tempdata.audicars as
select * from sashelp.cars
where make="Audi";

create index type on tempdata.audicars(type);

quit;

/*Turn on the macro variable SPDSWDEB */
/* to show that the index is not used */
/* during the table processing. */
%let spdswdeb=YES;

/* Set SPDSNIDX to YES to ignore index */
%let spdsnidx=YES;

title "Sedans manufactured by Audi";
proc print data=tempdata.audicars;
 where type="Sedan";
run;

/* Set SPDSNIDX to NO to ignore index */
%let spdsnidx=NO;

title "Sedans manufactured by Audi";
proc print data=tempdata.audicars;
 where type="Sedan";
run;

The following information was written to the log for the PROC PRINT request that uses SPDSNIDX=YES:

207  /*Turn on the macro variable SPDSWDEB */
208  /* to show that the index is not used */
209  /* during the table processing. */
210  %let spdswdeb=YES;
211
212  /* Set SPDSNIDX to YES to ignore index */
213  %let spdsnidx=YES;
214
215  title "Sedans manufactured by Audi";
216  proc print data=tempdata.audicars;
217   where type="Sedan";


whinit: WHERE (type='Sedan')
whinit: wh-tree presented
           /-NAME = [type]
 --CEQ----|
           \-LITC = ['Sedan']
whinit returns: ALL EVAL2

218  run;



whinit: WHERE (type='Sedan')
whinit: wh-tree presented
           /-NAME = [type]
 --CEQ----|
           \-LITC = ['Sedan']
whinit returns: ALL EVAL2



whinit: WHERE (type='Sedan')
whinit: wh-tree presented
           /-NAME = [type]
 --CEQ----|
           \-LITC = ['Sedan']
whinit returns: ALL EVAL2
NOTE: There were 13 observations read from the data set TEMPDATA.AUDICARS.
      WHERE type='Sedan';

The following information was written to the log for the PROC PRINT request that uses SPDSNIDX=NO:

220  %let spdsnidx=NO;
221
222  title "Sedans manufactured by Audi";
223  proc print data=tempdata.audicars;
224   where type="Sedan";

whinit: WHERE (type='Sedan')
whinit: wh-tree presented
           /-NAME = [type]
 --CEQ----|
           \-LITC = ['Sedan']
whinit: wh-tree after split
 --<empty>
whinit: INDEX Type uses 100% of segs (WITHIN maxsegratio 100%) 
whinit: INDEX tree after split
            /-NAME = [type] <1>INDEX Type (type)
  --CEQ----|
            \-LITC = ['Sedan']
whinit costing: 1 segs with est 1% yield reduces whthreads from 32 to 1 
whinit returns: ALL EVAL1(w/SEGLIST)  
225  run;

whinit: WHERE (type='Sedan') 
whinit: wh-tree presented 
           /-NAME = [type]
  --CEQ----|
            \-LITC = ['Sedan'] 
whinit: wh-tree after split 
 --<empty>
whinit: INDEX Type uses 100% of segs (WITHIN maxsegratio 100%) 
whinit: INDEX tree after split
            /-NAME = [type] <1>INDEX Type (type)
  --CEQ----|
            \-LITC = ['Sedan']
whinit costing: 1 segs with est 1% yield reduces whthreads from 32 to 1 
whinit returns: ALL EVAL1(w/SEGLIST)  

whinit: WHERE (type='Sedan') 
whinit: wh-tree presented
            /-NAME = [type]
  --CEQ----|
            \-LITC = ['Sedan']
whinit: wh-tree after split  
--<empty>
whinit: INDEX Type uses 100% of segs (WITHIN maxsegratio 100%) 
whinit: INDEX tree after split 
           /-NAME = [type] <1>INDEX Type (type)
 --CEQ----|
           \-LITC = ['Sedan'] 
whinit costing: 1 segs with est 1% yield reduces whthreads from 32 to 1 
whinit returns: ALL EVAL1(w/SEGLIST)
NOTE: There were 13 observations read from the data set TEMPDATA.AUDICARS.
      WHERE type='Sedan';

The

whinit
                        returns

message shows ALL EVAL1 when the index is used, and ALL EVAL2 when a sequential pass is used.

SPDSNIDX Macro Variable

Syntax

Required Arguments

YES

NO

Details

Example

See Also