Arranging the Join Order the WongYoussefi algorithm INGRES

Arranging the Join Order: the Wong-Youssefi algorithm (INGRES) Sample TPC-H Schema Nation(Nation. Key, NName) Customer(Cust. Key, CName, Nation. Key) Find the Order(Order. Key, Cust. Key, Status) Lineitem(Order. Key, Part. Key, Quantity) names of suppliers that Product(Supp. Key, Part. Key, PName) sell a product that appears Supplier(Supp. Key, SName) SELECT SName FROM Nation, Customer, Order, Line. Item, Product, Supplier WHERE Nation. Key = Cuctomer. Nation. Key AND Customer. Cust. Key = Order. Cust. Key AND Order. Key=Line. Item. Order. Key AND Line. Item. Part. Key= Product. Partkey AND Product. Suppkey = Supplier. Supp. Key AND NName = “Canada” in a line item of an order made by a customer who is in Canada

Challenges with Large Natural Join Expressions For simplicity, assume that in the query 1. All joins are natural 2. whenever two tables of the FROM clause have common attributes we join on them πSName 1. Consider Right-Index only RI RI One possible order RI RI RI σIndex NName=“Canada” Nation Customer Order Line. Item Product Supplier

Multiple Possible Orders πSName RI RI RI σNName=“Canada” Order Nation Line. Item Product Supplier Customer

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 relation – A join with a small relation results in a small relation • Objective: Create sequence of index-based joins such that all intermediate results are small

Hypergraphs Customer Nation. Key CName Cust. Key Nation NName Order Status Order. Key Quantity Supplier SName Line. Item Supp. Key PName Part. Key • relation hyperedges • two hyperedges for same relation are possible • each node is an attribute • can extend for non-natural equality joins by merging nodes Product

Small Relations/Hypergraph Reduction “Nation” is small because it has the equality selection NName = “Canada” Customer Nation. Key CName Cust. Key Nation NName Order Status σIndex NName=“Canada” Nation Order. Key Quantity Supplier SName Line. Item Supp. Key Pick a small relation (and its conditions) to start the plan PName Part. Key Product

Remove small relation (hypergraph reduction) and color as “small” any relation that joins with the removed “small” relation Customer Nation. Key CName Cust. Key Nation NName Order Status RI σIndex NName=“Canada” Nation Customer Order. Key Quantity Supplier SName Line. Item Supp. Key PName Pick a small relation (and its conditions if any) and join it with the small relation that has been reduced Part. Key Product

After a bunch of steps… πSName RI RI RI σIndex NName=“Canada” Nation Customer Order Line. Item Product Supplier

Multiple Instances of Each Relation SELECT S. SName FROM Nation, Customer, Order, Line. Item L, Product P, Supplier S, Line. Item LE, Product PE, Supplier Enron WHERE Nation. Key = Cuctomer. Nation. Key AND Customer. Cust. Key = Order. Cust. Key AND Order. Key=L. Order. Key AND L. Part. Key= P. Partkey AND P. Suppkey = S. Supp. Key AND Order. Key=LE. Order. Key AND LE. Part. Key= PE. Partkey AND PE. Suppkey = Enron. Supp. Key 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 order

Multiple Instances of Each Relation Customer Nation. Key CName Cust. Key Nation NName Line. Item L Order Status Order. Key Quantity Supplier S SName Supp. Key PName Part. Key Product P Supplier Enron SName Supp. Key PName Product PE Part. Key Line. Item LE

Multiple choices are possible Customer Nation. Key CName Cust. Key Nation NName Line. Item L Order Status Order. Key Quantity Supplier S SName Supp. Key PName Part. Key Product P Supplier Enron SName Supp. Key PName Product PE Part. Key Line. Item LE

Customer Nation. Key CName Cust. Key Nation NName Line. Item L Order Status Order. Key Quantity Supplier S SName Supp. Key PName Part. Key Product P Supplier Enron SName Supp. Key PName Product PE Part. Key Line. Item LE

Customer Nation. Key CName Cust. Key Nation NName Line. Item L Order Status Order. Key Quantity Supplier S SName Supp. Key PName Part. Key Product P Supplier Enron SName Supp. Key PName Product PE Part. Key Line. Item LE

RI RI RI Line. Item Product Supplie RI RI Index σSName=“Enron” Enron RI RI Index σNName=“Cayman” PE LE Nation Customer Order