How to Affect the WHERE Planner

To turn off dynamic WHERE-costing, specify

%let SPDSWCST=NO;  

To turn on WHINIT planning output, specify

%let SPDSWDEB=YES;  

To affect the WHERE planner SPD index pre-evaluation, specify

%let SPDSIRAT=index-segment-ratio; 

The SPDSIRAT= macro variable specifies a maximum percentage (ratio) for the number of segments in the hybrid bitmap that must contain the index value before the WHERE planner should pre-evaluate a segment list.

The segment list enables the planner to launch threads only for segments that contain the value. If the value number exceeds the ratio, the planner performs no pre-evaluation. Instead, the planner launches a thread for each segment in the table.

The SPDSIRAT= macro variable option can be used to ensure that time spent in pre-evaluation does not exceed the cost of launching a thread for each segment in the table. By default SPDSIRAT= is set to 75 percent. This means that if an index value is contained in 75 percent or less of the index segments, the hybrid bitmap logic will pre-evaluate the value and return a list of segments to the WHERE clause planner. If more than 75 percent of the index segments contain the target index value, the time spent on pre-evaluation might be more than the time saved by skipping a small number of segments.

For some tables 75 percent might not be the optimal setting. To determine a better setting, run a performance benchmark, adjust the percentage, and rerun the performance benchmark. Comparing results will show you how the specific data population you are querying responds to shifting the index-segment ratio. The allowable range to adjust the setting value is from 0 to 100, where 0 means never perform WHERE clause pre-evaluation, and 100 means always perform WHERE clause pre-evaluation.

To suppress WHINIT use of any index, specify the no index SPD Server macro variable or the corresponding SPD Server table option:

%let SPDSNIDX=YES;

data _null_;
set foo.a (noindex=yes); 

By default, when WHINIT detects equality predicates that have indexes, it chooses EVAL1. However, the user can decide that sequential EVAL3 or EVAL4 methods are better. For example, in an equality WHERE predicate such as where x = 3, WHINIT will default to EVAL1 to evaluate the clause. If a user knows that the table queried has only a few records that can satisfy this predicate, EVAL3 might be a better choice. To force WHINIT to choose EVAL3/4, specify:

%let SPDSWSEQ=YES;  

Controls whether the server uses dynamic WHERE-costing. When dynamic WHERE-costing is disable, the rules-based WHINIT heuristic is used to tune WHERE clauses for performance. The default setting is for NOWHERECOSTING.

A user might decide that one or more indexes selected by a WHINIT plan are not the best choice. This can occur because WHINIT is rule-based, not cost-based. Sometimes WHINIT selects a less-than-optimal plan. WHINIT's use of specific indexes can be affected by specifying the SPD Server option WHERENOINDEX= in your DATA step.

data _null_;
set foo.a (wherenoindex=(idx_abc d))

This example specifies that WHINIT not use index idx_abc and index d.

Most rule-based planners, including WHINIT from SPD Server, assume that the index has a uniform distribution of values between the upper and lower value boundaries. This means if data values range between 2 and 10, that there are an equal number of 3s and 4s, and so on. When the assumption of a uniform distribution is false, an indexed predicate can return a large number of records. In turn, this causes WHINIT's indexed plan to run slower than a sequential read of the entire table. In this case the index should be suppressed.

Here is another, more subtle instance. When the WHERE clause uses only the front part of the key, WHINIT selects a composite index. Assume an index abcd on columns A, B, C, and D, and an index e on column E, and specify the WHERE clause

where a = 3 and e = 5; 

Normally, WHINIT will select both indexes (abcd and e) and choose EVAL1. However, using the index abcd just to interrogate a might return a large number of records. In this case, suppressing the abcd index might be a good idea. If so, WHINIT will still choose EVAL1 for e = 5, or EVAL3 if SPDSWEV1=NO, and EVAL2, the post-filter, for a = 3.