5 Distributed Query Processing Chapter 7 Overview of

5. Distributed Query Processing Chapter 7 Overview of Query Processing Chapter 8 Query Decomposition and Data Localization

Outline v Overview of Query Processing (查询处理) v Query Decomposition and Localization (查询分解 与定位) 2

Query Processing High level user query Query Processor Low level data manipulation commands 3

Query Processing Components v Query language that is used w SQL (Structured Query Language) v Query execution methodology w The steps that the system goes through in executing high-level (declarative) user queries v Query optimization w How to determine the “best” execution plan? 4

Query Language v Tuple calculus: { t | F(t) } where t is a tuple variable, and F(t) is a well formed formula v Example: w Get the numbers and names of all managers. 5

Query Language (cont. ) v Domain calculus: where xi is a domain variable, and formed formula is a well v Example: { x, y | EMP(x, y, “Manager") } Variables are position sensitive! 6

Query Language (cont. ) v SQL is a tuple calculus language. SELECT ENO, ENAME FROM EMP WHERE TITLE=“Programmer” End user uses non-procedural (declarative) languages to express queries. 7

Query Processing Objectives & Problems v Query processor transforms queries into procedural operations to access data in an optimal way. v Distributed query processor has to deal with query decomposition and data localization. 8

Centralized Query Processing Alternatives SELECT ENAME FROM EMP E, ASG G WHERE E. ENO = G. ENO AND TITLE=“manager” v Strategy 1: v Strategy 2: Strategy 2 avoids Cartesian product, so is “better”. 9

Distributed Query Processing v Query processor must consider the communication cost and select the best site. v The same query example, but relation G and E are fragmented and distributed. 10

Distributed Query Processing Plans v By centralized optimization, v Two distributed query processing plans 1111

Distributed Query Plan I: To transport all segments to query site and execute there. Site 5 Result = (EMP 1 EMP 2) ASG 1 Site 1 ASG 2 Site 2 ⋈ ENO TITLE=“manager” EMP 1 Site 3 (ASG 1 ASG 2) EMP 2 Site 4 This causes too much network traffic, very costly. 12

Distributed Query Plan II (Optimized): Site 5 Result = (EMP 1 ’ EMP 2 ’) Site 3 EMP 1’ = EMP 1 ⋈ ENO ASG 1’ Site 4 EMP 2’ = EMP 2 ASG 1’ Site 1 ASG 1’ = TITLE=“manager” (ASG 1) EMP 2’ ⋈ ENO ASG 2’ Site 2 ASG 2’ = TITLE=“manager” (ASG 2) 13

Costs of the Two Plans v Assume: w size(EMP)=400, size(ASG)=1000, 20 tuples with TITLE=“manager” w tuple access cost = 1 unit; tuple transfer cost = 10 units w ASG and EMP are locally clustered on attribute TITLE and ENO, respectively. v Plan 1 w w v Transfer EMP to site 5: 400*tuple transfer cost Transfer ASG to site 5: 1000*tuple transfer cost Produce ASG’: 1000*tuple access cost Join EMP and ASG’: 400*20*tuple access cost Total cost 4000 1000 8000 23, 000 Plan 2 w w Produce ASG’: (10+10)*tuple access cost Transfer ASG’ to the sites of EMP: (10+10)*tuple transfer cost Produce EMP’: (10+10)*tuple access cost * 2 Transfer EMP’ to result site: (10+10)*tuple transfer cost Total cost 20 200 460 14

Query Optimization Objectives v Minimize a cost function I/O cost + CPU cost + communication cost w w These might have different weights in different distributed environments Can also maximize throughout 15

Communication Cost v Wide area network w Communication cost will dominate - Low bandwidth - Low speed - High protocol overhead w Most algorithms ignore all other cost components v Local area network w Communication cost not that dominate w Total cost function should be considered 16

Complexity of Relational Algebra Operations v Measured by cardinality n and tuples are sorted on comparison attributes Operation s , p (without duplicate elimination) p (with duplicate elimination), GROUP Complexity O(n) O(nlogn) Cartesian-Product X O(n 2) 17

Types of Query Optimization v Exhaustive search w Cost-based w Optimal w Combinatorial complexity in the number of relations w Workable for small solution spaces v Heuristics w Not optimal w Re-group common sub-expressions w Perform selection and projection ( ) first w Replace a join by a series of semijoins w Reorder operations to reduce intermediate relation size w Optimize individual operations 18

Query Optimization Granularity v Single query at a time w Cannot use common intermediate results v Multiple queries at a time w Efficient if many similar queries w Decision space is much larger 19

Query Optimization Timing v Static w Do it at compilation time by using statistics, appropriate for exhaustive search, optimized once, but executed many times. w Difficult to estimate the size of the intermediate results w Can amortize over many executions v Dynamic w Do it at execution time, accurate about the size of the intermediate results, repeated for every execution, expensive. 20

Query Optimization Timing (cont. ) v Hybrid w Compile using a static algorithm w If the error in estimate size > threshold, re-optimizing at run time 21

Statistics v Relation w Cardinality w Size of a tuple w Fraction of tuples participating in a join with another relation v Attributes w Cardinality of the domain w Actual number of distinct values v Common assumptions w Independence between different attribute values w Uniform distribution of attribute values within their domain 22

Decision Sites v For query optimization, it may be done by w Single site – centralized approach – Single site determines the best schedule – Simple – Need knowledge about the entire distributed database w All the sites involved – distributed approach – Cooperation among sites to determine the schedule – Need only local information – Cost of operation w Hybrid – one site makes major decision in cooperation with other sites making local decisions – One site determines the global schedule – Each site optimizes the local subqueries 23

Network Topology v Wide Area Network (WAN) – point-to-point w Characteristics – Low bandwidth – Low speed – High protocol overhead w Communication cost will dominate; ignore all other cost factors w Global schedule to minimize communication cost w Local schedules according to centralized query optimization 24

Network Topology (cont. ) v Local Area Network (LAN) w communication cost not that dominate w Total cost function should be considered w Broadcasting can be exploited w Special algorithms exist for star networks 25

Other Information to Exploit v Using replications to minimize communication costs v Using semijoins to reduce the size of operand relations to cut down communication costs when overhead is not significant. 26

Layers of Query Processing Calculus Query on Distributed Relations QUERY DECOMPOSITION CONTROL SITE GLOBAL SCHEMA Algebra Query on Distributed Relations DATA LOCALIZATION FRAGMENT SCHEMA Fragment Query GLOBAL OPTIMIZATION LOCAL SITE Optimized Fragment Query With Communication Operations LOCAL OPTIMIZATION Optimized Local Queries STATISTICS ON FRAGMENTS LOCAL SCHEMA 27

Step 1 - Query Decomposition v Decompose calculus query into algebra query using global conceptual schema information. (1) normalization (2) analysis (3) elimination of redundancy (4) rewriting 28

Step 1 - Query Decomposition (cont. ) 1) Normalization w The calculus query is written in a normalized form (CNF or DNF) for subsequent manipulation 2) Analysis w To reject normalized queries for which further processing is either impossible or unnecessary (type incorrect or semantically incorrect) 3) Simplification w Redundant predicates are eliminated to obtain simplified queries 4) Restructuring w The calculus query is translated to optimal algebraic query representation 29 w More than one translation is possible

