Database System: Chapter 10 - Query Processing and Optimization Introduction to Query Processing, Translating SQL Queries into Relational Algebra, Translating SQL Queries into Relational Algebra, Using Selectivity and Cost Estimates in Query Optimization, Overview of Query Optimization in Oracle. | Chapter 10 Query Processing and Optimization Copyright © 2004 Pearson Education, Inc. 5/14/2020 4:10:15 AM Outline Introduction to Query Processing Translating SQL Queries into Relational Algebra Using Heuristics in Query Optimization Using Selectivity and Cost Estimates in Query Optimization Overview of Query Optimization in Oracle Slide 10 - Query optimization: the process of choosing a suitable execution strategy for processing a query. Two internal representations of a query Query Tree Query Graph Introduction to Query Processing (1) Slide 10 - Introduction to Query Processing (2) Slide 10 - 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 . | Chapter 10 Query Processing and Optimization Copyright © 2004 Pearson Education, Inc. 5/14/2020 5:23:42 AM Outline Introduction to Query Processing Translating SQL Queries into Relational Algebra Using Heuristics in Query Optimization Using Selectivity and Cost Estimates in Query Optimization Overview of Query Optimization in Oracle Slide 10 - Query optimization: the process of choosing a suitable execution strategy for processing a query. Two internal representations of a query Query Tree Query Graph Introduction to Query Processing (1) Slide 10 - Introduction to Query Processing (2) Slide 10 - 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. Slide 10 - Translating SQL Queries into Relational Algebra (2) SELECT LNAME, FNAME FROM EMPLOYEE WHERE SALARY > ( SELECT MAX (SALARY) FROM EMPLOYEE WHERE DNO = 5); SELECT MAX (SALARY) FROM EMPLOYEE WHERE DNO = 5 SELECT LNAME, FNAME FROM EMPLOYEE WHERE SALARY > C πLNAME, FNAME (σSALARY>C(EMPLOYEE)) ℱMAX SALARY (σDNO=5 (EMPLOYEE)) Slide 10 - Using Heuristics in Query Optimization(1) Process for heuristics optimization The parser of a high-level query generates an initial internal representation; Apply heuristics rules to optimize the internal representation. A query execution plan is generated to execute groups of operations based on the access paths available on the files involved in the query. The main heuristic is to apply first the operations that reduce the size of intermediate results. ., Apply SELECT and PROJECT operations before applying the JOIN or other binary operations. Slide 10 - Using Heuristics in Query Optimization .
