Database Administration Query Processing 1 Query Processing SELECT

Database Administration Query Processing 1

Query Processing SELECT A. Name, B. Grade FROM A, B WHERE A. Id = B. Id Name, Grade ( Id=Id(A B)) How to evaluate this query efficiently? What algorithms and access path to use? 2

Query Processing in Oracle You can view the query execution plan used in Oracle and other DBMSs 3

Explain in My. SQL Simple means no union or subqueries All means do a full scan of the table Expected # rows to examine Filter rows using where clause sorting is needed for order by 4

Explain in My. SQL Matching party_index to const to fetch the rows Expected # rows to examine decreases 5

Explain in My. SQL Matches at most 1 row Use primary key to retrieve the row 6

Explain in My. SQL SELECT in Outer Query Inner subquery Use primary index to fetch row with id = 300 7

Sorting l Sorting is used to implement many relational operations (e. g. , join, project, intersect, …) – Ordering is specifically requested by users: SELECT … ORDER BY … – Eliminate duplicate tuples: SELECT DISTINCT … – To do sort-merge join (which is used for JOIN, UNION, and INTERSECTION operations) l Problems – Relations are typically large, do not fit in main memory – So we cannot use traditional in-memory sorting algorithms (such as quicksort) 8

External Sorting l Combines in-memory sorting with techniques for minimizing I/O l Cost of sorting is often measured in terms of number of block transfers – cost of in-memory sorting << I/O cost of block transfers l 2 -phase in external sorting: – Partial sorting phase – Merging phase 9

External Sorting l l nb = number of input buffer space in memory b = number of disk blocks for the file to be sorted Example: nb = 2, b = 7 5 3 2 run 6 1 10 15 7 2 6 5 3 2 input buffers in main memory 20 11 8 4 7 5 Unsorted file on external storage (disk) 10

Partial Sorting l Partial Sorting Phase: – Fetch a segment of the unsorted file from disk into buffers – Sort the data in buffers (e. g. , using quicksort) – Write the sorted file segment back to disk l Example: nb=2, b=7 – Number of runs = 7/2 = 4 5 3 2 6 1 10 15 Unsorted data file 7 20 11 8 4 7 5 Partially sorted file 2 3 Run 1 5 6 1 7 Run 2 10 15 4 8 Run 3 11 20 5 7 Run 4 11

Merging l Merging Phase: – Merge all runs using k input buffers and 1 output buffer – If number of runs > k (k: degree of merging) u Divide runs into groups of size k and merge each group into a run u Repeat until all runs are merged into 1 group k-way merging 12

Merging Example Partially sorted Input Output 2 -way merging 2 3 5 6 52 63 10631 1 7 10 15 101 Input buffers 752 15 7 15 1 2 3 5 6 7 10 15 2 runs (of size 2) are merged into 1 run (of size 4) Output buffer 13

Example of External Sorting l nb = 4, b = 10, k=3 (3 -way merging): After partial sorting After 3 -way merging 14

Example of External Sorting l nb = 4, b = 10, k=2 (2 -way merging): After partial sorting After 2 -way merging 15

Cost of External Sorting l Suppose we have b = 1024 blocks and n. B = 5 input buffers l Partial sorting: – number of runs, n. R = 1024/5 = 205 l Merging: assume degree of merging, k = 5 – – l After pass 1: number of runs remaining = 205/5 = 41 After pass 2: number of runs remaining = 41/5 = 9 After pass 3: number of runs remaining = 9/5 = 2 After pass 4: number of runs remaining = 2/5 = 1 Number of merging passes needed = logk n. R = logk b/n. B 16

Cost of External Sorting l Partial sorting : – Cost = 2 b (each disk block is read once and written back once) l Cost of merge phase (k is degree of merging) – b/n. B runs to be merged – Number of passes needed = log k (b/n. B) u Each pass requires 2 b page transfers – Cost of merging = 2 b log k(b/n. B) = 2 b log k (n. R) l Overall cost of external sorting = cost of partial sorting + cost of merging = 2 b + 2 b log k (n. R) 17

Query Processing SELECT Attrib. List FROM relations R 1, …, Rk WHERE condition Translated to: Attrib. List ( condition (R 1 R 2 … Rk)) Example: SELECT Dno FROM Employee WHERE SSN = ‘ 1234567890’ Dno ( SSN=‘ 1234567890’ (Employee)) 18

Algorithms for Query Processing l Relational Algebra operators – – – SELECT JOIN PROJECT SET (UNION, INTERSECT, SET DIFFERENCE) AGGREGATE 19

