Slideshow:

Heuristic-based logical query plan optimization - Example 1

Note: you cannot push the selection σ_{name=starName} further down....

Heuristic-based logical query plan optimization - Example 1

Optimal logical query plan !

Heuristic-based logical query plan optimization - Example 2

(1) Break up the select condition and move selection condition(s) down as far as possible:

Heuristic-based logical query plan optimization - Example 2

(2) Replace σ + × with ⋈:

Heuristic-based logical query plan optimization - Example 2

(3) Remove the unused attributes by inserting projection (π):

Comment on my lecture notes...

❮ ❯

Logical query plan optimization example 1

Query:

For each (movie) year, find the latest birthday (= youngest movie star) for that (movie) year

SQL command:

SELECT movieYear, max( birthday ) FROM movieStar, StarsIn WHERE name = starName GROUP BY movieYear

Find optimal logical query plan with heuristics:

The initial logical query plan is as follows:
Apply: σ_{name=startName}( MovieStar × StartsIn ) = MovieStar ⋈_{name=startName} StartsIn
Add harmless projections to remove unused attributes:

Logical query plan optimization example 2

Example:

SQL query:

SELECT lname FROM employee, works_on, project WHERE pname = 'Aquarius' and pnumber = pno and essn = ssn and bdate > '1957-12-31'

Note:

In my lecture notes, I some times will omit the projection (π) step from the optimization result to save on "clarity"
(The picture gets very crowded with projection (π) operators)