CS4224/CS5424 Lecture 9 Distributed Query Processing Query - PowerPoint PPT Presentation

CS4224/CS5424 Lecture 9 Distributed Query Processing

Query Processing • Translates query into a query plan that minimizes some cost function ◮ Minimize total cost ⋆ CPU cost, I/O cost, & communication cost ◮ Minimize response time ⋆ Time elapsed for query execution CS4224/CS5424: Sem 1, 2019/20 Distributed Query Processing 2

Example • Site A: Relations R ( a , c , · · · ) & S ( a , · · · ) • Site B: Relations T ( b , c , · · · ) & U ( b , · · · ) • Query at Site B : SELECT * FROM R, S, T, U WHERE R.a = S.a AND T.b = U.b AND R.c = T.c • Query Plans : ⊲ ⊳ ⊲ ⊳ U Site B ⊲ ⊳ Site B ⊲ ⊳ ⊲ ⊳ T ⊲ ⊳ R S T U R S Plan 1 Plan 2 CS4224/CS5424: Sem 1, 2019/20 Distributed Query Processing 3

Example (cont.) • Cost model : ◮ JC(X,Y) = CPU & I/O cost of joining relations X & Y ◮ CC(X) = Communication cost of sending relation X from one site to another site ◮ JC(R, S) = 2000, JC(T, U) = 2000 ◮ JC( R ⊲ ⊳ S , T) = 1000, JC( R ⊲ ⊳ T , U) = 600 ⊳ S ⊲ ◮ JC( R ⊲ ⊳ S , T ⊲ ⊳ U ) = 100, CC( R ⊲ ⊳ S ) = 200 ⊲ ⊳ ⊲ ⊳ U Site B ⊲ ⊳ Site B ⊲ ⊳ ⊲ ⊳ T ⊲ ⊳ R S T U R S Plan 1 Plan 2 CS4224/CS5424: Sem 1, 2019/20 Distributed Query Processing 4

Example (cont.) • Cost model : ◮ JC(X,Y) = CPU & I/O cost of joining relations X & Y ◮ CC(X) = Communication cost of sending relation X from one site to another site ◮ JC(R, S) = 2000, JC(T, U) = 2000 ◮ JC( R ⊲ ⊳ S , T) = 1000, JC( R ⊲ ⊳ T , U) = 600 ⊳ S ⊲ ◮ JC( R ⊲ ⊳ S , T ⊲ ⊳ U ) = 100, CC( R ⊲ ⊳ S ) = 200 600 100 U 200 1000 200 2000 2000 T 2000 R S T U R S Plan 1: Total Cost = 4300 Plan 2: Total Cost = 3800 CS4224/CS5424: Sem 1, 2019/20 Distributed Query Processing 5

Example (cont.) • Cost model : ◮ JC(X,Y) = CPU & I/O cost of joining relations X & Y ◮ CC(X) = Communication cost of sending relation X from one site to another site ◮ JC(R, S) = 2000, JC(T, U) = 2000 ◮ JC( R ⊲ ⊳ S , T) = 1000, JC( R ⊲ ⊳ T , U) = 600 ⊳ S ⊲ ◮ JC( R ⊲ ⊳ S , T ⊲ ⊳ U ) = 100, CC( R ⊲ ⊳ S ) = 200 600 100 U 200 1000 200 2000 2000 T 2000 R S T U R S Plan 1: Plan 2: Response Time = 2300 Response Time = 3800 CS4224/CS5424: Sem 1, 2019/20 Distributed Query Processing 6

Query Processing Steps • Query rewriting ◮ Query decomposition ⋆ Translates query into relational algebra query ◮ Data localization ⋆ Rewrites distributed query into a fragment query • Global query optimization ◮ Finds an optimal execution plan for query • Distributed query execution ◮ Executes query plan to compute query result CS4224/CS5424: Sem 1, 2019/20 Distributed Query Processing 7

Query Decomposition SELECT * ⊲ ⊳ R . a = S . a FROM R, S − → WHERE R.a = S.a S σ b = 20 AND R.b = 20 R • Normalization ◮ Rewrites query into some normal form • Semantic Analysis ◮ Checks that query is semantically correct • Simplification & Restructuring ◮ Rewrites query into simpler form (e.g., eliminates redundancy) CS4224/CS5424: Sem 1, 2019/20 Query Decomposition 8

