Query Optimization Advanced Databases By M. Akhtar Ali

1. Query Optimization Advanced Databases By M. Akhtar Ali

2. Implementation of Selection (summary)Assuming R.attr op value (R) • No Index is available on attr and R is not sorted on attr • Cost = M I/Os, where M is the number of pages in R • No Index is available on attr and R is sorted on attr • Cost = log2M + T I/Os, where T is the number of pages read for retrieving the qualifying tuples • B+ Tree Index (clustered) is available on attr • Cost = B + T I/Os, where B is the height of the index (i.e. 2). • Hash Index (clustered) is available on attr • If attr is not a primary key: • Cost = H + T I/Os, where H (i.e. 1) is the I/O required to obtain the rids of the qualifying tuples. • If attr is a primary key: • Cost = (H + 1) * TP I/Os, where TP is the number of the qualifying tuples.

3. Implementation of Projection • To implement projection, we have to: • Remove unwanted attributes (attributes not specified in the query). • Eliminate any duplicate tuples that are produced. • The second step of removing duplicates is very difficult and costly. • There are two algorithms for projection using • Sorting • Hashing • We will consider only the sorting based algorithm.

4. Projection operator – an example query • Consider the following query: SELECT sid, bid FROM Reserves • Assume that combined size of attributes sid and bid is 10 bytes. The result of the above query will contain 100,000 tuples and will occupy 250 pages (each page now contains 400 tuples).

5. Projection based on Sorting • The algorithm has the following steps: • Scan R and produce a relation with only specified attributes (discarding unwanted attributes). • Sort the above temporary relation using all the projected attributes as a key for the sorting order. • Scan the sorted temporary relation and discard duplicates by comparing adjacent tuples. • The above algorithm can be improved by combining projection with duplicate elimination. • If there are B number of buffer pages, M number of pages in relation R then the cost of projection is: • Cost of projection = M I/O, suppose that the projected relation is of size T pages, then • Cost of Sorting = 2 * T * ( log B-1 2* B) • The total cost = M + 2 * T * ( log B-1 2* B)

6. Projection based on Sorting … • Let us compute the cost of Projection for our example: • Cost of projection = 1000 I/O • The result of the projection will contain 100,000 tuples and will occupy 250 pages, so Let T = 250 • Let we have 20 buffer pages, so Let B = 20 • Cost of Sorting = 2 * T * ( log B-1 2 * B) = 2 * 250 * ( log 19 2 * 20) = 2 * 250 * ( log 40 / log 19) = 2 * 250 * (1.6020 / 1.2787) = 2 * 250 * ( 1.25  1) = 2 * 250 * ( 1) = 500 • The total cost = 1000 + 500 = 1500 I/Os.

7. Implementation of Join Operator • Consider the query: SELECT * FROM Reserves R, Sailors S WHERE R.sid = S.sid This query is translated into Reserves ⋈Sailors • Join operation is very useful and widely used in relational query processing. • Consider some terminologies for R ⋈S • Suppose that there are M pages in R, pR tuples per page, • Suppose that there are N pages in S, pS tuples per page

8. Simple Nested Loops Join (tuple-oriented) • For each tuple in the outer relation R, we scan the entire inner relation S: Foreach tuple r in R do Foreach tuple s in S do If ri = sj then add <r, s> to result • Cost = M + pR * M * N, where • M is the number of pages in R, • pR is the number of tuples of R per page, and • N is the number of pages in S. • Cost for our example is: • 1000 + 100 * 1000 * 500 = 50,001,000 I/Os

9. Simple Nested Loops Join (page-oriented) • For each page of the outer relation R, we scan the entire inner relation S: Foreach page of R do Foreach page of S do If ri = sj then add <r, s> to result, where r is in R-page and s is in S-page • Cost = M + M * N • Cost for our example is: • 1000 + 1000 * 500 = 501,000 I/Os • Which is about 100 times less than the tuple-oriented simple nested loops (SNL) join for the same example. • If smaller relation i.e. S is outer then • Cost = 500 + 500 * 1000 = 500,500 I/Os • Always use smaller relation in the outer loop

10. R & S Join Result Hash table for block of R (k < B-1 pages) . . . . . . . . . Output buffer Input buffer for S Block-Nested Loops Join • Use one page as an input buffer for scanning the inner S, one page as the output buffer, and use all remaining pages to hold “block” of outer R: Foreach block of B – 2 pages of R do Foreach page of S do If ri = sj then add <r, s> to result, where r is in R-page and s is in S-page

11. Block-Nested Loops Join … • Cost: • With Reserves (R) as outer, B = 102 pages (buffer size) • Cost = • Which is 83 times faster than that of the same example using page-oriented SNL (i.e., 501,000 I/Os) • With 100-page block of Sailors as outer • Cost = • Which is 91 times faster than that of the same example using page-oriented SNL (i.e., 500,500 I/Os)

