Algorithms for Query Processing and Optimization

1. Algorithms for Query Processing and Optimization

2. 0. Basic Steps in Query Processing • 1. Parsing and translation: • Translate the query into its internal form. • This is then translated into relational algebra. • Parser checks syntax, verifies relations • 2. Query optimization: • The process of choosing a suitable execution strategy for processing a query. • 3. Evaluation: • The query evaluation engine takes a query execution plan, executes that plan, and returns the answers to the query.

3. Basic Steps in Query Processing

4. Basic Steps in Query Processing

5. Basic Steps in Query Processing • Two internal representations of a query: • Query Tree • Query Graph • Select balance From account where balance < 2500 • balance2500(balance(account)) is equivalent to • balance(balance2500(account))

6. Basic Steps in Query Processing • Annotated relational algebra expression specifying detailed evaluation strategy is called an evaluation-plan. • E.g., can use an index on balance to find accounts with balance  2500, • or can perform complete relation scan and discard accounts with balance  2500 • Query Optimization: • Amongst all equivalent evaluation plans choose the one with lowest cost. • Cost is estimated using statistical information from the database catalog • e.g. number of tuples in each relation, size of tuples

7. Measures of Query Cost • Cost is generally measured as total elapsed time for answering query. Many factors contribute to time cost • disk accesses, CPU, or even network communication • Typically disk access is the predominant cost, and is also relatively easy to estimate. • For simplicity we just use number of block transfers from disk as the cost measure • Costs depends on the size of the buffer in main memory • Having more memory reduces need for disk access

8. 1. Translating SQL Queries into Relational Algebra (1) • Query Block: • The basic unit that can be translated into the algebraic operators and optimized. • A query block contains a single SELECT-FROM-WHERE expression, as well as GROUP BY and HAVING clause if these are part of the block. • Nested queries within a query are identified as separate query blocks • Aggregate operators in SQL must be included in the extended algebra.

9. Translating SQL Queries into Relational Algebra (2) SELECT LNAME, FNAME FROM EMPLOYEE WHERE SALARY > ( SELECT MAX (SALARY) FROM EMPLOYEE WHERE DNO = 5); SELECT LNAME, FNAME FROM EMPLOYEE WHERE SALARY > C SELECT MAX (SALARY) FROM EMPLOYEE WHERE DNO = 5 πLNAME, FNAME(σSALARY>C(EMPLOYEE)) ℱMAX SALARY(σDNO=5 (EMPLOYEE))

10. 2. Algorithms for External Sorting (1) • Sorting is needed in • Order by, join, union, intersection, distinct, … • For relations that fit in memory, techniques like quicksort can be used. • External sorting: • Refers to sorting algorithms that are suitable for large files of records stored on disk that do not fit entirely in main memory, such as most database files. • For relations that don’t fit in memory, external sort-merge is a good choice

11. External Sort-Merge • External sort-merge algorithm has two steps: • Partial sort step, called runs. • Merge step, merges the sorted runs. • Sort-Merge strategy: • Starts by sorting small subfiles (runs) of the main file and then merges the sorted runs, creating larger sorted subfiles that are merged in turn. • Sorting phase: • Sorts nB pages at a time • nB = # of main memory pages buffer • creates nR = b/nBinitialsorted runs on disk • b = # of file blocks (pages) to be sorted • Sorting Cost = read b blocks + write b blocks = 2 b

12. External Sort-Merge • Example: • nB = 5 blocks and file size b = 1024 blocks, then • nR = (b/nB) = 1024/5 = 205 initial sorted runs each of size 5 bocks (except the last run which will have 4 blocks) nB = 2, b = 7, nR = b/nB = 4 run

13. External Sort-Merge • Sort Phase: creates nR sorted runs. i = 0 Repeat Read next nB blocks into RAM Sort the in-memory blocks Write sorted data to run file Ri i = i + 1 Until the end of the relation nR = i

14. External Sort-Merge • Merging phase: • The sorted runs are merged during one or more passes. • The degree of merging (dM) is the number of runs that can be merged in each pass. • In each pass, • One buffer block is needed to hold one block from each of the runs being merged, and • One block is needed for containing one block of the merged result.