Normalization • A simple predicate defined on a relation R is of the form “ A i op v ” where A i is an attribute of R , op ∈ { = , � = , <, ≤ , >, ≥} and v ∈ Domain ( A i ) • Conjunctive Normal Form (CNF) ( p 11 ∨ p 12 · · ·∨ p 1 n 1 ) ∧ · · ·∧ ( p m 1 ∨ p m 2 · · ·∨ p mn m ) • Disjunctive Normal Form (DNF) ( p 11 ∧ p 12 · · ·∧ p 1 n 1 ) ∨ · · ·∨ ( p m 1 ∧ p m 2 · · ·∧ p mn m ) • Each p ij is a simple predicate CS4224/CS5424: Sem 1, 2019/20 Query Decomposition: Normalization 9

Review of RA Equivalence Rules attributes(R) = Set of attributes in schema of relation R attributes(p) = Set of attributes in predicate p 1. Commutativity of binary operators 1.1 R × S ≡ S × R 1.2 R ⊲ ⊳ S ≡ S ⊲ ⊳ R 2. Associativity of binary operators 2.1 ( R × S ) × T ≡ R × ( S × T ) 2.2 ( R ⊲ ⊳ T ≡ R ⊲ ⊳ S ) ⊲ ⊳ ( S ⊲ ⊳ T ) 3. Idempotence of unary operators 3.1 π L ′ ( π L ( R )) ≡ π L ′ ( R ) if L ′ ⊆ L ⊆ attributes ( R ) 3.2 σ p 1 ( σ p 2 ( R )) ≡ σ p 1 ∧ p 2 ( R ) CS4224/CS5424: Sem 1, 2019/20 Query Decomposition: Restructuring 10

Review of RA Equivalence Rules (cont.) 4. Commutating selection with projection 4.1 π L ( σ p ( R )) ≡ π L ( σ p ( π L ∪ attributes ( p ) ( R ))) 5. Commutating selection with binary operators 5.1 σ p ( R × S ) ≡ σ p ( R ) × S if attributes ( p ) ⊆ attributes ( R ) 5.2 σ p ( R ⊲ ⊳ p ′ S ) ≡ σ p ( R ) ⊲ ⊳ p ′ S if attributes ( p ) ⊆ attributes ( R ) 5.3 σ p ( R ∪ S ) ≡ σ p ( R ) ∪ σ p ( S ) CS4224/CS5424: Sem 1, 2019/20 Query Decomposition: Restructuring 11

Review of RA Equivalence Rules (cont.) 6. Commutating projection with binary operators Let L = L R ∪ L S , where L R ⊆ attributes ( R ) and L S ⊆ attributes ( S ) 6.1 π L ( R × S ) ≡ π L R ( R ) × π L S ( S ) 6.2 π L ( R ⊲ ⊳ p S ) ≡ π L R ( R ) ⊲ ⊳ p π L S ( S ) if attributes ( p ) ∩ attributes ( R ) ⊆ L R and attributes ( p ) ∩ attributes ( S ) ⊆ L S 6.3 π L ( R ∪ S ) ≡ π L ( R ) ∪ π L ( S ) CS4224/CS5424: Sem 1, 2019/20 Query Decomposition: Restructuring 12

Example Student (sid, sname, major) Course (cid, cname, area) Enrol (sid,cid, grade) π sname , cname SELECT sname, cname σ ( major =” CS ”) ∧ ( area =” DB ”) ∧ ( grade =” F ”) FROM Student S, Course C, Enrol E WHERE E.sid = S.sid − → AND E.cid = C.cid ⊲ ⊳ C . cid = E . cid AND major = "CS" AND area = "DB" AND grade = "F" Course C ⊲ ⊳ E . sid = S . sid Enrol E Student S CS4224/CS5424: Sem 1, 2019/20 Query Decomposition: Restructuring 13

Example (cont.) π sname , cname σ major =” CS ” π sname , cname σ area =” DB ” σ ( major =” CS ”) ∧ ( area =” DB ”) ∧ ( grade =” F ”) 3 . 2 − → σ grade =” F ” ⊲ ⊳ C . cid = E . cid Course C ⊲ ⊳ E . sid = S . sid ⊲ ⊳ C . cid = E . cid Course C Enrol E Student S ⊲ ⊳ E . sid = S . sid Enrol E Student S σ p 1 ( σ p 2 ( R )) ≡ σ p 1 ∧ p 2 ( R ) CS4224/CS5424: Sem 1, 2019/20 Query Decomposition: Restructuring 14

