1 / 18

Choosing an Order for Joins

Choosing an Order for Joins. Chapter 16.6 by: Chiu Luk ID: 210. Introduction. This section focuses on critical problem in cost-based optimization: Selecting order for natural join of three or more relations

chick
Download Presentation

Choosing an Order for Joins

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Choosing an Order for Joins Chapter 16.6 by: Chiu Luk ID: 210

  2. Introduction • This section focuses on critical problem in cost-based optimization: • Selecting order for natural join of three or more relations • Compared to other binary operations, joins take more time and therefore need effective optimization techniques

  3. Introduction

  4. Significance of Left and Right Join Arguments • The argument relations in joins determine the cost of the join • The left argument of the join is • Called the build relation • Assumed to be smaller • Stored in main-memory

  5. Significance of Left and Right Join Arguments • The right argument of the join is • Called the probe relation • Read a block at a time • Its tuples are matched with those of build relation • The join algorithms which distinguish between the arguments are: • One-pass join • Nested-loop join • Index join

  6. Join Trees Order of arguments is important for joining two relations Left argument, since stored in main-memory, should be smaller With two relations only two choices of join tree With more than two relations, there are n! ways to order the arguments and therefore n! join trees, where n is the no. of relations

  7. Join Trees Order of arguments is important for joining two relations Left argument, since stored in main-memory, should be smaller With two relations only two choices of join tree With more than two relations, there are n! ways to order the arguments and therefore n! join trees, where n is the no. of relations

  8. Join Trees Total # of tree shapes T(n) for n relations given by recurrence: T(1) = 1 T(2) = 1 T(3) = 2 T(4) = 5 … etc

  9. Left-Deep Join Trees Consider 4 relations. Different ways to join them are as follows

  10. In fig (a) all the right children are leaves. This is a left-deep tree In fig (c) all the left children are leaves. This is a right-deep tree Fig (b) is a bushy tree Considering left-deep trees is advantageous for deciding join orders

  11. Join order • Join order selection • A1 A2 A3 .. An • Left deep join trees • Dynamic programming • Best plan computed for each subset of relations • Best plan (A1, .., An) = min cost plan of( Best plan(A2, .., An) A1 Best plan(A1, A3, .., An) A2 …. Best plan(A1, .., An-1)) An An Ai

  12. Dynamic Programming to Select a Join Order and Grouping • Three choices to pick an order for the join of many relations are: • Consider all of the relations • Consider a subset • Use a heuristic o pick one • Dynamic programming is used either to consider all or a subset • Construct a table of costs based on relation size • Remember only the minimum entry which will required to proceed

  13. Dynamic Programming to Select a Join Order and Grouping

  14. Dynamic Programming to Select a Join Order and Grouping

  15. Dynamic Programming to Select a Join Order and Grouping

  16. Dynamic Programming to Select a Join Order and Grouping

  17. A Greedy Algorithm for Selecting a Join Order • It is expensive to use an exhaustive method like dynamic programming • Better approach is to use a join-order heuristic for the query optimization • Greedy algorithm is an example of that • Make one decision at a time about order of join and never backtrack on the decisions once made

  18. Thank you

More Related