p445-larson - View Matching for Outer-Join Views Per ˚ Ake...

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

View Full Document Right Arrow Icon

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

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: View Matching for Outer-Join Views Per- ˚ Ake Larson Jingren Zhou Microsoft Research { palarson, jrzhou } @microsoft.com Abstract Prior work on computing queries from mate- rialized views has focused on views defined by expressions consisting of selection, projection, and inner joins, with an optional aggregation on top (SPJG views). This paper provides the first view matching algorithm for views that may also contain outer joins (SPOJG views). The algorithm relies on a normal form for SPOJ expressions and does not use bottom- up syntactic matching of expressions. It han- dles any combination of inner and outer joins, deals correctly with SQL bag semantics and exploits not-null constraints, uniqueness con- straints and foreign key constraints. 1 Introduction Appropriately selected materialized views can speed up query processing greatly but only if the query opti- mizer can determine whether a query or part of query can be computed from existing materialized views. This is the view matching problem. Most work on view matching has focused on views defined by expressions consisting of selection, projection, and inner joins, pos- sibly with a single group-by on top (SPJG views). In this paper we introduce the first view matching algo- rithm for views where some of the joins may be outer joins (SPOJG views). The simplest approach to view matching is syntac- tic; essentially bottom-up matching of the operator trees of query and view expressions. However, algo- rithms of this type are easily fooled by expressions that are logically equivalent but syntactically different. A more robust approach is based on logical equivalence of expressions, which requires converting the expres- sions into a common normal form. SPJ expressions can be converted to a normal form consisting of a Cartesian product of all operand tables, followed by a selection and projection. More recently, Galindo- Legaria [5] showed that SPOJ expressions also have Permission to copy without fee all or part of this material is granted provided that the copies are not made or distributed for direct commercial advantage, the VLDB copyright notice and the title of the publication and its date appear, and notice is given that copying is by permission of the Very Large Data Base Endowment. To copy otherwise, or to republish, requires a fee and/or special permission from the Endowment. Proceedings of the 31st VLDB Conference, Trondheim, Norway, 2005 a normal form, called join-disjunctive normal form, which is the basis for our algorithm. Example 1. Suppose we create the view shown below against tables in the TPC-R database. create view oj view as select o orderkey, o custkey, l linenumber, l quantity, l extendedprice, p partkey, p name, p brand, p retailprice from part left outer join (orders left outer join lineitem on (l orderkey=o orderkey)) on (p partkey=l partkey) The following query asks for total quantity sold for each part with partkey < 100, including parts with no sales. Can this query be computed from the view?sales....
View Full Document

This note was uploaded on 03/01/2010 for the course ICT ... taught by Professor ... during the Three '10 term at University of Sydney.

Page1 / 12

p445-larson - View Matching for Outer-Join Views Per ˚ Ake...

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

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