Example (cont.) π sname , cname π sname , cname σ major =” CS ” σ major =” CS ” σ area =” DB ” σ area =” DB ” 5 . 2 ← − σ grade =” F ” ⊲ ⊳ C . cid = E . cid Course C σ grade =” F ” ⊲ ⊳ C . cid = E . cid Course C ⊲ ⊳ E . sid = S . sid ⊲ ⊳ E . sid = S . sid Enrol E Student S Enrol E Student S ⊳ p ′ S if attributes ( p ) ⊆ attributes ( R ) ⊳ p ′ S ) ≡ σ p ( R ) ⊲ σ p ( R ⊲ CS4224/CS5424: Sem 1, 2019/20 Query Decomposition: Restructuring 14

Example (cont.) π sname , cname π sname , cname σ major =” CS ” σ major =” CS ” σ area =” DB ” σ area =” DB ” 5 . 2 − → ⊲ ⊳ C . cid = E . cid ⊲ ⊳ C . cid = E . cid Course C Course C σ grade =” F ” ⊲ ⊳ E . sid = S . sid Student S ⊲ ⊳ E . sid = S . sid σ grade =” F ” Enrol E Enrol E Student S ⊳ p ′ S if attributes ( p ) ⊆ attributes ( R ) ⊳ p ′ S ) ≡ σ p ( R ) ⊲ σ p ( R ⊲ CS4224/CS5424: Sem 1, 2019/20 Query Decomposition: Restructuring 14

Example (cont.) π sname , cname π sname , cname σ major =” CS ” σ major =” CS ” σ area =” DB ” 5 . 2 ⊲ ⊳ C . cid = E . cid ← − ⊲ ⊳ C . cid = E . cid σ area =” DB ” ⊲ ⊳ E . sid = S . sid Course C ⊲ ⊳ E . sid = S . sid Course C Student S σ grade =” F ” Student S σ grade =” F ” Enrol E Enrol E ⊳ p ′ S if attributes ( p ) ⊆ attributes ( R ) ⊳ p ′ S ) ≡ σ p ( R ) ⊲ σ p ( R ⊲ CS4224/CS5424: Sem 1, 2019/20 Query Decomposition: Restructuring 14

Example (cont.) π sname , cname π sname , cname σ major =” CS ” ⊲ ⊳ C . cid = E . cid 5 . 2 ⊲ ⊳ C . cid = E . cid σ area =” DB ” σ major =” CS ” − → Course C σ area =” DB ” ⊲ ⊳ E . sid = S . sid ⊲ ⊳ E . sid = S . sid Student Course C Student S σ grade =” F ” σ grade =” F ” Enrol E Enrol E ⊳ p ′ S if attributes ( p ) ⊆ attributes ( R ) σ p ( R ⊲ ⊳ p ′ S ) ≡ σ p ( R ) ⊲ CS4224/CS5424: Sem 1, 2019/20 Query Decomposition: Restructuring 14

Example (cont.) π sname , cname π sname , cname ⊲ ⊳ C . cid = E . cid ⊲ ⊳ C . cid = E . cid 5 . 2 σ area =” DB ” σ major =” CS ” ← − σ area =” DB ” ⊲ ⊳ E . sid = S . sid Course C ⊲ ⊳ E . sid = S . sid Course C σ grade =” F ” σ major =” CS ” Student σ grade =” F ” Enrol E Student S Enrol E ⊳ p ′ S if attributes ( p ) ⊆ attributes ( R ) σ p ( R ⊲ ⊳ p ′ S ) ≡ σ p ( R ) ⊲ CS4224/CS5424: Sem 1, 2019/20 Query Decomposition: Restructuring 14

Example (cont.) π sname , cname π sname , cname π sname , cname , cid ⊲ ⊳ C . cid = E . cid 3 . 1 ⊲ ⊳ C . cid = E . cid − → σ area =” DB ” ⊲ ⊳ E . sid = S . sid σ area =” DB ” ⊲ ⊳ E . sid = S . sid Course C σ grade =” F ” σ major =” CS ” Course C σ grade =” F ” σ major =” Enrol E Student S Enrol E Student π L ′ ( π L ( R )) ≡ π L ′ ( R ) if L ′ ⊆ L ⊆ attributes ( R ) CS4224/CS5424: Sem 1, 2019/20 Query Decomposition: Restructuring 14