15. External Sort-Merge • We assume (for now) that nRnB. • Merge the runs (nR-way merge). • Use nR blocks of memory to buffer input runs, and 1 block to buffer output. • Merge nR Runs Step Read 1st block of each nR runs Ri into its buffer page Repeat Select 1st record (sort order) among nR buffer pages Write the record to the output buffer. If the output buffer is full write it to disk Delete the record from its input buffer page If the buffer page becomes empty then read the next block (if any) of the run into the buffer Until all input buffer pages are empty

16. External Sort-Merge • Merge nB- 1 Runs Step • If nR nB, several merge passes are required. • merge a group of contiguous nB- 1 runs using one buffer for output • A pass reduces the number of runs by a factor of nB- 1, and creates runs longer by the same factor. • E.g. If nB = 11, and there are 90 runs, one pass reduces the number of runs to 9, each 10 times the size of the initial runs • Repeated passes are performed till all runs have been merged into one

17. External Sort-Merge • Degree of merging (dM) • # of runs that can be merged together in each pass = min (nB - 1, nR) • Number of passes nP = (logdM(nR)) • In our example • dM = 4 (four-way merging) • min (nB-1, nR) = min(5-1, 205) = 4 • Number of passes nP = (logdM(nR)) = (log4(205)) = 4 • First pass: • 205 initial sorted runs would be merged into 52 sorted runs • Second pass: • 52 sorted runs would be merged into 13 • Third pass: • 13 sorted runs would be merged into 4 • Fourth pass: • 4 sorted runs would be merged into 1

18. External Sort-Merge Blocking factor bfr = 1 record, nB = 3, b = 12, nR = 4, dM = min(3-1, 4) = 2

19. External Sort-Merge

20. External Sort-Merge • External Sort-Merge: Cost Analysis • Disk accesses for initial run creation (sort phase) as well as in eachmergepass is 2b • reads every block once and writes it out once • Initial # of runs is nR = b/nB and # of runs decreases by a factor of nB - 1 in each merge pass, then the total # of merge passes is np = logdM(nR) • In general, the cost performance of Merge-Sort is • Cost = sort cost + merge cost • Cost = 2b + 2b * np • Cost = 2b + 2b * logdM nR • =2b(logdM(nR) + 1)

21. External Sort-Merge

22. Catalog Information • File • r: # of records in the file • R: record size • b: # of blocks in the file • bfr: blocking factor • Index • x: # of levels of a multilevel index • bI1: # of first-level index blocks

23. Catalog Information • Attribute • d: # of distinct values of an attribute • sl (selectivity): • the ratio of the # of records satisfying the condition to the total # of records in the file. • s (selection cardinality) = sl * r • average # of records that will satisfy an equality condition on the attribute • For a key attribute: • d = r, sl = 1/r, s = 1 • For a nonkey attribute: • assuming that d distinct values are uniformly distributed among the records • the estimated sl = 1/d, s = r/d

24. File Scans • Types of scans • File scan – search algorithms that locate and retrieve records that fulfill a selection condition. • Index scan – search algorithms that use an index • selection condition must be on search-key of index. • Cost estimate C = # of disk blocks scanned

25. 3. Algorithms for SELECT Operations • Implementing the SELECT Operation • Examples: • (OP1): SSN='123456789' (EMP) • (OP2): DNUMBER>5(DEPT) • (OP3): DNO=5(EMP) • (OP4): DNO=5 AND SALARY>30000 AND SEX=F(EMP) • (OP5): ESSN=123456789 AND PNO=10(WORKS_ON)

26. Algorithms for Selection Operation • Search Methods for Simple Selection: • S1 (linear search) • Retrieve every record in the file, and test whether its attribute values satisfy the selection condition. • If selection is on a nonkey attribute, C = b • If selection is equality on a key attribute, • if record found, average cost C = b/2, else C = b

27. Algorithms for Selection Operation • S2 (binary search) • Applicable if selection is an equality comparison on the attribute on which file is ordered. • Assume that the blocks of a relation are stored contiguously • If selection is on a nonkey attribute: • C = log2b: cost of locating the 1st tuple + • s/bfr - 1: # of blocks containing records that satisfy selection condition • If selection is equality on a key attribute: • C = log2b, since s = 1, in this case

