Query Optimization

1. Query Optimization Dr. Karen C. Davis Professor School of Electronic and Computing Systems School of Computing Sciences and Informatics

3. Outline • overview of relational query optimization • logical optimization • algebraic equivalences • transformation of trees • physical optimization • selection algorithms • join algorithms • cost-based optimization • research example using relational algebra

4. Relational Query Optimization relational algebra query tree access plan (executable) SQL query logical physical query optimizer

5. Learning Outcomes • translate basic SQL to RA query tree • perform heuristic optimizations to tree • use cost-based optimization to select algorithms for tree operators to generate an execution plan

6. SQL is declarative • describes what data, not how to retrieve it select distinct … from … where … • helpful for users, not necessarily good for efficient execution

7. Relational Algebra is procedural • specifies operators and the order of evaluation • steps for query evaluation: • translate SQL to RA operators (query tree) • perform heuristic optimizations: • push RA select operators down the tree • convert select and cross product to join • others based on algebraic transformations

8. Relational Algebra Operators

9. SQL to RA four relations ↓ select distinct … πl from … x where … σc πl | σc | X / \ X S / \ XT / \ R U πl | σc | X / \ X S / \ R T πl | σc | X / \ R S  two relations three relations 

10. SQL to RA Tree Example select A.x, A.y, B.z from A, B where A.a = B.z and A.x > 10 πA.x, A.y, B.z | σA.a = B.z and A.z > 10 | X / \ A B evaluated bottom-up left to right; intermediate values are passed up the tree to the next operator

11. SQL to RA Tree Example πlname | σpname = ‘Aquarius’ and pnumber = pno and essn = ssn and bdate = ‘1985-12-03’ | X / \ X projects / \ employee works_on select lname from employee, works_on, projects where pname = ‘Aquarius’ and pnumber = pno and essn = ssn and bdate = ‘1985-12-03’

12. Simple Heuristic Optimization πl | σc1 | σc2 | σc3 | X / \ R S • cascade selects (split them up) πl | σc1and c2and c3 | X / \ R S

13. πl | σc1 | σc2 | σc3 | X / \ R S πl | σc2 | X / \ σc1 σc3 | | R S Push any single attribute selects down the tree to be just above their relation

14. efficient join algorithms πl | σc2 | X / \ σc1 σc3 | | R S πl | ⋈c2 / \ σc1 σc3 | | R S smaller intermediate results Convert 2-attribute select and cross product to join

15. Practice problem: optimize RA tree select P.pnumber, P.dnum, E.lname, E.bdate from projects P, department D, employee E where D.dnumber = P.dnum and // c1 D.mgrssn = E.ssn and // c2 P.plocation = ‘Stafford’; // c3

16. RA tree to RA expression πl | ⋈c2 / \ σc1 σc3 | | R S σc1R σc3S ) πl( ⋈c2

17. Other Operators in Relational Algebra SQL: (select pnumber from projects, department, employee where dnum = dnumber and mgrssn = ssn and lname = 'Smith‘) union (select pnumber from projects, works_on, employee where pnumber = pno and essn = ssn and lname = 'Smith'); RA: π pnumber (σlname = ‘Smith’ employee ⋈ssn=mgrssn department ⋈ dnumber = dnum projects) ⋃ π pnumber (σlname = ‘Smith’ employee ⋈ssn=essn works_on ⋈ pnumber = pno projects)

18. Selection Algorithms • linear search • binary search • primary index or hash for point query • primary index for range query • clustering index • secondary index • conjunctives • individual index • composite index or hash • intersection of record pointers for multiple indexes

19. Join Algorithms • nested loop join • single-scan join • sort-merge join • hash join example execution plan sort-merge using indexes http://docs.oracle.com/cd/E13085_01/doc/timesten.1121/e14261/query.htm

20. Multiple View Processing Plan (MVPP) view chromosome: 101100010100001 index chromosome: 1100110 Fitness: sum of query processing costs of individual queries using the views and indexes selected Q2 Q3 Q1 πO.orderkey, O.shippriority (v9) πC.custkey, C.name, C.acctbal, N.name, C.address, C.phone (v12) πP.type, L.extendedprice (v15) σ C.mktsegment = “building” and L.shipdate = “1995-03-15” (v8) σ O.orderdate = “1994-10-01” (v11) σ L.shipdate = “1995-09-01” (v14) ⋈nationkey (v10) ⋈orderkey (v7) ⋈custkey (v6) ⋈partkey (v13) πname, address, phone, acctbal, nationkey, custkey, mktsegment (v1) πorderkey, orderdate, custkey, shippriority (v2) πpartkey, orderkey, shipdate, extendedprice (v3) πnationkey, name (v4) πpartkey, type(v5) Customer (C) Orders (O) Lineitem (L) Nation (N) Part (P) thesis defense of Sirisha Machiraju: Space Allocation for Materialized Views and Indexes Using Genetic Algorithms, June 2002