Distributed Query Processing. Agenda. Recap of query optimization Transformation rules for P&D systems Memoization Query evaluation strategies Eddies. Introduction. Alternative ways of evaluating a given query Equivalent expressions Different algorithms for each operation (Chapter 13)
Relations generated by two equivalent expressions have the same set of attributes and contain the same set of tuples, although their attributes may be ordered differently.
1. Conjunctive selection operations can be deconstructed into a sequence of individual selections.
2. Selection operations are commutative.
3. Only the last in a sequence of projection operations is needed, the others can be omitted.
5. Theta-join operations (and natural joins) are commutative.E1 E2 = E2 E1
6. (a) Natural join operations are associative:
(E1 E2) E3 = E1 (E2 E3)(b) Theta joins are associative in the following manner:(E1 1 E2) 2 3E3 = E1 2 3 (E22 E3) where 2involves attributes from only E2 and E3.
7. The selection operation distributes over the theta join operation under the following two conditions:(a) When all the attributes in 0 involve only the attributes of one of the expressions (E1) being joined.0E1 E2) = (0(E1)) E2
(b) When 1 involves only the attributes of E1 and2 involves only the attributes of E2.
1 E1 E2) = (1(E1)) ( (E2))
8. The projections operation distributes over the theta join operation as follows:
(a) if L involves only attributes from L1 L2:
(b) Consider a join E1 E2.
(E1 E2) E3 = E1 (E2 E3)(E1 E2) E3 = E1 (E2 E3)
12. The projection operation distributes over union
L(E1 E2) = (L(E1)) (L(E2))
Level n plans
Level 2 plans
Level 1 plans
In a distributed system, other issues must be taken into account:
The cost of a data transmission over the network.
The potential gain in performance from having several sites process parts of the query in parallel.Distributed Query Processing
L(E1 E2) = (L(E1)) (L(E2))
Vertical fragmented tables:
account is stored at site S1
depositor at S2
branch at S3
For a query issued at site SI, the system needs to produce the result at site SISimple Distributed Join Processing
Ship a copy of the account relation to site S2 and compute temp1 = account depositor at S2. Ship temp1 from S2 to S3, and compute temp2 = temp1 branch at S3. Ship the result temp2 to SI.
Devise similar strategies, exchanging the roles S1, S2, S3
Must consider following factors:
amount of data being shipped
cost of transmitting a data block between sites
relative processing speed at each sitePossible Query Processing Strategies
Let r2 be a relation with schema R2 stores at site S2
Evaluate the expression r1 r2 and obtain the result at S1.
1. Compute temp1 R1 R2 (r1)at S1.
2. Ship temp1 from S1 to S2.
3. Compute temp2 r2 temp1 at S2
4. Ship temp2 from S2 to S1.
5. Compute r1temp2 at S1. This is the same as r1r2.Semijoin Strategy
r1 is shipped to S2 and r1r2 is computed at S2: simultaneously r3 is shipped to S4 and r3r4 is computed at S4
S2 ships tuples of (r1 r2) to S1 as they produced; S4 ships tuples of (r3r4) to S1
Once tuples of (r1r2) and (r3r4) arrive at S1 (r1r2) (r3r4) is computed in parallel with the computation of (r1 r2) at S2 and the computation of (r3r4) at S4.Join Strategies that Exploit Parallelism
R. Avnur, J.M. Hellerstein
ACM Sigmod 2000
Eddies are tuple routers that distribute arriving tuples to interested operators