Algorithms for SELECT Operations EMPLOYEE(SSN, Fname, Minit, Lname, Sex, Address, Salary, Dno) DEPARTMENT(Dno, Dname, Mgrssn, Mgr. Start. Date) WORKS_ON(ESSN, Pno, Hours) l Examples of SELECT operations: – Simple selection u u u (OP 1): SSN='123456789' (EMPLOYEE) (OP 2): DNUMBER>5(DEPARTMENT) (OP 3): DNO=5(EMPLOYEE) – Complex selection: u u (OP 4): DNO=5 AND SALARY>30000 AND SEX=F(EMPLOYEE) (OP 5): ESSN=123456789 AND PNO=10(WORKS_ON) 20

Algorithms for SELECT Operation l Algorithms for Simple Selection: – Linear search (brute force, full tablescan): u Retrieve every record in the file, and test whether its attribute values satisfy the selection condition – Binary search u If the selection condition involves an equality comparison on a key attribute on which the file is ordered, binary search can be used – Use primary/clustering/secondary index u Use the index to find the record(s) satisfying the corresponding selection condition 21

Algorithms for SELECT Operation l Algorithms for Complex Selection: DNO=5 AND SALARY>30000 AND SEX=F(EMPLOYEE) – Linear search (brute-force; full tablescan) – Use an individual index u E. g. : use the Dno index to retrieve the records and then check whether each retrieved record satisfies the remaining conditions – Use a composite index u E. g. : use the (Dno, Sex) composite index to retrieve the records and check whether they satisfy the remaining condition – Intersection of record pointers: u This method is possible if secondary indexes are available on all (or some of) the fields involved u E. g. : intersect the record pointers returned by the indexes for Dno, Salary, and Sex 22

Algorithms for JOIN Operations R A=B S l The cost of joining two relations makes the choice of a join algorithm crucial l Examples – EMPLOYEE – DEPARTMENT DNO=DNUMBER DEPARTMENT MGRSSN=SSN EMPLOYEE 23

Computing Joins R l A=B S Suppose b. R and b. S are the number of blocks in R and S, r. R and r. S are the number of tuples in r and s r. R rows b. R blocks R r. S rows b. S blocks S 24

Algorithms for JOIN Operations l J 1 Nested-loop join (brute force) R R. A=S. B S foreach tuple t R do foreach tuple t’ S do if t. A = t’. B then output (t, t’) l Cost = b. R + r. R b. S + cost of writing the final result – Very expensive – Order of the loop matters 25

Algorithms for JOIN Operations l J 1(b) Nested-block join – Instead of joining 1 tuple at a time, join one block at a time R R 1 R 2 R 1 S l S 1 S 2 S 3 S 4 S 1 S 4 S 3 S 2 R 2 Join R 1 & R 2 with blocks in S Memory buffer Output Buffer Cost = b. R + b. R/(n. B – 2) b. S + cost of writing the result – n. B is the number of buffers available – Number of blocks (b. R) << Number of records (r. R) 26

Algorithms for JOIN Operations l J 2 Single-loop join (Using an access structure) R A=B S foreach tuple t in R do { use index to find all tuples t’ in S satisfying t. A = t’. B; output (t. t’) } u Cost = b. R + r. R cost of search + cost of writing output 27

Algorithms for JOIN Operations l J 3 Sort-merge join R R, A=S. B S sort R on attribute A; sort S on attribute B; while !eof(R) and !eof(S) do { Scan R and S concurrently until t. A = t’. B = c; Output A=c(R) B=c (S) A=c(R) } R S B=c (S) 28

Sort-Merge Join R D A 1 3 p p 0 9 q q 8 7 3 s s s 5 7 u u 1 1 v v 1 p p 4 B E S p p 4 0 r 9 s 7 t t 2 5 u u u 2 5 0 3 p p 0 1 p p 0 3 p p 4 8 s s 7 7 3 s s 7 7 5 u u 2 7 u u 2 A=B S 5 u u 5 7 u u 5 x 0 R 29 5 u u 0 7 u u 0

Algorithms for JOIN Operations l J 4 Hash-join: – Use the same hashing function on the join attributes A of R and B of S as hash keys – Hash the file with fewer records (say, R) to the hash file buckets. – Hash the other file (S) to the appropriate bucket, where the record is combined with all matching records from R. 30

