{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

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
Slide 15- 1 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe
Background image of page 1

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

View Full Document Right Arrow Icon
Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Chapter 15 Algorithms for Query Processing and Optimization
Background image of page 2
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
Background image of page 3

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

View Full Document Right Arrow Icon
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
Background image of page 4
Slide 15- 5 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Introduction to Query Processing (2)
Background image of page 5

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

View Full Document Right Arrow Icon
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.
Background image of page 6
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))
Background image of page 7

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

View Full Document Right Arrow Icon
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.
Background image of page 8
Slide 15- 9 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Algorithms for External Sorting (2)
Background image of page 9

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

View Full Document Right Arrow Icon
Slide 15- 10 Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe 3. Algorithms for SELECT and JOIN Operations (1) Implementing the SELECT Operation Examples: (OP1): σ SSN='123456789' (EMPLOYEE) (OP2): σ DNUMBER>5 (DEPARTMENT) (OP3): σ DNO=5 (EMPLOYEE) (OP4): σ DNO=5 AND SALARY>30000 AND SEX=F (EMPLOYEE) (OP5): σ ESSN=123456789 AND PNO=10 (WORKS_ON)
Background image of page 10
Image of page 11
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}