(数据库原理课件）Chapter12 Query Optimization教材课程.pptVIP

* * * * * * * * * * * * * * * * * * * * * * * Additional Optimization Techniques Top-K Queries Top-K queries select * from r, swhere r.B = s.Border by r.A ascendinglimit 10 Alternative 1: Indexed nested loops join with r as outer Alternative 2: estimate highest r.A value in result and add selection (and r.A = H) to where clause If 10 results, retry with larger H Optimization of Updates Halloween problem update R set A = 5 * A where A 10 If index on A is used to find tuples satisfying A 10, and tuples updated immediately, same tuple may be found (and updated) multiple times Solution 1: Always defer updates collect the updates (old and new values of tuples) and update relation and indices in second pass Drawback: extra overhead even if e.g. update is only on R.B, not on attributes in selection condition Solution 2: Defer only if required Perform immediate update if update does not affect attributes in where clause, and deferred updates otherwise. Join Minimization Join minimization select r.A, r.B from r, swhere r.B = s.B Check if join with s is redundant, drop it E.g. join condition is on foreign key from r to s, r.B is declared as not null, and no selection on s Other sufficient conditions possible select r.A, s2.B  from r, s as s1, s as s2 where r.B=s1.B and r.B = s2.B and s1.A 20 and s2.A 10 join with s1 is redundant and can be dropped (along with selection on s1) Lots of research in this area since 70s/80s! Multiquery Optimization Example Q1: select * from (r natural join t) natural join s Q2: select * from (r natural join u) natural join s Both queries share common subexpression (r natural join s) May be useful to compute (r natural join s) once and use it in both queries But this may be more expensive in some situations e.g. (r natural join s) may be expensive, plans as shown in queries may be cheaper Multiquery optimization: find best overall plan for a set of queries, expoiting sharing of common subexpressions between qu