15_Optimization_of_Distributed_Queries

15_Optimization_of_Distributed_Queries - Distributed...

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

View Full Document Right Arrow Icon
Distributed Database Systems Optimization of Distributed Queries (Ozsu Chap. 9.1 & 9.2) 605.741 David Silberberg
Background image of page 1

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

View Full DocumentRight Arrow Icon
D. Silberberg Distributed Database Systems Optimization of Distributed Queries 2 What We Have Learned So Far • We learned how to create queries onto distributed relations • Then, we mapped them onto relation fragments • Used techniques called decomposition and data localization • Optimized sub-expressions • Eliminated useless expressions • However, there are many ways to order and execute the query • Query optimizer tries to find the best ordering and execution strategy
Background image of page 2
D. Silberberg Distributed Database Systems Optimization of Distributed Queries 3 Complexity of Ordering and Execution Strategy • However, finding the best ordering is NP-hard with respect to the number of relations • Cannot seriously find the best ordering in real time • The purpose of the optimizer is to find good ordering in a reasonable amount of time • We use strategies to approximate the optimal performance • This is called optimal strategy • The result of an optimizer is a query execution plan
Background image of page 3

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

View Full DocumentRight Arrow Icon
D. Silberberg Distributed Database Systems Optimization of Distributed Queries 4 Execution Plan - Components • Cannot really know what the execution time will be • Must approximate it a priori –C o s t s – Disk I/O – CPU time – Communication time • Used to be that communication costs were dominant • Now, I/O and communication costs are on the same order of magnitude • One of the main issues: join ordering – This is what we focus on!
Background image of page 4
D. Silberberg Distributed Database Systems Optimization of Distributed Queries 5 Query Optimization - First Steps Much is independent of centralized versus distributed Since the input is just a bunch of relational algebra statements, the tables can be whole or fragmented tables We formulate an objective function that characterizes cost of query Ultimately, we want to minimize this cost We take in a query (in relational algebra) and we produce a query execution plan (QEP) based on the minimal cost
Background image of page 5

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

View Full DocumentRight Arrow Icon
D. Silberberg Distributed Database Systems Optimization of Distributed Queries 6 Query Optimization - 3 Components • Search space • Search strategy • Cost model Relational query Generate Search Space Rules QEP set Search Strategy Best QEP Cost model Environment Details contained in these
Background image of page 6
D. Silberberg Distributed Database Systems Optimization of Distributed Queries 7 Search Space • Basically, equivalent operator trees that solve the same query • Join trees are of the most interest because of their costs to evaluate – Joins – Cartesian products • These have the most effect on the performance of queries • Example: SELECT ename, resp FROM EMP, ASG, PROJ WHERE EMP.ename = ASG.ename AND ASG.pno = PROJ.pno
Background image of page 7

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

View Full DocumentRight Arrow Icon
D. Silberberg Distributed Database Systems Optimization of Distributed Queries 8 Search Space (continued) • Corresponding Trees • With more tables, the number of options is high: –O (N ! ) ~ n n , where N = # tables
Background image of page 8
Image of page 9
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 39

15_Optimization_of_Distributed_Queries - Distributed...

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

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