When using indexes, WHERE-based queries pre-evaluate segments. The segments are scanned
for candidates that match one or more predicates in the
WHERE clause. The candidate segments that are identified during the pre-evaluation are queried
in subsequent logic to evaluate the WHERE clause. Eliminating the non-candidate segments
from the WHERE clause evaluation generally results in substantial performance gains.
Some queries can benefit by limiting the pre-evaluation phase.
SPD Server imposes the limit based on a ratio: the number of segments that contain candidates
compared to the total number of segments in the table. The reason for this is simple.
If the predicate has candidates in a high percentage of the segments, the pre-evaluation
work is largely wasted.
The ratio formed by dividing the number of segments that contain candidates by the
number of total segments is compared to a cutoff point. The segment ratio is greater
than the value assigned to the cutoff point. The extra processing required to perform
pre-evaluation outweighs any potential process savings that might be gained through
the predicate pre-evaluation. The server calculates the ratio for a given predicate
and compares the ratio to the SPDSIRAT value, which acts as the cutoff point. If the
calculated ratio is less than or equal to the SPDSIRAT value, pre-evaluation is performed.
If the calculated ratio is greater than the SPDSIRAT value, pre-evaluation is skipped,
and every segment is a candidate for the WHERE clause.
Use the global server
parameter MAXSEGRATIO to set the default cutoff value. The default
MAXSEGRATIO should provide good performance. Certain specific query
situations might justify modifying your SPDSIRAT value. When you
modify your SPDSIRAT value, the new value overrides the default value
established by MAXSEGRATIO.