Query Processing and Query Optimization

1. Query Processing and Query Optimization CS 157B Dennis Le Weishan Wang

2. What are we going to cover • Processing Selection Queries • Processing Projection Queries and Eliminating Duplicates • Processing Join Queries • Query Plans and Query Optimization for Complex Relational Expression • Query Optimization and Database Analysis in Oracle8 • Summary

3. 1. How database processes queries

4. 1. Processing Selection Queries • A Query Plan is a particular strategy for reading the indexes and files to process a query

5. Query plan for query 1select * from Customer where accountId = 101

6. Query 5 selection based on a hash table indexselect * from Customerwhere zipcode = 32306

7. 2. Processing Projection Queries and Eliminating Duplicates • Suppose we use the following query to eliminate the duplicates select distinct lastName, firstName from Customer • The DBMS has two ways of doing this which are based on sorting and hashing • The sorting method sorts the projected records and writes the nonduplicates • The hashing method partitions the file into buckets then sorts within buckets to eliminate duplicates

8. 3. Processing Join Queries • Suppose we have following three tables: Customer, Rental and Movie • B: number of Blocks, R: number of rows • The following example shows the same cost of data blocks reads for both plans

9. Two plans have the same cost

10. Improvement - block nested loops join • Use the method readBlock() in the outer loop to read the outer table into an array • The inner loop reads the inner file(array) • Reduce the cost to Bc + Bc x Br

11. Improvement- indexed nested loops join • Reduce the reads by using index • If one of the attributes is indexed, we should take advantage of it • Use indexed read  readByAccountId() • Two plans have different costs because of the one-to-many relationship

12. Improvement- indexed nested loops join

13. Improvement- indexed nested loops join • Each rental has a customer but not every customer has a rental • Plan-1 (Rental inner)reads every customer and every rental once, but plan-2 (Rental outer)reads some customers many times and some customer no times • Which plan to use is depending on the size of the file(table). • Chose plan-1 if the customer file is smaller

14. Improvement- sort-merge join • Sort both tables and merge the result • The cost will depend on the cost of sorting(typically on the order of NlogN for N records)

15. Improvement- hash join • Create a hash table for each join table • The cost of the hash join depends on the size of the two hash tables(Hc and Hr) • The total cost is 2(Bc+Br+Hc+Hr)

16. 4. Query Plans and Query Optimization for Complex Relational Expression • Query Optimization Process: 1. Enumerate the query plans 2. Estimate the costs of the plans 3. Choose the best plan for the query

17. 4. Query Plans and Query Optimization for Complex Relational Expression

18. 4. Query Plans and Query Optimization for Complex Relational Expression

19. 4. Query Plans and Query Optimization for Complex Relational Expression

20. 4. Query Plans and Query Optimization for Complex Relational Expression Estimating costs (Query Optimizer) - number of records - size of records in each table - location and types of indexes - ordering of files - memory available for disk cache - etc…

21. 5. Query Optimization and Database Analysis in Oracle8 • Tables that keep information about the DataBase • Analyze Statement

22. 5. Query Optimization and Database Analysis in Oracle8

23. 6. Summary • Principles and Practices of Query Processing • Techniques in optimizing Query Processes • Process of Query Optimization • Commercial products implements these principles

24. 7. References “Principles of Database Systems with internet and JAVA applications,” by Greg Riccardi