Example l EMPLOYEE l Meta-data: DNO=DNUMBER DEPARTMENT – DEPARTMENT: u Number of records, r. D = 50 u Number of disk blocks to store records, b. D = 10 – EMPLOYEE u Number of records, r. E = 6000 u Number of disk blocks to store records, b. E = 2000 – Number of buffers available in memory, n. B = 7 u Size of each buffer is the same as size of each block on disk 31

Example: Nested Loop Join foreach tuple t R do foreach tuple t’ S do if t. A = t’. B then output (t, t’) l Simple nested loop join – Cost = b. R + r. R bs + cost of output l Nested-block join – Suppose there are n. B buffers u Use 1 buffer for output, 1 buffer for relation S, n. B – 2 for relation R – Cost = b. R + b. R/(n. B – 2) bs + cost of output 32

Example: Block Nested Loop Join l EMPLOYEE l If EMPLOYEE is outer loop DNO=DNUMBER DEPARTMENT – Cost = 2000 + 2000/5 10 + cost of output = 6000 l If DEPARTMENT is outer loop – Cost = 10 + 10/5 2000 + cost of output = 4010 l l More efficient to use DEPARTMENT as the outer loop Order of the tables in the nested loop matters! 33

My. SQL Example 34

My. SQL Example department table chosen for outer loop of nested block join employee table chosen for inner loop of nested block join 35

My. SQL Example straight_join forces My. SQL to process the join in the order given (employee for outer loop and department for inner loop) 36

Example: Single Loop Join l l DEPARTMENT MGRSSN=SSN EMPLOYEE Suppose there are multi-level indexes on – SSN (for EMPLOYEE) : number of index levels, XSSN = 4 – MGR_SSN (for DEPARTMENT): number of index levels, XMGR_SSN = 2 foreach tuple t in R do use index to find all tuples t’ in S satisfying t. A = t’. B; l Use EMPLOYEE as outer loop – Max Cost = b. E + r. E (x. MGR_SSN + 1) + cost of output = 2000 + 6000 3 + cost of output = 20000 + cost of output l Use DEPARTMENT as outer loop – Max Cost = b. D + r. D (x. SSN + 1) + cost of output = 10 + 50 5 + cost of output = 260 + cost of output 37

My. SQL Example Scan all the rows in department (outer loop of single loop join) For each mgr. Id value, use the primary key index in employee table to fetch its corresponding row 38

Explaining EXPLAIN in My. SQL l type – determines how table is accessed (most frequent) – “ALL” - full table scan – “eq_ref” - reference by primary or unique key (1 row) – “ref” - reference by non-unique key (multiple rows) l l l possible_keys - indexes My. SQL could use for this table key – index My. SQL sellected to use “ref” - The column or constant this key is matched against “rows” - How many rows will be looked up in this table “extra” - Extra Information – “Using Filesort” - external sort is used – “Using where” - where clause will be resolved 39

Algorithms for PROJECT Operations l Algorithm for PROJECT operations <attribute list>(R) 1. If <attribute list> has a key of relation R, extract all tuples from R with only the values for the attributes in <attribute list>. 1. If <attribute list> does NOT include a key of relation R, duplicated tuples are removed from the results. u Methods to remove duplicate tuples – Sorting – Hashing 40

Algorithms for SET Operations l Set operations: – UNION, INTERSECTION, SET DIFFERENCE and CARTESIAN PRODUCT l CARTESIAN PRODUCT of relations R and S – Includes all possible combinations of records from R and S. – The attributes of the result include all attributes of R and S. – CARTESIAN PRODUCT operation is very expensive and should be avoided if possible. u If R has n records and j attributes and S has m records and k attributes, the result relation will have n*m records and j+k attributes. 41

Algorithms for SET Operations l UNION (See Figure 15. 3 c) – Sort the two relations on the same attributes. – Scan and merge both sorted files concurrently, whenever the same tuple exists in both relations, only one is kept in the merged results. l INTERSECTION (See Figure 15. 3 d) – Sort the two relations on the same attributes. – Scan and merge both sorted files concurrently, keep in the merged results only those tuples that appear in both relations. l SET DIFFERENCE R-S (See Figure 15. 3 e) – Sort the two relations on the same attributes. – Scan and merge both sorted files concurrently, keep in the merged results only those tuples that appear in relation R but not in relation S. 42

Implementing Aggregate Operations l Aggregate operators: – MIN, MAX, SUM, COUNT and AVG l Options to implement aggregate operators: – Table Scan – Use Index – Example: SELECT MAX (SALARY) FROM EMPLOYEE; – If an (ascending) index on SALARY exists, then the optimizer could traverse the index for the largest value, which would entail following the right most pointer in each index node from the root to a leaf. 43