Operators for Phased Execution

In this section, we describe the principles of phased execution. We consider some im- portant algebraic and physical-level aspects of the traditional relational operators, and see how they are adapted to the context of phased execution. Naturally, we start with joins, since they set the stage for the rest.

6.2.1 Join

Consider an equijoin-only query plan J = R1 1 R2 1 . . . 1 Rm, over the relations

R₁, . . . , Rm. Suppose there are n phases before the cleanup phase, and hence each

Rj,1 ≤ j ≤ m, has been partitioned into n subsets, one per phase: Rj = Rj1∪ R2j. . .∪ Rjn.

Using the distributive property of unions over joins, we can write the join as follows. R1 1 . . . 1 Rn=

[

1≤c1≤n,...,1≤cm≤n

(Rc1

1 1 . . . 1 Rcmm)

We can now rearrange the terms in the union into two sets. The first set is the union of the results from the first n phases: ∪1≤i≤n(Ri1 1 . . . 1 Rmi ), and the second set

{t|t ∈ (Rc1

1 1 . . . 1 Rcmm), 1 ≤ ci≤ n, ¬(c1= . . . = cm)}

Since the cleanup plan must join the subtables in all possible cross-phase combina- tions, one might conclude that the system needs to re-scan all source relations in order to perform cleanup.

However, if we require that every join operation buffers each of its inputs to perform the join, then the same effect can be obtained by taking the buffered inputs from each of the leaf-level join operators, and joining across those. Note that in terms of query operator algorithms, the pipelined hash family of joins [WA91] has precisely this prop- erty — and also has the benefit that the “build” portion of the hash join operation is already complete. For other join implementations, such as the nested loops join or the hybrid hash join, the “inner” or “build” relation is typically stored in memory, and the “outer” or “probe” relation is typically not. In this case, the external data source must be re-scanned or buffered and reused.

As mentioned previously, additional query processing performance can be gained by observing that certain subexpressions within the cleanup phase (e.g., joins between pairs of tables from the same phase) may have already been executed in previous phases. These intermediate join results can be reused within the cleanup process, reducing the amount of work that must be performed. In our example, there is no need to re-compute tuples of R2_T2_{. (Note, however, that if phase plans differ substantially}

in order of execution, few intermediate results may be shared between the plans.) 6.2.2 Selection and Projection

Algebraically, the addition of selection and projection requires only a small modifica- tion to the join-splitting strategy described in Section 6.2.1. Selection and projection operators can initially be applied over the union of the different phases’ join trees. Us- ing the distributive laws, they can be pushed over the unions and joins — typically in query optimization, they will be pushed to the point of earliest evaluation.

At the physical level, however, predicate push-down requires some care in the cleanup stage. The join process during cleanup may require some combination of hash, nested loops, and double pipelined hash joins — as is required to join the data struc- tures from prior phases. In some of these cases, the selection predicates may be evalu- ated within the join itself; in others, a separate selection operation must take place on

the join results. Hence, some predicates that can be pushed into a join operator may also be evaluated immediately afterwards in a separate selection operator.

Fortunately, projection generally causes fewer problems in terms of phased execu- tion: conventional projection-push-down strategies ensure that identical subqueries within different phases will have the same projected attributes, even if different sub- plans are used. Thus no special precautions need to be taken to maintain homogeneity of intermediate results for re-use in the cleanup stage.

6.2.3 Grouping and Aggregation

Algebraically, grouping and aggregation are slightly more complicated, because one needs to be careful how to combine aggregate values from the different phases. In the cases ofMINandMAX, one can combine the minimum or maximum values computed in

the different phases by simply anotherMIN/MAXoperation. Likewise, for SUMone can

add the sums of the different phases to obtain the correct result. TheCOUNToperation

requires slightly different logic: the counts of each group from the individual phases need to be summed to produce the desired result. Finally, in order to handleAVERAGE

one must compute for each phase both the sum and count of each group, and then average the results of the different phases at the end. (These techniques are the same as those suggested by [CS94].) A few additional adjustments are necessary in order to combine results that have pushed-down grouping and those that are not grouped, but we defer our discussion of these until Section 6.3, when we describe techniques for optimizing GROUP BY operations.

6.2.4 Other Operators

The phased execution model can also be extended to support other operators, such as union and outer join. Union, of course, distributes over join and other unions following the standard rules of the relational algebra — hence disjunctive queries can be decom- posed into phases. The nestChild operator of Tukwila, which is similar to a left outer join operation, is commonly used in XQueries. It can be implemented in the follow- ing way: for all phases other than the cleanup phase, the nestChild is replaced with a standard join (which will not output any parents that do not have children, and which may not maintain contiguous order between sibling child elements). All results are fed into a grouping operator that clusters child elements in contiguous order under their

parents. Finally, during the cleanup phase, any parents that appear without children are also output.

6.2.5 Ordered Results

Up to this point, I have presented the phased execution model without mentioning ordered execution. In general, dependence on the preservation of “interesting orders” throughout plan execution, as is typically done for sorted inputs in a relational system, can be done within the phased execution model, but care must be taken to ensure that phases are broken along “natural boundaries” across the data. Otherwise, there may not be a total ordering on the data returned by the query. If the query needs to return data following a particular order, it needs to sort the final output4.