/
Arranging the Join Order: the Wong-Youssefi algorithm (INGR Arranging the Join Order: the Wong-Youssefi algorithm (INGR

Arranging the Join Order: the Wong-Youssefi algorithm (INGR - PowerPoint Presentation

pasty-toler
pasty-toler . @pasty-toler
Follow
384 views
Uploaded On 2015-12-04

Arranging the Join Order: the Wong-Youssefi algorithm (INGR - PPT Presentation

Sample TPCH Schema Nation NationKey NName Customer CustKey CName NationKey Order OrderKey CustKey Status Lineitem OrderKey PartKey Quantity Product ID: 214193

product order supplier lineitem order product lineitem supplier suppkey customer nname nation sname nationkey orderkey relation pname partkey small

Share:

Link:

Embed:

Download Presentation from below link

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.


Presentation Transcript

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