Query Optimization and Data Localization in Distributed Database Management Systems

1. Distributed Database Management Systems Lecture 33

2. In the previous lecture • Final phase of QD • Data Localization: for HF, VF and DF.

3. In today’s Lecture • Data Localization for Hybrid Fragmentation • Query Optimization.

4. Reduction for HyF • HyF contains both types of Fragmentations • EMP1=eNo ≤ E4 (eNo, eName (EMP)) • EMP2=eNo > E4 (eNo, eName (EMP)) • EMP3=eNo, title (EMP).

5. eName eName eNo = E5 eNo = E5 ⋈eNo U EMP2 Reduced Query- EMP3 EMP1 EMP2 • Select eName from EMP where eNo = E5

6. Summary of what we have done so far

7. Query Decomposition: generates an efficient query in relational algebra • Normalization, Analysis, Simplification, Rewriting • Data Localization: applies global query to fragments; increases optimization level-

8. So, next is the cost-based optimization • Mainly concentrates on the order of performing joins • Characteristics of relations like cardinalities are considered

9. First QO in general • QO refers to producing a Query Execution plan (QEP) that represents execution strategy.

10. Components of Optimizer • Search Space: set of eq. alternative exec plans • Cost Model: predicts cost of a execution plan • Search Strategy: produces best plan

11. Search Space • Search space consists of eq. Query Trees produced using Tr Rules • Optimizer concentrates on join trees, since join cost is the most effective

12. Example: • Select eName, resp From EMP, ASG, PROJ where EMP.eNo = ASG. eNo and ASG.pNo = PROJ.pNo.

13. ⋈pNo ⋈eNo ⋈eNo ⋈pNo PROJ EMP EMP ASG PROJ ASG ⋈pNo, eNo x ASG EMP PROJ

14. Alternatives with N relations are O(N!) based on properties of relations • So, restrictions are applied

15. 1- Heuristics - Selection and projection on base relations - Avoid Cartesian product

16. 2- Shape of Tree • Linear Tree: At least one node for each operand is a base relation • Bushy tree: May have operators with interm tables only; allows parallel execution

17. Search Strategy • Most popular is Dynamic Programming • That starts with base relations and keeps on adding relations calculating cost

18. DP is almost exhaustive so produces best plan • Too expensive with more than 5 relations • Other option is Randomized strategy • Do not guarantee best

19. Cost Model • Cost of operators, statistics of base data to predict size of intermediate tables • Cost considered as Total Time and Response Time.

20. Total time = CPU time + I/O time + tr time • In WAN, major cost is tr time • Initially ratios were 20:1 for tr and I/O, for LAN it is 1:1.6

21. Response time = CPU time + I/O time + tr time • Difference.?

22. TCPU = time for a CPU inst • TI/O = a disk I/O • TMSG = fixed time for initiating and recv a msg • TTR = transmit a data unit from one site to another.

23. X units Site 1 Site 3 Site 2 Y units • TT = 2TMSG + TTR*(x+y) • RT = max{TMSG + TTR*X, TMSG + TTR*Y}

24. Database Statistics • Major factor is interm tabs • If the interm results are to be transmitted, then estimation about size is a must • More precise statistics cost more

25. For each relation R[A1, A2, …, An] fragmented as R1, …, Rr • length of each attribute: length(Ai) • the number of distinct values for each attribute in each fragment: card(Ai(Rj)) • maximum and minimum values in the domain of each attribute: min(Ai), max(Ai).

26. 4.The cardinalities of each domain: card(dom[Ai]) and the cardinalities of each fragment: card(Rj) 5.Join selectivity factor for some of the relationsSFJ (R,S) = card(R ⋈ S)/ (card(R) ∗ card(S))-

27. Cardinalities of Intermediate Results

28. Selection Operation • Card(F(R))=SFS(F) * card(R) • SFS(A = value) = 1/card(A(R)) • SFS(A > value) = max(A) – value /(max(A) – min(A)) • SFS(A < value) = value - min(A) /(max(A) – min(A))

29. SFS(A < value) = max(A) – value /(max(A) – min(A)) • SFS(p(Ai) ^ p(Aj)) = SFS(p(Ai)) * (SFSp(Aj)) • SFS(p(Ai) v p(Aj)) = SFS(p(Ai)) + SFS(p(Aj))–(SFS(p(Ai))* SFS(p(Ai))).

30. Cardinality of Projection • Hard to determine precisely • Two cases when it is trivial 1- When a single attribute A, card(A(R)) = card (A) 2- When PK is included card(A(R)) = card (R)

31. Cartesian Product • card(RxS) = card (R) * card(S). • Cardinality of Join • No general way to test without additional information • In case of PK/FK combination Card(R ⋈ S) = card (S)

32. Semi Join: SFSJ(R ⋉AS)= card(A(S))/ card(dom[A]) card(R ⋉AS) = SFSJ(S.A) * card(R).

33. Union: Hard to estimate • Limits possible which are card(R) + card(S) and max{card (R) + card (S)) • Difference: Like Union, card (R) for (R-S), and 0

34. Centralized Query Optimization

35. Why to Study • Distributed Query is transformed into local ones • Issues are related and more complex in DD • Easier to understand • Two famous ones.

36. INGRES • Dynamic • Recursively breaks into smaller ones • System R • static • exhaustive search