1) Normalization v Lexical and syntactic analysis w check validity (similar to compilers) w check for attributes and relations w type checking on the qualification v There are two possible forms of representing the predicates in query qualification: w Conjunctive Normal Form (CNF) or Disjunctive Normal Form (DNF) – CNF: (p 11 p 12 . . . p 1 n) . . . (pm 1 pm 2 . . . pmn) – DNF: (p 11 p 12 . . . p 1 n) . . . (pm 1 pm 2 . . . pmn) – OR's mapped into union – AND's mapped into join or selection 30

1) Normalization (cont. ) v The transformation of the quantifier-free predicate is straightforward using the well-known equivalence rules for 1. logical operations ( ): 2. 3. 4. 5. 6. 7. 8. 9. 10. 31

1) Normalization (cont. ) v Example SELECT ENAME FROM EMP, ASG WHERE EMP. ENO=ASG. ENO AND ASG. JNO=”J 1” AND (DUR=12 OR DUR=24) v The conjunctive normal form: 32

2) Analysis v Objective w reject type incorrect or semantically incorrect queries. v Type incorrect w if any of its attribute or relation names is not defined in the global schema; w if operations are applied to attributes of the wrong type 33

2) Analysis (cont. ) v Type incorrect example SELECT E# FROM EMP WHERE ENAME>200 ! Undefined attribute ! Type mismatch 34

2) Analysis (cont. ) v Semantically incorrect w Components do not contribute in any way to the generation of the result w For only those queries that do not use disjunction ( ) or negation ( ), semantic correctness can be determined by using query graph 35

