1 / 7

Query Evaluation Techniques in Database Systems

Explore various query evaluation methods like Nested Loop Join, Sort Merge Join, and Index Nested Loop Join. Learn about efficient join ordering strategies for optimal performance.

aysel
Download Presentation

Query Evaluation Techniques in Database Systems

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. ICS 421 Spring 2010Query Evaluation (iii) Asst. Prof. Lipyeow Lim Information & Computer Science Department University of Hawaii at Manoa Lipyeow Lim -- University of Hawaii at Manoa

  2. Outline Lipyeow Lim -- University of Hawaii at Manoa

  3. Example SELECTS.sname FROM Reserves R, Sailors S WHERE R.sid=S.sid AND R.bid=100 ANDS.rating>5 πS.sname R.sid=S.sid On the fly • Nested Loop Join cost 1K+ 100K*500 • On the fly selection and project does not incur any disk access. • Total disk access = 500001K (worst case) S.rating>5 AND R.bid=100 On the fly Nested Loop Join Reserves Sailors (SCAN) (SCAN) Lipyeow Lim -- University of Hawaii at Manoa

  4. Example: Predicate Pushdown SELECTS.sname FROM Reserves R, Sailors S WHERE R.sid=S.sid AND R.bid=100 ANDS.rating>5 πS.sname 10% 50% R.sid=S.sid On the fly • Nested Loop Join requires materializing the inner table as T1. • With 50% selectivity, T1 has 250 pages • With 10% selectivity, outer “table” in join has 10K tuples • Disk accesses for scans = 1000 + 500 • Writing T1 = 250 • NLJoin = 10K * 250 • Total disk access = 2500.175 K (worst case) Nested Loop Join Temp T1 R.bid=100 S.rating>5 (SCAN) (SCAN) Reserves Sailors What happens if we make the left leg the inner table of the join ? Lipyeow Lim -- University of Hawaii at Manoa

  5. Example: Sort Merge Join SELECTS.sname FROM Reserves R, Sailors S WHERE R.sid=S.sid AND R.bid=100 ANDS.rating>5 πS.sname 10% 50% R.sid=S.sid On the fly • Sort Merge Join requires materializing both legs for sorting. • With 50% selectivity, T1 has 100 pages • With 10% selectivity, T2 has 250 pages • Disk accesses for scans = 1000 + 500 • Writing T1 & T2 = 100 + 250 • Sort Merge Join = 100 log 100 + 250 log 250 + 100+250 (assume 10 way merge sort) • Total disk access = 52.8 K Sort Merge Join Temp T1 Temp T2 R.bid=100 S.rating>5 (SCAN) (SCAN) Reserves Sailors What happens if we make the left leg the inner table of the join ? Lipyeow Lim -- University of Hawaii at Manoa

  6. Example: Index Nested Loop Join SELECTS.sname FROM Reserves R, Sailors S WHERE R.sid=S.sid AND R.bid=100 ANDS.rating>5 πS.sname 10% 50% R.sid=S.sid On the fly • With 10% selectivity, selection on R has 10K tuples • Disk accesses for scan = 1000 • Index Nested Loop Join = 10K*( 1 + log10200) = 33K • Total disk access = 34 K S.rating>5 On the fly Index nested Loop Using Index on S.sid R.bid=100 (SCAN) Reserves Sailors What happens if we make the left leg the inner table of the join ? Lipyeow Lim -- University of Hawaii at Manoa

  7. Join Ordering 500 10K 30K 10K • Independent of what join algorithm is chosen, the order in which joins are perform affects the performance. • Rule of thumb: do the most “selective” join first • In practice, left deep trees (eg. the right one above) are preferred --- why ? A C 20K 30K 10K 20K C B B A Lipyeow Lim -- University of Hawaii at Manoa

More Related