28. Selections Using Indices • S3 (primary or hash index on a key, equality) • Retrieve a single record that satisfies the corresponding equality condition • If the selection condition involves an equality comparison on a key attribute with a primary index (or a hash key), use the primary index (or the hash key) to retrieve the record. • Primary index: retrieve 1 more block than the # of index levels, • C = x + 1: • Hash index: • C =1: for static or linear hashing • C =2: for extendable hashing

29. Selections Using Indices • S4 (primary index on a key, range selection) • S4 Using a primary index to retrieve multiple records: • If the comparison condition is >, ≥, <, or ≤ on a key field with a primary index, use the index to find the record satisfying the corresponding equality condition, then retrieve all subsequent records in the (ordered) file. • Assuming relation is sorted on A • For Av(r) use index to find 1st tuple = v and retrieve all subsequent records. • For Av(r) use index to find 1st tuple = v and retrieve all preceding records. • OR just scan relation sequentially till 1st tuple  v;do not use index with average cost C = b/2 • Average costC = x + b/2

30. Selections Using Indices • S5 (clustered index on nonkey, equality) • Retrieve multiple records. • Records will be on consecutive blocks • C = x + s/bfr • # of blocks containing records that satisfy selection condition

31. Selections Using Indices • S6-1 (secondary index B+-tree, equality) • Retrieve a single record if the search-key is a candidate key, • C = x + 1 • Retrieve multiple records if search-key is not a candidate key, • C = x + s • Can be very expensive!. Each record may be on a different block , one block access for each retrieved record

32. Selections Using Indices • S6-2 (secondary index B+-tree, comparison) • For Av(r) use index to find 1st index entry = v and scan index sequentially from there, to find pointers to records. • For Av(r) just scan leaf pages of index finding pointers to records, till first entry  v • If ½ records are assumed to satisfy the condition, then ½ first-level index blocks are accessed, plus ½ the file records via the index • C = x + bI1/2 + r/2

33. Complex Selections: 12…n(r) • S7 (conjunctive selection using one index) • Select i and algorithms S1 through S6 that results in the least cost for i(r). • Test other conditions on tuple after fetching it into memory buffer. • Cost of the algorithms chosen.

34. Complex Selections: 12…n(r) • S8 (conjunctive selection using composite index). • If two or more attributes are involved in equality conditions in the conjunctive condition and a composite index (or hash structure) exists on the combined field, we can use the index directly. • Use appropriate composite index if available using one the algorithms S3 (primary index), S5, or S6 (B+-tree, equality).

35. Complex Selections: 12…n(r) • S9 (conjunctive selection by intersection of record pointers) • Requires indices with record pointers. • Use corresponding index for each condition, and take intersection of all the obtained sets of record pointers, then fetch records from file • If some conditions do not have appropriate indices, apply test in memory. • Cost is the sum of the costs of the individual index scan plus the cost of retrieving records from disk.

36. Complex Selections: 12… n(r) • S10 (disjunctive selection by union of identifiers) • Applicable if all conditions have available indices. • Otherwise use linear scan. • Use corresponding index for each condition, and take union of all the obtained sets of record pointers. Then fetch records from file • READ • “Examples of Cost Functions for Select” page 569--570.

37. Join Algorithms • Join (EQUIJOIN, NATURAL JOIN) • two–way join: a join on two files e.g. R A=B S • multi-way joins: joins involving more than two files. e.g., R A=B S C=D T • Examples • (OP6): EMP DNO=DNUMBER DEPT • (OP7): DEPT MGRSSN=SSN EMP • Factors affecting JOIN performance • Available buffer space • Join selection factor • Choice of inner vs outer relation

38. Join Algorithms • Join selectivity (js) : 0 js 1 • the ratio of the # of tuples of the resulting join file to the # of tuples of the Cartesian product file. • = |RA=BS| / |RS| = |RA=BS| / (rR * rS) • If A is a key of R, then • |RS| rS, so js 1/rR • If B is a key of S, then • |RS| rR, so js 1/rS