Query Graph v Two kinds of nodes w One node represents the result relation w Other nodes represent operand relations v Two types of edges w an edge to represent a join if neither of its two nodes is the result w an edge to represent a projection if one of its node is the result node Nodes and edges may be labeled by predicates for selection, projection, or join. 36

Query Graph Example SELECT FROM WHERE ENAME, RESP EMP, ASG, PROJ EMP. ENO=ASG. GNO AND ASG. PNO=PROJ. PNO AND PNAME=“CAD/CAM” AND DUR>36 AND TITLE=“Programmer” 37

Join Graph Example 1 A subgraph of query graph for join operation. 38

Tool of Analysis v A conjunctive query without negation is semantically incorrect if its query graph is NOT connected! 39

Analysis Example 2 SELECTENAME, RESP FROM EMP, ASG, PROJ WHERE EMP. ENO=ASG. GNO AND 40

Query Graph Example 2 41

3) Simplification v Using idempotency rules to eliminate redundant predicates from WHERE clause. 42

Simplification Example SELECT TITLE FROM EMP WHERE (NOT(TITLE=”Programmer) AND (TITLE=“Programmer” OR TITLE=“Electrical Eng. ”) AND NOT(TITLE=“Electrical Eng. ”)) OR ENAME=“J. Doe” is equivalent to SELECT TITLE FROM EMP WHERE ENAME="J. Doe" 43

Simplification Example (cont. ) p 1 = <TITLE = ``Programmer''> p 2 = <TITLE = ``Elec. Engr''> p 3 = <ENAME = ``J. Doe''> Let the query qualification is (¬ p 1 (p 1 p 2) ¬ p 2) p 3 The disjunctive normal form of the query is = (¬ p 1 ¬p 2) (¬ p 1 p 2 ¬ p 2) p 3 = (false ¬ p 2) (¬ p 1 false) Ú p 3 = false p 3 = p 3 44

4) Rewriting v Converting a calculus query in relational algebra w straightforward transformation from relational calculus to relational algebra w restructuring relational algebra expression to improve performance w making use of query trees 45

Relational Algebra Tree v. A tree defined by: w a root node representing the query result w leaves representing database relations w non-leaf nodes representing relations produced by operations w edges from leaves to root representing the sequences of operations 46

An SQL Query and Its Query Tree ENAME (ENAME<>“J. DOE” ) (JNAME=“CAD/CAM” ) (Dur=12 Dur=24) SELECT FROM WHERE AND AND Ename J, G, E G. Eno=E. ENo G. JNo = J. JNo ENAME <> `J. Doe' JName = `CAD' AND (Dur=12 or Dur=24) JNO ENO PROJ ASG EMP 47

How to translate an SQL query into an algebra tree? 1. Create a leaf for every relation in the FROM clause 2. Create the root as a project operation involving attributes in the SELECT clause 3. Create the operation sequence by the predicates and operators in the WHERE clause 48

Rewriting -- Transformation Rules (I) v Commutativity of binary operations: R SS R R R S S R v Associativity of binary operations: (R (R S)S) T T RR ( S(S T T) ) v Idempotence of unary operations: grouping of projections and w A’ ( A’’ (R )) A’ (R ) for A’ A’’ A w p 1(A 1) ( p 2(A 2) (R )) p 1(A 1) p 2(A 2) (R ) 49

Rewriting -- Transformation Rules (II) v Commuting selection with projection A 1, …, An ( p (Ap) (R )) A 1, …, An ( p (Ap) ( A 1, …, An, Ap(R ))) v Commuting selection with binary operations p (Ai)(R S) ( p (Ai)(R)) S p (Ai)(R S) ( p (Ai)(R)) S p (Ai)(R S) p (Ai)(R) p (Ai)(S) v Commuting projection with binary operations C(R S) A(R) B (S) C = A B C(R S) C(R) C (S) C (R S) C (R) C (S) 50

How to use transformation rules to optimize? v Unary operations on the same relation may be grouped to access the same relation once v Unary operations may be commuted with binary operations, so that they may be performed first to reduce the size of intermediate relations v Binary operations may be reordered 51

Optimization of Previous Query Tree 52

Step 2 : Data Localization v Task : To translate a query on global relation into algebra queries on physical fragments, and optimize the query by reduction. 53

