Predicates and Filtering


The first part of this chapter discussed how to code predicates by using the WHERE, HAVING, and ON clauses. Predicates describe the attributes of the data. Usually based on the columns of a table, predicates either qualify rows, through an index, or reject rows, returned by a scan, when the table is accessed. The resulting qualified or rejected rows are independent of the access path chosen for that table.

Various classifications of predicates have been discussed previously in this chapter:

  • Compound. A compound predicate is the result of two predicates, whether simple or compound, connected together by AND or OR Boolean operators.

  • Local. Local predicates reference only one table. They are local to the table and restrict the number of rows returned for that table.

  • Join. Join predicates involve more than one table or correlated reference. They determine the way rows are joined from two or more tables.

  • Boolean term. Any predicate that is not contained by a compound OR predicate structure is a Boolean term. If a Boolean term is evaluated FALSE for a particular row, the whole WHERE clause is false.

  • Simple. Predicates that are not compound, join, or Boolean term are called simple predicates.

Predicates are then further classified as indexable, stage 1, or stage 2. Indexable predicate types can match index entries. An example of an indexable predicate follows:

Assuming there is an index on LNAME, the following predicate is indexable:

 SELECT FNAME, LNAME FROM CANDIDATE WHERE LNAME = 'LAWSON'; 

The following predicate is not a matching predicate and is not indexable:

 SELECT FNAME, LNAME FROM CANDIDATE WHERE STATUS <> 'A'; 

To make your queries as efficient as possible, use indexable predicates in your queries, and create suitable indexes on your tables. Indexable predicates allow the possible use of a matching index scan, which is often a very efficient access path.

Rows retrieved for a query go through two stages of processing.

  1. Stage 1 predicates, sometimes called sargable, can be applied at the first stage.

  2. Stage 2 predicates, sometimes called nonsargable, or residual, cannot be applied until the second stage.

Following are some general rules about predicate evaluation.

  • In terms of resource use, the earlier a predicate is evaluated, the better.

  • Because they disqualify rows earlier and reduce the amount of processing needed at stage 2, stage 1 predicates are better than stage 2 predicates.

  • When possible, try to write queries that evaluate the most restrictive predicates first. When predicates with a high filter factor are processed first, unnecessary rows are screened as early as possible, which can reduce processing cost at a later stage. However, a predicate's restrictiveness is effective only among predicates of the same type and the same evaluation stage.

When DB2 evaluates the predicates, two sets of rules are used to determine the order. The first set is as follows:

  • Indexable predicates are applied first. All matching predicates on index-key columns are applied first and evaluated when the index is accessed. Next, stage 1 predicates that have not been picked as matching predicates but refer to index columns are applied to the index. This is called index screening.

  • Other stage 1 predicates are applied next. After data page access, stage 1 predicates are applied to the data.

  • Finally, the stage 2 predicates are applied on the returned data rows.

The second set of rules describes the order of predicate evaluation within each of the stages.

  • All equal predicatesincluding the column IN list, where the list has only one element, or COL BETWEEN value1 AND value1are evaluated.

  • All range predicates and predicates of the form COL IS NOT NULL are evaluated.

  • All other predicate types are evaluated. After both sets of rules are applied, predicates are evaluated in the order in which they appear in the query. Because you specify that order, you have some control over the order of evaluation. One exception here is the fact that, regardless of coding order, noncorrelated subqueries are evaluated before correlated subqueries, unless DB2 transforms the subquery into a join.

Table 6-1 shows whether predicates are indexable and whether they are processing at stage 1.

Table 6-1. Indexable and Stage 1 Predicates

Predicate Type

Indexable

Stage 1

COL = value

Y

Y

COL = noncol expr

Y

Y

COL IS NULL

Y

Y

COL op value

Y

Y

COL op noncol expr

Y

Y

COL BETWEEN value1 AND value2

Y

Y

COL BETWEEN noncol expr1 AND noncol expr2

Y

Y

value BETWEEN COL1 AND COL2

N

N

COL BETWEEN COL1 AND COL2

