ch15-Algorithms for Query Processing and Optimization

ch15-Algorithms for Query Processing and Optimization -...

Info iconThis preview shows pages 1–11. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Slide 15- 1 Copyright 2007 Ramez Elmasri and Shamkant B. Navathe Copyright 2007 Ramez Elmasri and Shamkant B. Navathe Chapter 15 Algorithms for Query Processing and Optimization Slide 15- 3 Copyright 2007 Ramez Elmasri and Shamkant B. Navathe Chapter Outline (1) 0. Introduction to Query Processing 1. Translating SQL Queries into Relational Algebra 2. Algorithms for External Sorting 3. Algorithms for SELECT and JOIN Operations 4. Algorithms for PROJECT and SET Operations 5. Implementing Aggregate Operations and Outer Joins 6. Combining Operations using Pipelining 7. Using Heuristics in Query Optimization 8. Using Selectivity and Cost Estimates in Query Optimization 9. Overview of Query Optimization in Oracle 10. Semantic Query Optimization Slide 15- 4 Copyright 2007 Ramez Elmasri and Shamkant B. Navathe 0. Introduction to Query Processing (1) Query optimization : The process of choosing a suitable execution strategy for processing a query. Two internal representations of a query: Query Tree Query Graph Slide 15- 5 Copyright 2007 Ramez Elmasri and Shamkant B. Navathe Introduction to Query Processing (2) Slide 15- 6 Copyright 2007 Ramez Elmasri and Shamkant B. Navathe 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. Slide 15- 7 Copyright 2007 Ramez Elmasri and Shamkant B. Navathe 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 15- 8 Copyright 2007 Ramez Elmasri and Shamkant B. Navathe 2. Algorithms for External Sorting (1) 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. 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: n R = (b/n B ) Merging phase: d M = Min (n B-1, n R ); n P = (log dM (n R )) n R : number of initial runs; b: number of file blocks; n B : available buffer space; d M : degree of merging; n P : number of passes. Slide 15- 9 Copyright 2007 Ramez Elmasri and Shamkant B. Navathe Algorithms for External Sorting (2) Slide 15- 10 Copyright 2007 Ramez Elmasri and Shamkant B. Navathe 3. Algorithms for SELECT and JOIN 3....
View Full Document

This note was uploaded on 02/23/2012 for the course CS 348 taught by Professor Staff during the Fall '08 term at Purdue University-West Lafayette.

Page1 / 65

ch15-Algorithms for Query Processing and Optimization -...

This preview shows document pages 1 - 11. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online