SPD Server contains
a table option called MINMAXVARLIST=. The primary purpose of MINMAXVARLIST=
is for use with SPD Server dynamic cluster tables, where specific
members in the dynamic cluster contain a set or range of values, such
as sales data for a given month. When an SPD Server SQL subsetting
WHERE clause specifies specific months from a range of sales data,
the WHERE planner checks the MIN and MAX variable list. Based on the
MIN and MAX list information, the SPD Server WHERE planner includes
or eliminates member tables in the dynamic cluster for evaluation.
Use the MINMAXVARLIST=
table option with either numeric or character-based columns. MINMAXVARLIST=
uses the list of columns you submit to build a variable list. The
MINMAXVARLIST= list contains only the minimum and maximum values for
each column. The WHERE clause planner uses the index to filter SQL
predicates quickly, and to include or eliminate member tables belonging
to the cluster table from the evaluation.
Although the MINMAXVARLIST=
table option is primarily intended for use with dynamic clusters,
it also works on standard SPD Server tables. MINMAXVARLIST= can help
reduce the need to create many indexes on a table, which can save
valuable resources and space.
The MINMAXVARLIST= table
option is available only when a table is being created or defined.
If a table has a MINMAXVARLIST= variable list, moving or copying the
table will destroy the variable list unless MINMAXVARLIST= is specified
in the table output.
%let domain=path3 ;
%let host=kaboom ;
%let port=5201 ;
LIBNAME &domain sasspds "&domain"
server=&host..&port
user='anonymous' ;
/* Create three tables called */
/* xy1, xy2, and xy3. */
data &domain..xy1(minmaxvarlist=(x y));
do x = 1 to 10;
do y = 1 to 3;
output;
end;
end;
run;
data &domain..xy2(minmaxvarlist=(x y));
do x = 11 to 20;
do y = 4 to 6 ;
output;
end;
end;
run;
data &domain..xy3(minmaxvarlist=(x y));
do x = 21 to 30;
do y = 7 to 9 ;
output;
end;
end;
run;
/* Create a dynamic cluster table */
/* called cluster_table out of */
/* new tables xy1, xy2, and xy3 */
PROC SPDO library=&domain ;
cluster create cluster_table
mem=xy1
mem=xy2
mem=xy3
maxslot=10;
quit;
/* Enable WHERE evaluation to see */
/* how the SQL planner selects */
/* members from the cluster. Each */
/* member is evaluated using the */
/* min-max variable list. */
%let SPDSWDEB=YES;
/* The first member has true rows */
PROC PRINT data=&domain..cluster_table ;
where x eq 3 and y eq 3;
run;
/* Examine the other tables */
PROC PRINT data=&domain..cluster_table ;
where x eq 19
and y eq 4 ;
run;
PROC PRINT data=&domain..cluster_table ;
where x eq 22
and y eq 9;
run;
PROC PRINT data=&domain..cluster_table ;
where x between 1 and 10
and y eq 3;
run;
PROC PRINT data=&domain..cluster_table ;
where x between 11 and 30
and y eq 8 ;
run;
/* Delete the dynamic cluster table. */
PROC SPDO library=&domain ;
cluster undo cluster_table ;
quit;
PROC DATASETS lib=&domain nolist;
delete xy1 xy2 xy3 ;
quit ;