SAS users can use an
SPD Server macro variable to view WHERE clause planner output:
%let SPDSWDEB=YES;
The following is what
the WHINIT plan might give for the following scenario:
-
a WHERE clause of
where
a = 1 and b in (1 2 3) and d = 3 and (d + 3 = c)
-
an SPD index IDX_ABC on columns
(A B C)
-
an SPD index D on column (D)
Note: The line numbers are for
reference; they are NOT part of the actual output.
1:whinit: WHERE ((A=1) and B in (1, 2, 3) and (D=3) and (C=(D+3)))
2:whinit: wh-tree presented
3:
/-NAME = [A]
4: /-CEQ----|
5: |
\-LITN = [1]
6: --LAND---|
7: |
/-NAME = [B]
8: |--IN-----|
9: |
| /-LITN = [1]
10: |
\-SET----|
11: |
|--LITN = [2]
12: |
\-LITN = [3]
13: |
/-NAME = [D]
14: |--CEQ----|
15: |
\-LITN = [3]
16: |
/-NAME = [C]
17: \-CEQ----|
18:
| /-NAME = [D]
19:
\-AADD---|
20:
\-LITN = [3]
21:whinit: wh-tree after split
22: /-NAME = [C]
23: --CEQ----|
24: |
/-NAME = [D]
25: \-AADD---|
26:
\-LITN = [3]
27:whinit: SBM-INDEX D uses 50% of segs (WITHIN maxsegratio 75%)
28:whinit: INDEX tree after split
29:
/-NAME = [A] <1>SBM-INDEX IDX_ABC (A,B)
30: /-CEQ----|
31: |
\-LITN = [1]
32: --LAND---|
33: |
/-NAME = [B]
34: |--IN-----|
35: |
| /-LITN = [1]
36: |
\-SET----|
37: |
|--LITN = [2]
38: |
\-LITN = [3]
39: |
/-NAME = [D] <2>SBM-INDEX D (D)
40: \-CEQ----|
41:
\-LITN = [3]
42:whinit returns: ALL EVAL1(w/SEGLIST) EVAL2
Line 1 shows what the
WHINIT Planner received. Do not be surprised -- what the Planner receives
can differ from your entries. Sometimes SAS optimizes or transforms
a WHERE clause before passing it to SPD Server. For example, it can
eliminate entities such as NOT operators, the union of set lists,
and so on.
Lines 2 to 20 show the
presented WHERE clause in a tree format. The tree format is a user-readable
form of the actual WHERE clause that is processed by the SPD Server
engine.
Lines 21 to 26 show
the non-indexed WHERE tree, the result of splitting off the indexed
part. The non-indexed WHERE tree can be empty or it can look the same
as lines 2 to 20 if no indexes are selected. Consider that it is the
non-indexed part of the WHERE clause that WHINIT uses to filter records
obtained by the indexed strategies (EVAL1, 3 or 4).
Lines 27 to 41 shows
that the percentage of segments containing values selected from column
D is with the maximum allowed to proceed with pre-segment logic.
Therefore, only those segments that contain values that satisfy the
WHERE clause for column D will be included in further query processing
for that column. Composite index IDX_ABC and simple index D are used
to resolve the indexed WHERE clause predicates.
Line 42, the last line
in our output, shows which strategies are used. The first keyword
ALL indicates that SPD Server can identify correctly ALL resulting
records, without help from the SAS System. First, SPD Server will
call EVAL1, an indexed method, to quickly access a list of records
that satisfy
where a = 1 and b in (1 2 3) and d = 3
, then it will use EVAL2 to determine whether
c = d +
3
is true on these records.
When output from EVAL1
displays the suffix w/ seglist, as it does in the above output, it
means that SPD indexes were detected, and that the indexes were used
to filter only the segments that satisfy the indexed predicates. When
EVAL1 has no suffix, it means that ALL segments will be evaluated.
SPD Server stores the
minimum and maximum values for a table index in a global structure.
WHINIT can use the numeric range to 'prune' predicates when the table
index values are out of the min / max range. WHINIT output keywords
can indicate pruning activity. For example, if WHINIT had determined
that the values for D (in our WHERE clause) are between 5 and 13,
then as a consequence, the predicate
where d = 3
could never be true. In this case, WHINIT would have pruned this
predicate since it is logically impossible, or FALSE. Pruning can
also affect higher nodes. If the
d = 3 predicate
were deemed FALSE, then the AND sub tree would also be FALSE and would
also have been pruned.