In terms of a query tree it means that the σ poperation is push as far down the

# In terms of a query tree it means that the σ

• Notes
• 106

This preview shows page 24 - 33 out of 106 pages.

In terms of a query tree , it means that the σ p operation is push as far down the logical query tree as possible Example π movieTitle σ birthdate LIKE %1960 1 starName=name StarsIn MovieStar Push DOWN π movieTitle 1 starName=name σ birthdate LIKE %1960 StarsIn MovieStar 24 / 106
Note: “push select down” query optimization technique When a query contains a virtual table, then the σ p operation is pushed down the logical query tree as far as possible is not sufficient Sometimes it is useful to push selection the other way, i.e., up in the tree, using the law σ p (R 1 S) = R 1 σ p (S) backwards Example Relations: StarsIn (title, year, starName, birthday) // Movie stars Movies (title, year, gender, studioName) // Movies View: CREATE VIEW Movies96 AS { SELECT * FROM Movies WHERE year = 1996 } Corresponding logical query plan σ year=1996 Movies 25 / 106
Note: “push select down” query optimization technique Example (Continue) Query: Find all movie stars and their studio name in movies of 1996 SELECT starName , studioName FROM Movies96 , StarsIn WHERE Movies96.title = StarsIn.title initial logical query plan π starName,studioName 1 StarsIn Movies96 26 / 106
Note: “push select down” query optimization technique Example (Continue) After replacing the virtual table with the corresponding query: π starName,studioName 1 StarsIn σ year =1996 Movies However, the optimal query plan is as follows: π starName,studioName 1 σ year =1996 StarsIn σ year =1996 Movies 27 / 106
Amendment to the simple query optimization technique If there are virtual table in the query plan, then to find the optimal query plan, we must Push any selection σ operators in the virtual table as far up the query tree as possible Push every selection σ operators in the resulting query tree as far down the query tree as possible Example Query plan after incorporating the virtual table query: π starName,studioName 1 StarsIn σ year =1996 Movies 28 / 106
Amendment to the simple query optimization technique Example (Continue) Use this algebraic law in the reverse order: σ p (R 1 S) = σ p (R) 1 S to push the σ year =1996 operation up the tree π starName,studioName 1 StarsIn σ year =1996 Movies π starName,studioName σ year =1996 1 StarsIn Movies 29 / 106
Amendment to the simple query optimization technique Example (Continue) Both relations have the attribute year Use this algebraic law in the forward order : σ p (R 1 S) = σ p (R) 1 σ p (S) to push the σ year =1996 operation down the tree π starName,studioName σ year =1996 1 StarsIn Movies Push DOWN π starName,studioName 1 σ year =1996 StarsIn σ year =1996 Movies 30 / 106
Algebraic Laws: Project The projection operator π can remove unnecessary attributes from intermediate results Projections can be pushed down through many operators: The projection operator π may be introduced anywhere as long as it does not remove any attributes used above in the tree The projection operator is thus often not moved, we introduce a new Adding a projection lower in the tree can improve performance, since often tuple size is reduced Example R(a,b,c), S(x,y,z) π b,y 1 a = x R S π b,y 1 a = x π a,b π x,y S R 31 / 106
Algebraic Laws: Project If a query contains a sequence of project operations, only the final operation is needed, the others can be omitted.

#### You've reached the end of your free preview.

Want to read all 106 pages?

• Fall '19
• Joseph Rosen
• Relational model, StarsIn, R 1c S

### What students are saying

• As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

Kiran Temple University Fox School of Business ‘17, Course Hero Intern

• I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

Dana University of Pennsylvania ‘17, Course Hero Intern

• The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

Jill Tulane University ‘16, Course Hero Intern