Jonathan Goldstein
Per-Åke Larson
Abstract
Materialized views can provide massive improvements in query processing time, especially for aggregation queries over large tables. To realize this potential, the query optimizer must know how and when to exploit materialized views. This paper presents a fast and scalable algorithm for determining whether part or all of a query can be computed from materialized views and describes how it can be incorporated in transformation-based optimizers. The current version handles views composed of selections, joins and a final group-by. Optimization remains fully cost based, that is, a single “best” rewrite is not selected by heuristic rules but multiple rewrites are generated and the optimizer chooses the best alternative in the normal way. Experimental results based on an implementation in Microsoft SQL Server show outstanding performance and scalability. Optimization time increases slowly with the number of views but remains low even up to a thousand.
- 1 S. Agrawal, S. Chaudhuri, V. R. Narasayya: Automated Selection of Materialized Views and Indexes in SQL Databases. VLDB 2000: 496-505 Google Scholar
Digital Library
- 2 R.G. Bello, K. Dias, J. Feenan, J. Finnerty, W.D. Norcott, H. Sun, A. Witkowski, M. Ziauddin, Materialized Views in Oracle, VLDB 1998, 659-664. Google ScholarDigital Library
- 3 J. Chang and S. Lee, Query Reformulation Using Materialized Views in Data Warehousing Environment, First ACM Int'l Workshop on Data Warehousing and OLAP (DOLAP), 1998, 54-59. Google ScholarDigital Library
- 4 S.Chaudhuri,S.Krishnamurthy,S.Potamianos,K.Shim,Optimizing Queries with Materialized Views, ICDE 1995, 190-200. Google ScholarDigital Library
- 5 S. Cohen, W. Nutt, A. Serebrenik, Rewriting Aggregate Queries Using Views, PODS, 1999, 155-166. Google ScholarDigital Library
- 6 G. Graefe, The Cascades Framework for Query Optimization, Data Engineering Bulletin, 18(3), 1995, 19-29.Google Scholar
- 7 G. Graefe and W. J. McKenna, The Volcano Optimizer Generator: Extensibility and Efficient Search, ICDE 1993, 209-218. Google ScholarDigital Library
- 8 S. Grumbach, M. Rafanelli, L. Tininini, Querying Aggregate Data, PODS 1999, 174-184. Google ScholarDigital Library
- 9 A. Gupta, V. Harinarayan, D. Quass, Aggregate Query Processing in Data WareHousing Environments, VLDB 1995, 358-369. Google ScholarDigital Library
- 10 P.-A. Larson and H. Z. Yang, Computing Queries from Derived Relations, VLDB 1985, 259-269.Google Scholar
- 11 A.Levy,A.O.Mendelzon,Y.Sagiv,D.Srivastava,Answering Queries Using Views, PODS 1995, 95-104. Google ScholarDigital Library
- 12 W. Nutt, Y. Sagiv, S. Shurin, Deciding Equivalence Among Aggregate Queries, PODS 1998, 214-223. Google ScholarDigital Library
- 13 C.-S. Park, M. H. Kim and Y.-J. Lee, Rewriting OLAP Queries Using Materialized Views and Dimension Hierarchies in Data Warehouses, Korea Advanced Institute of Science and Technology, CS/TR-2000-156.Google Scholar
- 14 R. Pottinger, A. Levy, A Scalable Algorithm for Answering Queries Using Views, VLDB 2000, 484-495. Google ScholarDigital Library
- 15 D.Srivastava,S.Dar,H.V.Jagadish,A.Levy,AnsweringQueries with Aggregation Using Views, VLDB 1996, 318-329. Google ScholarDigital Library
- 16 W. P. Yan and P. -A. Larson, Eager Aggregation and Lazy Aggregation, VLDB 1995, 345-357. Google ScholarDigital Library
- 17 H. Z. Yang and P. -A. Larson, Query Transformation for PSJ Queries, VLDB 1987, 245-254. Google ScholarDigital Library
- 18 M. Zaharioudakis, R. Cochrane, G. Lapis, H. Pirahesh, M. Urata, Answering Complex SQL Queries Using Automatic Summary Tables, SIG- MOD 2000, 105-116 Google ScholarDigital Library
Index Terms
- Optimizing queries using materialized views: a practical, scalable solution
Recommendations
Optimizing queries using materialized views: a practical, scalable solution
SIGMOD '01: Proceedings of the 2001 ACM SIGMOD international conference on Management of dataMaterialized views can provide massive improvements in query processing time, especially for aggregation queries over large tables. To realize this potential, the query optimizer must know how and when to exploit materialized views. This paper presents ...
Query evaluation using overlapping views: completeness and efficiency
SIGMOD '06: Proceedings of the 2006 ACM SIGMOD international conference on Management of dataWe study the problem of finding efficient equivalent view-based rewritings of relational queries, focusing on query optimization using materialized views under the assumption that base relations cannot contain duplicate tuples. A lot of work in the ...
Answering XML queries using materialized views revisited
CIKM '09: Proceedings of the 18th ACM conference on Information and knowledge managementAnswering queries using views is a well-established technique in databases. In this context, two outstanding problems can be formulated. The first one consists in deciding whether a query can be answered exclusively using one or multiple materialized ...
Reviews
Evgueni V Ogouretchnikov
Materialized views are a very new area in the world of relational database management systems (RDBMS). They can provide massive improvements in query processing time, especially for aggregation queries over large tables. However, using materialized views efficiently in query optimizers can be difficult. The solution described in this paper is implemented in a production RDBMS, and is supported by experimental results that show outstanding performance and scalability. As the authors state, the main contributions of this paper are an efficient view-matching algorithm for views composed of selections, joins, and a final group-by (SPJG views); and a novel index structure that quickly narrows the search to a small set of candidate views on which view-matching is applied. The version of the algorithm described here is limited to SPJG views, and produces single-view substitutes. Section 2 describes the class of materialized views supported, and defines the problem to be solved. Section 3 describes the algorithm for deciding if a query expression can be computed from a view. Section 4 introduces lattice index structure. Section 5 presents experimental results based on a prototype implementation. Related work is discussed in section 6. Section 7 contains a summary and a brief discussion of possible extensions. This paper is scientifically and practically valuable, and is appropriate for researches and skillful database developers. However, understanding the paper requires deep knowledge of structured query language (SQL) optimization issues. Online Computing Reviews Service
Access critical reviews of Computing literature here
Become a reviewer for Computing Reviews.
Comments