CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of...
Transcript of CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of...
![Page 1: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/1.jpg)
CSE 4125: Distributed Database Systems
Chapter – 6
Optimization of Access Strategies.
(part – C)
1 Jubair | AUST
![Page 2: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/2.jpg)
Outline
• Semi-join programs.
• Full reducer.
Jubair | AUST 2
![Page 3: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/3.jpg)
Semi-join Programs
R JNC=A S ↔ (R SJC=A PJA S) JNC=A S
Jubair | AUST 3
![Page 4: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/4.jpg)
Semi-join Programs (contd.)
R JNC=A S ↔ (R SJC=A PJA S) JNC=A S
Jubair | AUST 4
S S’ = PJAS
S’ R
R’ = R SJ C=A S’
R’
Q = R’ JN C=A S
site 1 site 2
S’
S’
R’ R’
![Page 5: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/5.jpg)
Semi-join Programs (contd.)
Cost of Semi-join program
• Cost of sending S’ to site – 2: TC1 = ?
Jubair | AUST 5
![Page 6: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/6.jpg)
Semi-join Programs (contd.)
Cost of Semi-join program
• Cost of sending S’ to site – 2: TC1 = C0+C1*size(A)*val(A[S])
Jubair | AUST 6
![Page 7: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/7.jpg)
Semi-join Programs (contd.)
Cost of Semi-join program
• Cost of sending S’ to site – 2: TC1 = C0+C1*size(A)*val(A[S])
• Cost of computing R’ at site – 2: TC2 = ?
Jubair | AUST 7
![Page 8: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/8.jpg)
Semi-join Programs (contd.)
Cost of Semi-join program
• Cost of sending S’ to site – 2: TC1 = C0+C1*size(A)*val(A[S])
• Cost of computing R’ at site – 2: TC2 = 0
Jubair | AUST 8
![Page 9: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/9.jpg)
Semi-join Programs (contd.)
Cost of Semi-join program
• Cost of sending S’ to site – 2: TC1 = C0+C1*size(A)*val(A[S])
• Cost of computing R’ at site – 2: TC2 = 0
• Cost of sending R’ to site – 1: TC3 = ?
Jubair | AUST 9
![Page 10: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/10.jpg)
Semi-join Programs (contd.)
Cost of Semi-join program
• Cost of sending S’ to site – 2: TC1 = C0+C1*size(A)*val(A[S])
• Cost of computing R’ at site – 2: TC2 = 0
• Cost of sending R’ to site – 1: TC3 = C0+C1*size(R)*card(R’)
Jubair | AUST 10
![Page 11: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/11.jpg)
Semi-join Programs (contd.)
Cost of Semi-join program
• Cost of sending S’ to site – 2: TC1 = C0+C1*size(A)*val(A[S])
• Cost of computing R’ at site – 2: TC2 = 0
• Cost of sending R’ to site – 1: TC3 = C0+C1*size(R)*card(R’)
• Cost of computing Q at site – 1: TC4 = ?
Jubair | AUST 11
![Page 12: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/12.jpg)
Semi-join Programs (contd.)
Cost of Semi-join program
• Cost of sending S’ to site – 2: TC1 = C0+C1*size(A)*val(A[S])
• Cost of computing R’ at site – 2: TC2 = 0
• Cost of sending R’ to site – 1: TC3 = C0+C1*size(R)*card(R’)
• Cost of computing Q at site – 1: TC4 = 0
Jubair | AUST 12
![Page 13: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/13.jpg)
Semi-join Programs (contd.)
Cost of Semi-join program
• Total cost TCSJ = TC1 + TC2 + TC3 + TC4
• If TCSJ < TCJN then semi-join program is profitable.
– Here TCJN is the cost of performing join without semi-join program.
Jubair | AUST 13
![Page 14: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/14.jpg)
Other Applications of Semi-join Programs
• Semi-join programs can be used as fragment reducers (operations that can reduce cardinality of a relation).
– Similarly to unary operations.
• Full reducer:
– Chain of semi-joins.
Jubair | AUST 14
![Page 15: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/15.jpg)
Full Reducer
Jubair | AUST 15
R
A B
1 a
2 b
3 c
S
B C
a x
b y
c z
T
C A
x 2
y 3
z 4
R
S
T A=A
B=B C=C
![Page 16: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/16.jpg)
Full Reducer (contd.)
Jubair | AUST 16
R
A B
1 a
2 b
3 c
S
B C
a x
b y
c z
T
C A
x 2
y 3
z 4
R
S
T A=A
B=B C=C
R’ = R SJ A=A T
A B
2 b
3 c
![Page 17: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/17.jpg)
Full Reducer (contd.)
Jubair | AUST 17
R
A B
1 a
2 b
3 c
S
B C
a x
b y
c z
T
C A
x 2
y 3
z 4
R’
S
T A=A
B=B C=C
R’ = R SJ A=A T
A B
2 b
3 c
S’ = S SJ B=B R’
B C
b y
c z
![Page 18: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/18.jpg)
Full Reducer (contd.)
Jubair | AUST 18
R
A B
1 a
2 b
3 c
S
B C
a x
b y
c z
T
C A
x 2
y 3
z 4
R’
S’
T A=A
B=B C=C
R’ = R SJ A=A T
A B
2 b
3 c
S’ = S SJ B=B R’
B C
b y
c z
T’ = T SJ C=C S’
C A
y 3
z 4
![Page 19: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/19.jpg)
Full Reducer (contd.)
Jubair | AUST 19
R
A B
1 a
2 b
3 c
S
B C
a x
b y
c z
T
C A
x 2
y 3
z 4
R’
S’
T’ A=A
B=B C=C
R’ = R SJ A=A T
A B
2 b
3 c
S’ = S SJ B=B R’
B C
b y
c z
T’ = T SJ C=C S’
C A
y 3
z 4
R” = R’ SJ A=A T’
A B
3 c
![Page 20: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/20.jpg)
Full Reducer (contd.)
Jubair | AUST 20
R
A B
1 a
2 b
3 c
S
B C
a x
b y
c z
T
C A
x 2
y 3
z 4
R”
S’
T’ A=A
B=B C=C
R’ = R SJ A=A T
A B
2 b
3 c
S’ = S SJ B=B R’
B C
b y
c z
T’ = T SJ C=C S’
C A
y 3
z 4
R” = R’ SJ A=A T’
A B
3 c
S” = S’ SJ B=B R”
B C
c z
![Page 21: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/21.jpg)
Full Reducer (contd.)
Jubair | AUST 21
R
A B
1 a
2 b
3 c
S
B C
a x
b y
c z
T
C A
x 2
y 3
z 4
R”
S”
T’ A=A
B=B C=C
R’ = R SJ A=A T
A B
2 b
3 c
S’ = S SJ B=B R’
B C
b y
c z
T’ = T SJ C=C S’
C A
y 3
z 4
R” = R’ SJ A=A T’
A B
3 c
S” = S’ SJ B=B R”
B C
c z
T” = T’ SJ C=C S”
C A
z 4
![Page 22: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/22.jpg)
Full Reducer (contd.)
Jubair | AUST 22
R
A B
1 a
2 b
3 c
S
B C
a x
b y
c z
T
C A
x 2
y 3
z 4
R”
S”
T” A=A
B=B C=C
R’ = R SJ A=A T
A B
2 b
3 c
S’ = S SJ B=B R’
B C
b y
c z
T’ = T SJ C=C S’
C A
y 3
z 4
R” = R’ SJ A=A T’
A B
3 c
S” = S’ SJ B=B R”
B C
c z
T” = T’ SJ C=C S”
C A
z 4
R”’ = R” SJ A=A T”
Φ
![Page 23: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/23.jpg)
Full Reducer (contd.)
Jubair | AUST 23
R
A B
1 a
2 b
3 c
S
B C
a x
b y
c z
T
C A
x 2
y 3
z 4
R”’
S”
T” A=A
B=B C=C
R’ = R SJ A=A T
A B
2 b
3 c
S’ = S SJ B=B R’
B C
b y
c z
T’ = T SJ C=C S’
C A
y 3
z 4
R” = R’ SJ A=A T’
A B
3 c
S” = S’ SJ B=B R”
B C
c z
T” = T’ SJ C=C S”
C A
z 4
R”’ = R” SJ A=A T”
Φ
![Page 24: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/24.jpg)
Additional Reading
• Length of full reducer.
• Tree queries.
• “Best” reducer.
Jubair | AUST 24
![Page 25: CSE 4125: Distributed Database Systems Chapter - 3...Jubair | AUST 13 Other Applications of Semi-join Programs •Semi-join programs can be used as fragment reducers (operations that](https://reader036.fdocument.pub/reader036/viewer/2022071508/6128d9b0e38f5226b61b40a3/html5/thumbnails/25.jpg)
Practice Problems/ Questions
1. With an example, prove that the semi-join is not symmetric. [hint: page. 142]
2. Textbook exercise: 6.1, 6.4
Jubair | AUST 25