Review

1
Review

• Jun 5th, 2002

2
HW5.2
R R.a S.b S (52buffers)
Table Tuple Tuple/page Page
R 10000 10 1000
S 2000 10 200
3
Page-Oriented Nested Loop Join

• For each page in the outer relation R, we scan
  the entire inner relation S.
• Cost M M N
• __________________

4
Nested Join v.s. Index Join

• Simple Nested Join
• Cost M ( PRM ) N
• _______________________
• Page-oriented Simple Nested Join
• Cost M M N
• _______________________
• Index Join (Unclustered)
• Cost M ( PRM ) (1.2 1)
• _______________________
• When is Nested Join better than Index Join?
• _______________________

5
Blocked Nested Loop Join

• For each matching tuple r in R-block, s in
  S-page, addltr, sgt to result. Then read next
  R-block, scan S, etc.
• Cost M ( M / (B-2) ) N
• ____________________

6
Hash-Join

• Partition both relations using hash fn h1 R
  tuples in partition i will only match S tuples in
  partition i.
• __________

• Read in a partition of R, hash it using h2 (ltgt
  h1). Scan matching partition of S, search for
  matches.
• ________

7
Blocked Join v.s. Hash-Join

• Blocked Join
• Cost M ( M / (B-2) ) N
• ___________________
• Hash Join
• Cost 3 ( M N )
• ___________________
• When is Blocked Join better than Hash Join?
• ___________________

8
Sort-Join
Original Relation
Partitions(B-1) pages
INPUT
1
1

• Sorting both relations using Multi-way sort
  ________

2
OUTPUT
2
Multi sorting
. . .
B-1
M_ B-1
B main memory buffers
Disk
Disk
Partitions of R S
Join Result

• Read in each intermediate result of R and S,
  search for matches.
• ________

INPUT
1
_ M_ B-1
OUTPUT
Merge Join
1
_ N_ B-1
Disk
Disk
B main memory buffers
9
Sort-Merge Join v.s. Hash-Join

• Sort-Merge needs more buffer space
• Sort-Merge Join
• Cost 3 ( M N )
• Buffer Size ________________
• Hash Join
• Cost 3 ( M N )
• Buffer Size ________________
• Sort-Merge join is less sensitive to data skew
• Result of Sort-Merge join is sorted

10
HW5.4 SQL Transformation
SELECT DISTINCT F.FirstName, F.LastName FROM
GradStudents AS G, Faculty AS F, Advise AS
A WHERE G.LoginID A.Student AND F.LoginID
A.Advisor AND G.Office '224' GradStudents
157 tuples (20 distinct values for Office,
uniform distribution) Faculty 53 tuples Advise
87 tuples
11
HW5.4 SQL Transformation
SELECT EntryYear, count() FROM
GradStudentsWHERE FirstName 'David'GROUP BY
EntryYearHAVING EntryYear gt 1995ORDER BY
EntryYear DESC
SELECT EntryYear, COUNT() FROM
GradStudents WHERE FirstName 'David' GROUP BY
EntryYear HAVING COUNT() gt 10 ORDER BY
EntryYear DESC
12
HW5.4 SQL Transformation
SELECT FirstName FROM Faculty WHERE FirstName IN
( SELECT FirstName FROM GradStudents )
SELECT FirstName FROM Faculty WHERE FirstName NOT
IN ( SELECT FirstName FROM gradStudents )
13
HW5.4 SQL Transformation
SELECT LoginID FROM UndergradStudents WHERE
EntryYear gt ANY ( SELECT EntryYear FROM
GradStudents)
SELECT LoginID FROM UndergradStudents WHERE
EntryYear gt ALL ( SELECT EntryYear FROM
GradStudents)
14
HW4.1 XML XQuery
lt!ELEMENT products (product)gt lt!ELEMENT
product (name, price, description,
store)gt lt!ELEMENT store (name, phone, markup)gt

• Which products are sold at least in one store?
• What are the product-store pairs whose markup is
  no lower than 15?

• Which stores sell some products with a price
  higher than 50?
• Which products (except gizmo) are sold in some
  store that also sells the product gizmo?

15
HW4.1 XML XQuery
lt!ELEMENT products (product)gt lt!ELEMENT
product (name, price, description,
store)gt lt!ELEMENT store (name, phone, markup)gt

• Returns the names and prices of all products that
  are sold in all stores with a markup of 25.

• Returns the names and prices of all products that
  are sold at least at one store with a markup of
  25

16
HW4.1 XML XQuery
lt!ELEMENT products (product)gt lt!ELEMENT
product (name, price, description,
store)gt lt!ELEMENT store (name, phone, markup)gt
ltproductsgt FOR p IN documents(database.x
ml)//products/row RETURN ltproduct pid
p/pid/text()gt ltnamegtp/name/text()lt/namegt lt
pricegtp/price/text()lt/pricegt ltdescriptiongtp/d
escription/text()lt/descriptiongt FOR x IN
documents(database.xml)//sells/rowpid
p/pid FOR s IN documents(database.xml)//s
tores/rowsid x/sid RETURN ltstore sid
s/sid/text()gt ltnamegts/name/text()lt/na
megt ltphonegts/phone/text()lt/phonegt ltmarkup
gtx/markup/text)()lt/markupgt lt/storegt
lt/productgt lt/productsgt
17
Midterm 1
18
Midterm 1
Company (DeptID, Name, Budget, CEOEmployID,
CEOContratID, Since) Work-in (EmployID, Lot,
DeptID, Name, Budget, CEOEmployID, Since)
19
HW5.1 B Tree
20
B Tree Insert 70
21
B Tree Insert 155
22
B Tree Insert 165
23
B Tree Delete 10
24
B Tree Delete 8