MINMAXVARLIST= Table Option

Creates a list that documents the minimum and maximum values of specified columns.

Valid in:	SPD Server
Interaction:
Note:	SPD Server WHERE clause evaluations use MINMAXVARLIST= lists to include or eliminate member tables in a server dynamic cluster table from SQL evaluation scans.
See:	MINMAX Variable List

Syntax

Required Argument

Details

Example

Syntax

MINMAXVARLIST=(variable-name(s) )

Required Argument

(variable-name(s) )

server table column names. If there is more than one name, separate the names with a space.

Details

The MINMAXVARLIST= table option is used on server tables that will become members of a dynamic cluster table. The option facilitates searches where specific members in the dynamic cluster table contain a set or range of values. An example would be sales data for a given month.

When a server SQL subsetting WHERE clause specifies specific months from a range of sales data, the WHERE planner checks the MIN and MAX list. Based on the MIN and MAX list information, the server WHERE planner includes or eliminates member tables in the dynamic cluster for evaluation.

MINMAXVARLIST= uses the list of columns that you submit to build the 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 cluster tables, it also works on standard server tables. MINMAXVARLIST= can help reduce the need to create many indexes on a table, which can save valuable resources and space.

Example

%let host=kaboom ;
%let port=5201 ;

LIBNAME mylib sasspds "path3"
   server=&host..&port
   user='anonymous' ;

/* Create three tables called */
/* xy1, xy2, and xy3.         */

data mylib.xy1(minmaxvarlist=(x y));
  do x = 1 to 10;
  do y = 1 to 3;
  output;
  end;
end;
run;

data mylib.xy2(minmaxvarlist=(x y));
  do x = 11 to 20;
  do y = 4 to 6 ;
  output;
  end;
end;
run;

data mylib.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=mylib;
   cluster create cluster_table
      mem=xy1
      mem=xy2
      mem=xy3
     quit;


/* Enable WHERE evaluation to see  */
/* how the SQL planner selects     */
/* members from the cluster. Each  */
/* member is evaluated using the   */
/* min-max list.                   */

%let SPDSWDEB=YES;


/* The first member has true rows  */

PROC PRINT data=mylib.cluster_table ;
   where x eq 3
   and y eq 3;
run;


/* Examine the other tables */

PROC PRINT data=mylib.cluster_table ;
   where x eq 3
   and y eq 3 ;
run;

PROC PRINT data=mylib.cluster_table ;
   where x eq 3
   and y eq 3;
run;

PROC PRINT data=mylib.cluster_table ;
   where x between 1 and 10
   and y eq 3;
run;

PROC PRINT data=mylib.cluster_table ;
   where x between 11 and 30
   and y eq 8 ;
run;


/* Delete the dynamic cluster table. */

PROC DATASETS lib=mylib nolist;
   delete cluster_table ;
quit ;

Last updated: February 8, 2017