1 / 32

Constructing Search Space for Materialized View Selection

Constructing Search Space for Materialized View Selection. Dimiti Theodoratos Wugang Xu New Jersey Institute of Technology. Problem (1). Many problems in Databases require the selection of views to materialize. A general form of these problems is the following:

zelda
Download Presentation

Constructing Search Space for Materialized View Selection

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Constructing Search Space for Materialized View Selection Dimiti Theodoratos Wugang Xu New Jersey Institute of Technology DOLAP'04 - Washington DC

  2. Problem (1) • Many problems in Databases require the selection of views to materialize. • A general form of these problems is the following: • Given a set of queries, select a set of views to materialize such that a cost function is optimized and a number of constraints is satisfied. DOLAP'04 - Washington DC

  3. Problem (2) • Examples of view selection problems in DWing. • Given a set of queries to be satisfied by the DW, select a set of views to materialize such that the combination of the query evaluation and view maintenance cost is minimized and the size of the materialized views does not exceed the space allocated for materialization. • Find the best global evaluation plan for multiple incremental maintenance expressions for materialized views. DOLAP'04 - Washington DC

  4. Problem (3) • Solving view selection problems requires the identification of common sub-expressions between queries. • Usually, this is done by identifying equivalent (or subsumed) view nodes in query evaluation plans of two queries in a bottom-up way. • However, for this approach to be successful, all the alternative query evaluation plans of the queries need to be considered – an unfeasible task. DOLAP'04 - Washington DC

  5. Example - Query Evaluation Plans and common subexpressions DOLAP'04 - Washington DC

  6. Example - Query Evaluation Plans and common subexpressions DOLAP'04 - Washington DC

  7. Example - Query Evaluation Plans and common subexpressions DOLAP'04 - Washington DC

  8. Example - Query Evaluation Plans and common subexpressions DOLAP'04 - Washington DC

  9. Example - Query Evaluation Plans and common subexpressions DOLAP'04 - Washington DC

  10. Our approach DOLAP'04 - Washington DC

  11. Goals • Formalize the concept of ‘closeness’ of a common subexpression to two queries. • Design algorithms for computing common sub-expressions that are as close to the queries as possible (these common subexpressions are called Closest Common Derivators). • We address these problems starting with SPJ queries that involve self-joins. DOLAP'04 - Washington DC

  12. Example Q1 Select R1.A, R2.B, R3.C From U, R as R1, R as R2, R as R3, S as S1 Where U.A=R1.A and R1.B<=R2.B and R2.C<=R3.B and R3.C=S.C and R2.B<3 and R3.A>=4 and R3.A<=7 and S1.D>=3 Q2 Select R4.C, R5.A, S3.C From S as s2, R as R4, R as R5, S as S3, T Where S2.C<=R4.C and R4.C=R5.B and R5.C<=S3.C and S3.D=T.D and R4.B=3 and R5.A>=5 and R5.A<=9 and S3.D>=3 DOLAP'04 - Washington DC

  13. Query Graph Representation DOLAP'04 - Washington DC

  14. Query rewritings • A rewriting Q’ of a query Q using view V is a query that references V and possibly base relations such that replacing V by its definition results in a query equivalent to Q. Notation: Q |-- V. If there is a rewriting of Q that references only V (no base relations), we call it complete rewriting. Notation: Q ||-- V. Otherwise, we call it a partial rewriting. • A rewriting Q’ of query Q using a view V is called simple rewriting if view V has a single occurrence in Q’. • A rewriting Q’ of a query Q using a view V is minimal if for every relation R that has n, n>0, occurrences in Q, R has k, 0 ≤ k≤ n, occurrences in V and n- k occurrences in Q’. Notation: Q |--m V. DOLAP'04 - Washington DC

  15. Common Derivator (CD) of two queries • Let Q1 and Q2 be two queries and R1, R2 be two sets of relation occurrences from Q1 and Q2, respectively, that have the same number of relation occurrences of each relation. A common derivator (CD) of Q1 and Q2 over the respective sets R1 and R2 is a view V such that there is a minimal rewriting of Q1 (resp. Q2) using V that involves V and only those relation occurrences of Q1 (resp. Q2) that do not appear in R1 (resp. R2.) DOLAP'04 - Washington DC

  16. Example - Common Derivator DOLAP'04 - Washington DC

  17. Example - Common Derivator DOLAP'04 - Washington DC

  18. Closeness relationship between CDs • Let Q1, Q2 be two queries, V=X(C(R)) is a CD of Q1 and Q2 over R1 and R2, V’=X(C’(R’)) be a CD of Q1 and Q2 over R1’ and R2’, and R1 R1’ and R2 R2’. CD V’ is closer to Q1 and Q2 than CD V if the following conditions are satisfied (a) V’ |-- V (b) if C’(R’) ||-- C(R) then V ||─V’ DOLAP'04 - Washington DC

  19. Example – Closeness relationship • V2 is closer to Q1 and Q2 than V1 DOLAP'04 - Washington DC

  20. Example – Closeness relationship • V3 is closer to Q1 and Q2 than V2 DOLAP'04 - Washington DC

  21. Example – Closeness relationship • V4 is closer to Q1 and Q2 than V3

  22. Closest Common Derivator (CCD) • Let Q1 and Q2 be two queries. A Closest Common Derivator (CCD) of Q1 and Q2 over R1 and R2 is a CD V of Q1 and Q2 over R1 and R2 such that there exists no CD of Q1 and Q2 that is closer to Q1 and Q2 than V. DOLAP'04 - Washington DC

  23. Example DOLAP'04 - Washington DC

  24. How to compute a CCD • Query graph in Full Form • Condition merging • Candidate CCDs • Comparison of Candidate CCDs over the same occurrence set DOLAP'04 - Washington DC

  25. Full Form Condition and Query • A condition C is in full form if: • For every atomic condition Ai such that C |= Ai, there is an atomic condition Aj in C such that Aj |= Ai (|= denotes logical implication) • Condition C does not include strongly redundant atomic conditions. • A queryX(C(R) is in full form if its condition C is in full from. DOLAP'04 - Washington DC

  26. Example—Query graph Full Form DOLAP'04 - Washington DC

  27. Example—Query graph full form DOLAP'04 - Washington DC

  28. Condition Merging • Two conditions C1 and C2 are mergeable if there is a non-valid condition C such that C1|=C and C2|=C and there exists no condition C', C'≡C, such that C1|=C', C2 |= C' and C’|= C. Condition C is called a merge of C1 and C2. • We show how the merge of two conditions can be computed. DOLAP'04 - Washington DC

  29. CCD Computation • We introduce the concept of a candidate CCD: a graph representation of a CCD resulting by ‘merging’ common subparts of two query graphs. • We show that a CCD of two queries is a candidate CCD. • We express the CCD closeness relationship on candidate CCDs. DOLAP'04 - Washington DC

  30. CCD Computation (2) In order to compute all the CCDs of two queries: • We compute all the candidate CCDs of two query graphs in full form. • We discard a candidate CCD V if there is another CCD V’ that is closer to the queries than V. DOLAP'04 - Washington DC

  31. Future work • Extend the concept of a CCD so that it applies to a more general class of queries. • Use the concept of a CCD to identify common sub-expressions within one query • Use the concept of a CCD to design algorithms for different materialized view selection problems. DOLAP'04 - Washington DC

  32. Thanks DOLAP'04 - Washington DC

More Related