Data Localization-- An Example EMP is fragmented into EMP 1 = ENO “E 3” (EMP) EMP 2 = “E 3” < ENO “E 6” (EMP) EMP 3 = ENO >“E 6” (EMP) ENAME Dur=12 Dur=24 ENAME<>“J. DOE” ASG is fragmented into ASG 1 = ENO “E 3” (ASG) ASG 2 = ENO >“E 3” (ASG) JNAME=“CAD/CAM” JNO ENO PROJ ASG 1 ASG 2 EMP 1 EMP 2 EMP 3 54

Reduction with Selection for PHF SELECT * FROM EMP WHERE ENO=“E 5” EMP is fragmented into EMP 1 = ENO “E 3” (EMP) EMP 2 = “E 3” < ENO “E 6” (EMP) EMP 3 = ENO >“E 6” (EMP) Given Relation R, FR={R 1, R 2, …, Rn} where Rj = pj(R) pj(Rj) = if x R: (pi(x) pj(x)) ENO=“E 5” EMP ENO=“E 5” EMP 1 EMP 2 EMP 3 EMP 2 55

Reduction with Join for PHF SELECT * FROM EMP, ASG WHERE EMP. ENO=ASG. ENO ASG 1 ASG 2 EMP 1 EMP 2 EMP 3 EMP ASG is fragmented into ASG 1 = ENO “E 3” (ASG) ASG 2 = ENO >“E 3” (ASG) EMP is fragmented into EMP 1 = ENO “E 3” (EMP) EMP 2 = “E 3” < ENO “E 6” (EMP) EMP 3 = ENO >“E 6” (EMP) 56

Reduction with Join for PHF (I) (R 1 R 2) S (R 1 S) (R 2 ENO ASG 1 ASG 2 EMP 1 EMP 2 S) EMP 3 ENO ENO ENO EMP 1 ASG 1 EMP 1 ASG 2 EMP 2 ASG 1 EMP 2 ASG 2 EMP 3 ASG 1 EMP 3 ASG 2 57

Reduction with Join for PHF (II) ENO EMP 1 ASG 1 ENO EMP 2 ASG 2 ENO EMP 3 ASG 2 Given Ri = pi(R) and Rj = pj(R) Ri Rj = if x Ri , y Rj: (pi(x) pj(y)) Reduction with join 1. Distribute join over union 2. Eliminate unnecessary work 58

Reduction for VF v Find useless intermediate relations Relation R defined over attributes A = {A 1, A 2, …, An} vertically fragmented as Ri = A’ (R) where A’ A D (Ri) is useless if the set of projection attributes D is not in A’ EMP 1= ENO, ENAME (EMP) EMP 2= ENO, TITLE (EMP) SELECT ENAME FROM EMP ENAME ENO EMP 1 EMP 2 EMP 1 59

Reduction for DHF Distribute joins over union Apply the join reduction for horizontal fragmentation EMP 1: TITLE=“Programmer” (EMP) EMP 2: TITLE “Programmer” (EMP) ASG 1: ASG ENO EMP 1 ASG 2: ASG ENO EMP 2 SELECT * ASG 1 FROM EMP, ASG WHERE ASG. ENO = EMP. ENO AND EMP. TITLE = “Mech. Eng. ” ENO TITLE=“MECH. Eng. ” ASG 2 EMP 1 EMP 2 60

Reduction for DHF (II) ENO Selection first Joins over union TITLE=“Mech. Eng. ” ASG 1 ASG 2 EMP 2 ENO TITLE=“Mech. Eng. ” ASG 2 ASG 1 EMP 2 TITLE=“Mech. Eng. ” ASG 1 EMP 2 ASG 1 ASG 2 ENO TITLE=“Mech. Eng. ” EMP 2 61

Reduction for Hybrid Fragmentation v Combines the rules already specified: w Remove empty relations generated by contradicting selection on horizontal fragments; w Remove useless relations generated by projections on vertical fragments; w Distribute joins over unions in order to isolate and remove useless joins. 62

Reduction for Hybrid Fragmentation - Example EMP 1 = ENO “E 4” ( ENO, ENAME (EMP)) EMP 2 = ENO>“E 4” ( ENO, ENAME (EMP)) EMP 3 = ENO, TITLE (EMP) ENAME QUERY: SELECT ENAME FROM EMP WHERE ENO = “E 5” ENO=“E 5” EMP 2 ENO EMP 1 ASG 1 EMP 2 EMP 3 63

Question & Answer 64