2011-10-04-QueryOptimization

2011-10-04-QueryOptimization - Query Optimization Database...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
Query Optimization Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 1 Schema for Examples ± Similar to old schema; rname added for variations. Sailors ( sid : integer , sname : string, rating : integer, age : real) Reserves ( sid : integer, bid : integer, day : dates , rname : string) Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 2 ± Reserves: ² Each tuple is 40 bytes long, 100 tuples per page, 1000 pages. ± Sailors: ² Each tuple is 50 bytes long, 80 tuples per page, 500 pages. Motivating Example SELECT S.sname FROM Reserves R, Sailors S WHERE R.sid=S.sid AND R.bid=100 AND S.rating>5 bid=100 rating > 5 sname (Page Nested Loops) (On-the-fly) (On-the-fly) Plan: Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 3 ± Cost: 500+500*1000 I/Os ± By no means the worst plan! ± But can do better (how?) Reserves Sailors sid=sid bid=100 rating > 5 sname RA Tree: Reserves Sailors sid=sid
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Alternative Plans 1 (No Indexes) ± Main difference: push selects. ± With 5 buffers, cost of plan: Scan Reserves (1000) + write temp T1 (10 pages if we have 100 boats Reserves Sailors sid=sid bid=100 sname (On-the-fly) rating > 5 (Scan; write to temp T1) (Scan; write to temp T2) (Sort-Merge Join) Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 4 ² Scan Reserves (1000) + write temp T1 (10 pages, if we have 100 boats, uniform distribution). ² Scan Sailors (500) + write temp T2 (250 pages, if we have 10 ratings). ² Sort T1 (2*2*10), sort T2 (2*3*250), merge (10+250) ² Total: 3560 page I/Os. ± If we used BNL join, join cost = 10+4*250, total cost = 2770. ± If we `push’ projections , T1 has only sid , T2 only sid and sname : ² T1 fits in 3 pages, cost of BNL drops to under 250 pages, total < 2000. Alternative Plans 2 With Indexes ± With clustered index on bid of Reserves, we get 100,000/100 = 1000 tuples on 1000/100 = 10 pages. ± INL with pipelining (outer is not materialized) Sailors sid=sid bid=100 sname (On-the-fly) rating > 5 (Use hash index; do not write result to temp) (Index Nested Loops, with pipelining ) (On-the-fly) Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 5 . ± Decision not to push rating>5 before the join is based on availability of sid index on Sailors. ± Cost: Selection of Reserves tuples (10 I/Os); for each, must get matching Sailors tuple (1000*1.2); total 1210 I/Os. ± Join column sid is a key for Sailors. –At most one matching tuple, unclustered index on sid OK. –Projecting out unnecessary fields from outer doesn’t help. Reserves Overview of Query Optimization ± Plan : Tree of R.A. ops, with choice of alg for each op. ² Each operator typically implemented using a `pull interface: when an operator is `pulled for the next output tuples, it `pulls on its inputs and computes them. Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 6 ± Two main issues: ² For a given query, what plans are considered ? •A lgor ithm to search p lan space for cheapest (est imated) p .
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

Page1 / 10

2011-10-04-QueryOptimization - Query Optimization Database...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online