Method and system for using materialized views to evaluate queries involving ...

Fig. 3

302 Condition Ci:

There is a 1-1 column mapping <|) from V to Q

304

Condition C2:

If a column A in ColSel(Q) uGroups(Q) is a column in <p(Cols(V)), then Sel(V) must have a column BA such that Conds(Q) implies (A = (j>(ba)).

Note that this condition is satisfied if BA is (^(A).

306

Condition C3: Suppose AGG(A) is in Sel(Q). If column A is in <|>(Cols(V)), then

1. If AGG is MIN, MAX or SUM, then Sel(V) must have a column BA such that Conds(Q) implies (A = (ba)).

2. If AGG is COUNT, then Sel(V) must not be empty.

308

Condition C4:

There exists a Boolean combination of built-in predicates, Conds', such that:

1. Conds(Q) is equivalent to <|)(Cols(V)) & Conds'.

2. Conds' involves only the columns in <KSel(V)) u (Cols(Q) - <|>(Cols(V))).

Fig. 4

400

^^/ConjViewSingleBlock

402 Step Si

Replace all the tables in <j)(Tables(V)) by <j>(V).

404 Step S2:

Replace each column A in Groups(Q) uColSel(Q) u AggSel(Q) by <kba), where BA satisfies conditions C2 and

C3, part 1.

406 Step S3:

Determine a Boolean combination of built-in predicates Conds' satisfying condition C4. Replace Conds(Q) in Q by

Conds'.

408 Step S4:

Replace COUNT(A) by COUNT(B), where B is any

column in (|)(V).

Fig. 5

500

Condition CTM:

Let Condsi be Conds(Q) & §(Conds(V)), and Conds2 be Conds(Q) &-4(Conds(V)). Then,

1. Condsi must be satisfiable.

2. There exists a Boolean combination of built-in predicates, Conds' such that:

(a) Conds 1 is equivalent to (Conds(V)) & Conds',

(b) Conds' involves only the columns in

<KSel(V)) u (Cols(Q) - (j)(Cols(V))),

(c) Group( Q)(CondSi) &

GrouP(Q)(Conds2) is FALSE.