# ch9-1 - Chapter 9 Optimization of Distributed Queries...

This preview shows pages 1–5. Sign up to view the full content.

Chapter 9 Optimization of Distributed Queries Chapter 9 Optimization of Distributed Queries The query resulting from decomposition and localization can be executed in many ways by choosing different data transfer paths. We need an optimizer to choose a strategy close to the optimal one. 91 INPUTS TO QUERY OPTIMIZER Objects to be optimized: time, space , etc. Let’s focus on time . 9.11 Cost Model Two cost models used to optimize a query to T calculate the total time of the query for increasing throughput, or i calculate the response time for reducing query response time A general formula of total cost of time Total-cost = C cpu * number-of-instructions + C I/O * number-of-I/O’s + C MSG * number-of-messages + C TR * number-of-bytes-to-transfer 1

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

View Full Document
Chapter 9 Optimization of Distributed Queries C cpu , C I/O , C MSG , and C TR are the unit cost for CPU time, I/O time, message passing, and data transfer. Components of the model may weight differently depending on the type of the network. LAN cares all. But WAN cares the last two. A general formula of response time Response-time = max{ cos ,..., cos } Total t Total t n - - 1 where Total t i - cos is the total cost of one thread of the parallel executed query. Example: cost of a query involving 3 sites by ignoring CPU and I/O times. site 1 x units of data site 3 y units of data site 2 Total-time = C MSG * 2 + C TR * (x+y) Response-time = max{(C MSG + C TR * x), (C MSG + C TR * y)} The two objectives should be compromised in 2
Chapter 9 Optimization of Distributed Queries practice 9.12 Database Statistics The information for base and intermediate relations as needed by query optimization. R: a relation A=(A 1 ,A 2 ,…,A n ): attributes of R {R 1 ,R 2 ,…,R r }: fragmentation of R length(A i ) : the length of A i ( in bytes) card R A j i ( ( )): π the number of distinct values of A i of R j min(A i ), max(A i ) : the maximum and minimum possible values of A i card(dom[A i ]) : the number of unique values in the domain of Ai card(R j ) : the number of tuples of fragment R j The join selectivity factor for R and S: a real between 0 and 1 SF R S card R S card R card S J ( , ) ( ) ( ) ( ) = × 3

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

View Full Document
Chapter 9 Optimization of Distributed Queries size(R ) = card( R ) * length( R) 9.13 Cardinalities of Intermediate Results Slection card R SF F card R SF A value card R SF A value A value A A SF A value value A A A SF P Ai P Aj SF P Ai SF P Aj SF P Ai P Aj SF P Ai F S S A S S S S S S S ( ( )) ( ) * ( ) ( ) ( ( )) ( ) max(
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### Page1 / 16

ch9-1 - Chapter 9 Optimization of Distributed Queries...

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

View Full Document
Ask a homework question - tutors are online