Fundamentals of Database Systems - Algorithms for Query Processing and Optimization

Fundamentals of Database Systems - Algorithms for Query Processing and Optimization - Transcript

Copyright 2004 Pearson Education Inc

Chapter 15
Algorithms for Query Processing and Optimization

Copyright 2004 Pearson Education Inc

Translating SQL Queries into Relational Algebra

Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc

Slide 15 3

Algorithms for External Sorting

Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc

Slide 15 4

Algorithms for SELECT and JOIN Operations
Implementing the SELECT Operation Implementing the JOIN Operation

Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc

Slide 15 5

Algorithms for PROJECT and SET Operations

Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc

Slide 15 6

Implementing Aggregate Operations and Outer Joins
Implementing Aggregate Operations Implementing Outer Join

Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc

Slide 15 7

Combining Operations Using Pipelining

Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc

Slide 15 8

Using Heuristics in Query Optimization
Notation for Query Trees and Query Graphs Heuristic Optimization of Query Trees Converting Query Trees into Query Execution Plans

Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc

Slide 15 9

Using Selectivity and Cost Estimates in Query Optimization
Cost Components for Query Execution Catalog Information Used in Cost Functions Examples of Cost Functions for SELECT Examples of Cost Functions for JOIN Multiple Relation Queries and Join Ordering Examples to Illustrate Cost Based Query Optimization
Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc

Slide 15 10

Overview of Query Optimization in Oracle

Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc

Slide 15 11

Semantic Query Optimization

Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc

Slide 15 12

Summary

Elmasri and Navathe Fundamentals of Database Systems Fourth Edition Copyright 2004 Pearson Education Inc

Slide 15 13