N

N

COL BETWEEN expression1 AND expression2

Y

Y

COL LIKE 'pattern'

Y

Y

COL IN (list)

Y

Y

COL <> value

N

Y

COL <> noncol expr

N

Y

COL IS NOT NULL

Y

Y

COL NOT BETWEEN value1 AND value2

N

Y

COL NOT BETWEEN noncol expr1 AND noncol expr2

N

Y

value NOT BETWEEN COL1 AND COL2

N

N

COL NOT IN (list)

N

Y

COL NOT LIKE ' char'

N

Y

COL LIKE '%char'

N

Y

COL LIKE '_char'

N

Y

COL LIKE host variable

Y

Y

T1.COL = T2 col expr

Y

Y

T1.COL op T2 col expr

Y

Y

T1.COL <> T2 col expr

N

Y

T1.COL1 = T1.COL2

N

N

T1.COL1 op T1.COL2

N

N

T1.COL1 <> T1.COL2

N

N

COL=(noncor subq)

Y

Y

COL = ANY (noncor subq)

N

N

COL = ALL (noncor subq)

N

N

COL op (noncor subq)

Y

Y

COL op ANY (noncor subq)

Y

Y

COL op ALL (noncor subq)

Y

Y

COL <> (noncor subq)

N

Y

COL <> ANY (noncor subq)

N

N

COL <> ALL (noncor subq)

N

N

COL IN (noncor subq)

Y

Y

(COL1,...COLn) IN (noncor subq)

Y

Y

COL NOT IN (noncor subq)

N

N

(COL1,...COLn) NOT IN (noncor subq)

N

N

COL = (cor subq)

N

N

COL = ANY (cor subq)

N

N

COL = ALL (cor subq)

N

N

COL op (cor subq)

N

N

COL op ANY (cor subq)

N

N

COL op ALL (cor subq)

N

N

COL <> (cor subq)

N

N

COL <> ANY (cor subq)

N

N

COL <> ALL (cor subq)

N

N

COL IN (cor subq)

N

N

(COL1,...COLn) IN (cor subq)

N

N

COL NOT IN (cor subq)

N

N

(COL1,...COLn) NOT IN (cor subq)

N

N

COL IS DISTINCT FROM value

N

Y

COL IS NOT DISTINCT FROM value

Y

Y

COL IS DISTINCT FROM noncol expr

N

Y

COL IS NOT DISTINCT FROM noncol expr

Y

Y

T1.COL1 IS DISTINCT FROM T2.COL2

N

N

T1.COL1 IS NOT DISTINCT FROM T2.COL2

N

N

T1.COL1 IS DISTINCT FROM T2 col expr

N

Y

T1.COL1 IS NOT DISTINCT FROM T2 col expr

Y

Y

COL IS DISTINCT FROM (noncor subq)

N

Y

COL IS NOT DISTINCT FROM (noncor subq)

Y

Y

COL IS DISTINCT FROM ANY (noncor subq)

N

N

COL IS NOT DISTINCT FROM ANY (noncor subq)

N

N

COL IS DISTINCT FROM ALL (noncor subq)

N

N

COL IS NOT DISTINCT FROM ALL (noncor subq)

N

N

COL IS NOT DISTINCT FROM (cor subq)

N

N

COL IS DISTINCT FROM ANY (cor subq)

N

N

COL IS DISTINCT FROM ANY (cor subq)

N

N

COL IS NOT DISTINCT FROM ANY (cor subq)

N

N

COL IS DISTINCT FROM ALL (cor subq)

N

N

COL IS NOT DISTINCT FROM ALL (cor subq)

N

N

EXISTS (subq)

N

N

NOT EXISTS (subq)

N

N

expression = value

N

N

expression <> value

N

N

expression op value

N

N

expression op (subq)

N

N




DB2 for z. OS Version 8 DBA Certification Guide
DB2 for z/OS Version 8 DBA Certification Guide
ISBN: 0131491202
EAN: 2147483647
Year: 2003
Pages: 175
Authors: Susan Lawson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net