WHERE Clause Examples

Data for WHERE Examples

The WHERE clause examples below assume that the user is connected to the SPD Server LIBNAME foo and has executed the following SAS code:

data foo.a;
do i=1 to 100;
  do j=1 to 100;
    do k=1 to 100;
      m=mod(i,3);
      output;
    end;
  end;
end;
run;

proc datasets lib=foo;
modify a;
index create ijk = (i j k);
index create j;
index create m;
quit;

Example 1 "where i = 1 and j = 2 and m = 4"

whinit: WHERE ((I=1) and (J=2) and (M=4))
whinit: wh-tree presented

      /-NAME = [I]
           /-CEQ----|
          |
    \-LITN = [1]
 --LAND---|
          |
    /-NAME = [J]
          |--CEQ----|
          |
    \-LITN = [2]
          |
    /-NAME = [M]
           \-CEQ----|

      \-LITN = [4]
whinit: wh-tree after split
 --[empty]
whinit: pruning INDEX node which is trivially FALSE
           /-NAME = [M] INDEX M (M)
 --CEQ----|
           \-LITN = [4]
whinit: INDEX tree evaluated to FALSE
whinit returns: FALSE

Here the only values that column M can contain are 0, 1, or 2. Thus, the predicate m = 4 is identified as trivially FALSE. Because this predicate is part of an AND predicate, it too is FALSE. Consequently, the entire WHERE clause is pre-evaluated to FALSE, meaning that no records can satisfy this WHERE clause. Thus, as a result of the pre-evaluation, no records are actually read from disk. This is an example of optimization at its best.

WHERE_EXAMPLE 2: where i in (1, 2, 3) and j in (4, 5, 6, 7) and k > 8 and m = 2

  whinit: WHERE (I in (1, 2, 3) and J in (4, 5, 6, 7) and (K>8) and (M=2))
whinit: wh-tree presented

      /-NAME = [I]
           /-IN-----|
          |
    |          /-LITN = [1]
          |
    \-SET----|
          |
             |--LITN = [2]
          |
              \-LITN = [3]
 --LAND---|
          |
    /-NAME = [J]
          |--IN-----|
          |
   |          /-LITN = [4]
          |
    \-SET----|
          |
             |--LITN = [5]
          |
             |--LITN = [6]
          |
              \-LITN = [7]
          |
    /-NAME = [K]
          |--CGT----|
          |
    \-LITN = [8]
          |
    /-NAME = [M]
           \-CEQ----|

      \-LITN = [2]
whinit: SBM-INDEX M uses 60% of segs(WITHIN maxsegratio 100%)
whinit: wh-tree after split
           /-NAME = [K]
 --CGT----|
           \-LITN = [8]
whinit: INDEX tree after split

      /-NAME = [I] <1>SBM-INDEX IJK (I,J)
           /-IN-----|
          |
   |          /-LITN = [1]
          |
    \-SET----|
          |
             |--LITN = [2]
          |
              \-LITN = [3]
 --LAND---|
          |
    /-NAME = [J]
          |--IN-----|
          |
   |          /-LITN = [4]
          |
    \-SET----|
          |
             |--LITN = [5]
          |
             |--LITN = [6]
          |
              \-LITN = [7]
          |
    /-NAME = [M] <2>SBM-INDEX M (M)
           \-CEQ----|

      \-LITN = [2]
whinit returns: ALL EVAL1(w/SEGLIST) EVAL2

Here, a composite index ijk was defined on columns (i j k). This composite index is used for column's i and j, which is an equality index predicate. Column k is not included because it involves an inequality operator (greater than). Since there are no other indexes for column k, this predicate is assigned to EVAL2 . EVAL2 will post-filter the records obtained through the use of indexes.

WHERE_EXAMPLE 3: where i = 1 and j > 5 and mod(k, 3) = 2

  whinit: WHERE ((I=1) and (J>5) and (MOD(K, 3)=2))
whinit: wh-tree presented

      /-NAME = [I]
           /-CEQ----|
          |
    \-LITN = [1]
 --LAND---|
          |
    /-NAME = [J]
          |--CGT----|
          |
    \-LITN = [5]
          |
              /-FUNC = [MOD()]
          |
    /-FLST---|
          |
   |         |--NAME = [K]
          |
   |          \-LITN = [3]
           \-CEQ----|

      \-LITN = [2]
whinit: wh-tree after split

      /-FUNC = [MOD()]
           /-FLST---|
          |
   |--NAME = [K]
          |
    \-LITN = [3]
 --CEQ----|
           \-LITN = [2]
whinit: SBM-INDEX IJK uses 1% of sges(WITHIN maxsegratio 75%)
whinit: SBM-INDEX J uses at least 76% of segs(EXCEEDS maxsegratio 75%)
whinit: INDEX tree after split

      /-NAME = [I] <1>SBM-INDEX IJK (I)
           /-CEQ----|
          |
    \-LITN = [1]
 --LAND---|
          |
    /-NAME = [J] <2>SBM-INDEX J (J)
           \-CGT----|

      \-LITN = [5]
whinit returns: ALL EVAL1(w/SEGLIST) EVAL2

Here the indexes on column i, a composite index on the columns (i j k), and the column j are combined. In this example WHINIT uses both EVAL1 and EVAL2. The j predicate involves an inequality operator (greater than). Therefore, WHINIT cannot combine the predicate with i and the composite index involving i and j (and k).

Using the composite index ijk in this plan might be inefficient. If a smaller composite index (that is, one on i j or a simple index on i) were available, WHINIT would select it. In lieu of this, try benchmarking the plan. Suppress the composite index and compare the results to the existing plan to see which is more efficient (faster) on your machine.

The example that follows shows what WHINIT's plan would look like with the composite index suppressed.

WHERE_Example 4: where i = 1 and j > 5 and mod(k, 3) = 2

In this example, the index IJK is suppressed.

  whinit: WHERE ((I=1) and (J>5) and (MOD(K, 3)=2))
whinit: wh-tree presented

       /-NAME = [I]
           /-CEQ----|
          |
     \-LITN = [1]
 --LAND---|
          |
     /-NAME = [J]
          |--CGT----|
          |
     \-LITN = [5]
          |
               /-FUNC = [MOD()]
          |
     /-FLST---|
          |
    |         |--NAME = [K]
          |
    |          \-LITN = [3]
           \-CEQ----|

      \-LITN = [2]
whinit: wh-tree after split

      /-NAME = [I]
           /-CEQ----|
          |
    \-LITN = [1]
 --LAND---|
          |
              /-FUNC = [MOD()]
          |
    /-FLST---|
          |
   |         |--NAME = [K]
          |
   |          \-LITN = [3]
           \-CEQ----|

      \-LITN = [2]
whinit: SBM_INDEX J uses at least 76% of segs (EXCEEDS maxsegratio 75%)
whinit: checking all hybrid segments
whinit: INDEX tree after split
           /-NAME = [J] <1>SBM-INDEX J (J)
 --CGT----|
           \-LITN = [5]
whinit returns: ALL EVAL1 EVAL2

Notice that the predicate involving column i is non-indexed. WHINIT evaluates it using EVAL2. Because the predicate j > 5 still uses an inequality comparison, WHINIT continues to use EVAL1. Finally, because the percentage of segments that contain values for column J exceeds the maximum segment ratio, pre-segment logic is not done on column J. As a result, all segments of the table are queried for values that satisfy the WHERE clause for column J.