39. Join Algorithms • Having an estimate of js, • the estimate # of tuples of the resulting join file is |RS| = js * rR * rS • bfrRSis the blocking factor of the resulting join file • Cost of writing the resulting join file to disk (bRS) is • (js * rR * rS)/bfrRS

40. Join Algorithms • J1: Nested-loop join (brute force): • For each record t in R (outer loop), retrieve every record s from S (inner loop) and test whether the two records satisfy the join condition t[A] = s[B]. • J2: Single-loop join (Using an index): • If an index (or hash key) exists for one of the two join attributes — say, B of S — retrieve each record t in R, one at a time, and then use the index to retrieve directly all matching records s from S that satisfy s[B] = t[A].

41. Join Algorithms • J3 Sort-merge join: • If the records of R and S are physically sorted (ordered) by value of the join attributes A and B, respectively, we can implement the join in the most efficient way possible. • Both files are scanned in order of the join attributes, matching the records that have the same values for A and B. • In this method, the records of each file are scanned only once each for matching with the other file—unless both A and B are non-key attributes, in which case the method needs to be modified slightly.

42. Join Algorithms • J4 Hash-join: • The records of files R and S are both hashed to the same hash file, using the same hashing function on the join attributes A of R and B of S as hash keys. • A single pass through the file with fewer records (say, R) hashes its records to the hash file buckets. • A single pass through the other file (S) then hashes each of its records to the appropriate bucket, where the record is combined with all matching records from R.

43. Nested-Loop Join (J1) • To compute the theta join RS for each tuple tR in R do for each tuple tSin S do if tR,tS satisfy , add tR•tS to result • R is called the outerrelation and S the inner relation of the join. • Requires no indices and can be used with any kind of join condition. • Expensive since it examines every pair of tuples in the two relations.

44. Nested-Loop Join (Cont.) • In the worst case, if there is enough memory only to hold one block of each relation (3 in total), the estimated cost is • C = bR + (rR bS) + bRS • If the smaller relation fits entirely in memory • use that as the inner relation • reduces cost to bR+ bS+ bRS • If both relations fit entirely in memory • cost to bR+ bS+ bRS

45. Nested-Loop Join (Cont.) • Examples use the following information • customerdepositor • # of records of rcustomer=10000, rdepositor=5000 • # of blocks of bcustomer=400, bdepositor=100 • Assuming worst case, join cost estimate is • If depositor is used as outer relation • C = 5000  400 + 100 + bRS = 2,000,100 + bRS • If customer is used as the outer relation • C = 1000  100 + 400 + bRS = 1,000,400 + bRS • If smaller relation (depositor) fits entirely in memory, • C = 100 + 400 + bRS = 500 + bRS

46. Block Nested-Loop Join • Every block of inner relation is paired with every block of outer relation. for each block BR ofR do for each block BS ofS do for each tuple tRin BR do for each tuple tSin BSdo if tR,tSsatisfy , add tR•tSto result

47. Block Nested-Loop Join • In the worst case, if there is enough memory only to hold one block of each relation (3 in total), the estimated cost is • C = bR + (bR bS) + bRS • If both relations fit entirely in memory • C = bR+ bS+ bRS • If the smaller relation fits entirely in memory • use that as the inner relation • C = bR+ bS+ bRS • Choose smaller relation for outer loop, if neither of R and S fits in memory.

48. Block Nested-Loop Join • Cost can be reduced to bR/(nB-2)  bS + bR + bRS • use nB-2 blocks for outer relations • where nB = memory buffer size in blocks • use remaining two blocks for inner relation and output

49. Block Nested-Loop Join • Cost can be reduced • If equi-join or natural join attribute forms a key on the inner relation, stop inner loop on first match • Scan inner loopforward and backward alternately, to make use of the blocks remaining in buffer. • Use index on inner relation if available

50. Indexed Nested-Loop Join (J2) • Index lookups can replace file scans if • an index is available on the inner relation’s join attribute • Can construct an index just to compute a join. • For each tuple tRin the outer relation R, • use the index to retrieve tuples in S that satisfy the join condition with tuple tR • If indices are available on join attributes of both R and S, • use the relation with fewer tuples as the outer relation.