12. Overview of Query Optimization • Logical Optimization: Using some transformation rules and algebraic equivalences • To choose different join orders • R ⋈S  R ⋈S (Commutative) • (R ⋈S) ⋈T  R ⋈ (S ⋈T) (Associative) • To push selections and projections bellow of joins • (attr1 op value (R) ) ⋈S  attr1 op value(R ⋈S ) • attr1, attr2, attr3 ((attr1, attr2 (R) ) ⋈S )  attr1, attr2,attr3 (R ⋈S ) • Physical Optimization: For a given R.A expression • There could be several different plans possible using different implementation of the R.A operators • Calculate the cost of these different plans and choose the best • Ideally, we want the best plan, but • Practically, we should avoid worst plans

13. Pushing Selection & Projection below Join • A join is quite expensive operation. • The cost can be reduced by reducing the sizes of the input relations. • The sizes of the Input Relations can be reduced by applying: • Selection: restricting the input relation. • Projection: reducing the number of columns. • Usually Selection reduces the size of the input relation more than Projection. • Projection before the Join should be done quite carefully as the cost of Projection could increase the overall cost if it does not reduce by a good factor the size of the input.

14. Estimating the Evaluation Cost of a Plan • For each plan, we should be able to estimate the overall cost. • For each node in the query tree, we estimate the cost of performing the corresponding operation; • For each node in the query tree, we estimate the size of the result, which is used by the operation in the parent node; • In order to correctly estimate the cost of each operation and the size of its result, the optimizer uses certain statistical information maintained by the DBMS.

15. Statistics Maintained by a DBMS • Cardinality • The number of tuples for each relation. • Size • The number of pages for each relation. • Index Cardinality • The number of distinct key values for each index. • Index Size • The number of pages for each index. • Index Height • The number of non-leaf levels for each tree index. • Index Range • The minimum present key value and the maximum present key value for each index.

16. A motivating example SELECT S.sname FROM Reserves R, Sailors S Where R.sid = S.sid AND R.bid = 100 AND S.rating > 5 • Cost = 500 + 500 * 1000 I/Os • 500,500 I/Os • This is a worst plan • We could push selections down before join, no index is used. • Goal of Optimization: • To find more efficient plans that compute the same answer.

17. Optimization: Alternative 1 (no index) • Push Selects before join • Assuming that there are 1000 tuples in Reserves with bid=100 and 20000 tuples in Sailors with rating > 5. So Cost of Selections: • Scan Reserves (1000 pages) and write the selected 1000 tuples to temp relation T1 (10 pages), so in total 1010 I/Os • Scan Sailors (500 pages) and write the selected 20000 tuples to temp relation T2 (250 pages), so in total 750 I/Os • Total cost = 1010 + 750 = 1760 I/Os so far • Using SNL the cost = 10 + 10 * 250 = 2510 I/Os • Total cost = 1760 + 2510 = 4270 I/Os about 117 times less than the cost of the initial plan i.e. 500,500 I/Os

18. Optimization: Alternative 2 (uses indexes) • The same RA as alternative 1 • Using a clustered Hash index on Reserves: • A hash index will take 1 plus 10 to retrieve the 1000 qualifying tuples • Cost of Selection = 1 + 10 = 11 I/Os • Cost of Writing to T1 = 10 I/Os • Sub-Total = 11 +10 = 21 • The size of T1 = 10 pages • Using a clustered B+ tree index on Sailors: • Cost of Selection = 2 (the constant cost) + 250 = 252 I/Os, Cost of Writing to T2 = 250 I/Os, Sub-Total = 252 + 250 = 502, and the size of T2 = 250 pages • Using SNL the cost is: • Cost = 10 + 10 * 250 = 2510 I/Os • Total cost = 21 + 502 + 2510 = 3033 I/Os, which is 165 times less than the cost of the initial plan i.e. 500,500 I/Os

19. Optimization: Alternative 3 (using pipelining) • The same RA as alternative 1 • Using a clustered Hash index on Reserves: • A hash index will take 1 plus 10 to retrieve the 1000 qualifying tuples • Cost of Selection = 1 + 10 = 11 I/Os • The size of T1 = 10 pages • Using a clustered B+ tree index on Sailors: • Cost of Selection = 2 (the constant cost) + 250 = 252 I/Os • The size of T2 = 250 pages • Using Block-Nested Loops join with 7 buffer pages: • Cost = • Total cost = 11 + 252 + 510 = 773 I/Os, which is 647 times faster than the initial plan and about 4 times faster than the previous one.

20. Summary • Query optimization is very important component of a DBMS. • We must understand the principals of optimization to know how it influences the performance of a database system. • Two ways of optimization: • Logical: Push projection/selection below the join • Physical: Using indexes and better implementation of relational operators.