Sample TPCH Schema Nation NationKey NName Customer CustKey CName NationKey Order OrderKey CustKey Status Lineitem OrderKey PartKey Quantity Product ID: 214193
Download Presentation The PPT/PDF document "Arranging the Join Order: the Wong-Youss..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Arranging the Join Order: the Wong-Youssefi algorithm (INGRES)
Sample TPC-H SchemaNation(NationKey, NName)Customer(CustKey, CName, NationKey)Order(OrderKey, CustKey, Status)Lineitem(OrderKey, PartKey, Quantity)Product(SuppKey, PartKey, PName)Supplier(SuppKey, SName)
SELECT SNameFROM Nation, Customer, Order, LineItem, Product, SupplierWHERE Nation.NationKey = Cuctomer.NationKey AND Customer.CustKey = Order.CustKey AND Order.OrderKey=LineItem.OrderKey AND LineItem.PartKey= Product.Partkey AND Product.Suppkey = Supplier.SuppKey AND NName = “Canada”
Find the names of suppliers that sell a product that appears in a line item of an order made by a customer who is in CanadaSlide2
Challenges with Large Natural Join Expressions
For simplicity, assume that in the queryAll joins are naturalwhenever two tables of the FROM clause have common attributes we join on themConsider Right-Index onlyNationCustomerOrderLineItemProductSupplier
σNName=“Canada”
π
SName
One possible order
RI
RI
RI
RI
RI
IndexSlide3
Multiple Possible Orders
NationCustomerOrderLineItemProductSupplierσNName=“Canada”
π
SName
RI
RI
RI
RI
RISlide4
Wong-Yussefi algorithm assumptions and objectives
Assumption 1 (weak): Indexes on all join attributes (keys and foreign keys)Assumption 2 (strong): At least one selection creates a small relationA join with a small relation results in a small relationObjective: Create sequence of index-based joins such that all intermediate results are smallSlide5
Hypergraphs
CNameCustKeyNationKey NName Status OrderKeyQuantityPartKeySuppKey PName SName relation hyperedges two hyperedges for same relation are possible each node is an attribute can extend for non-natural equality joins by merging nodes
NationCustomerOrderLineItemProduct
SupplierSlide6
Small Relations/Hypergraph Reduction
CNameCustKeyNationKey NName Status OrderKeyQuantityPartKeySuppKey PName SName NationCustomerOrder
LineItemProductSupplierNationKey NName
“Nation” is small because it has the equality selection NName = “Canada”
Nation
σ
NName=“Canada”
Index
Pick a small relation (and its conditions) to start the planSlide7
CName
CustKeyNationKey NName Status OrderKeyQuantityPartKeySuppKey PName SName NationCustomerOrder
LineItemProductSupplierNationKey NNameNation
σ
NName=“Canada”
Index
RI
Remove small relation (hypergraph reduction) and color as “small” any relation that joins with the removed “small” relation
Customer
Pick a small relation (and its conditions if any) and join it with the small relation that has been reducedSlide8
After a bunch of steps…
NationCustomerOrderLineItemProductSupplierσNName=“Canada”
π
SName
RI
RI
RI
RI
RI
IndexSlide9
Multiple Instances of Each Relation
SELECT S.SNameFROM Nation, Customer, Order, LineItem L, Product P, Supplier S, LineItem LE, Product PE, Supplier EnronWHERE Nation.NationKey = Cuctomer.NationKey AND Customer.CustKey = Order.CustKey AND Order.OrderKey=L.OrderKey AND L.PartKey= P.Partkey AND P.Suppkey = S.SuppKey AND Order.OrderKey=LE.OrderKey AND LE.PartKey= PE.Partkey AND PE.Suppkey = Enron.SuppKey AND Enron.Sname = “Enron” AND NName = “Cayman”Find the names of suppliers whose products appear in an order made by a customer who is in Cayman Islands and an Enron product appears in the same orderSlide10
Multiple Instances of Each Relation
CNameCustKeyNationKey NName Status OrderKeyQuantityPartKeySuppKey PName SName NationCustomerOrder
LineItem LProduct PSupplier SSuppKey PName PartKey
SName
Product PE
Supplier Enron
LineItem LE
QuantitySlide11
Multiple choices are possible
CNameCustKeyNationKey NName Status OrderKeyQuantityPartKeySuppKey PName SName NationCustomerOrder
LineItem LProduct PSupplier SSuppKey PName PartKey
SName
Product PE
Supplier Enron
LineItem LE
QuantitySlide12
CName
CustKeyNationKey NName Status OrderKeyQuantityPartKeySuppKey PName SName NationCustomerOrder
LineItem LProduct PSupplier SSuppKey PName PartKey SName
Product PE
Supplier Enron
LineItem LE
QuantitySlide13
CName
CustKeyNationKey NName Status OrderKeyQuantityPartKeySuppKey PName SName NationCustomer
OrderLineItem LProduct PSupplier S
SuppKey PName PartKey
SName
Product PE
Supplier Enron
LineItem LE
QuantitySlide14
Nation
CustomerOrderσNName=“Cayman”
RIRIIndex
Enron
PE
LE
σ
SName=“Enron”
RI
RI
Index
LineItem
Product
Supplier
RI
RI
RI