Automated generation of materialized views in Oracle

Automated generation of a right set of materialized views is a challenging task. It is a highly desirable feature for autonomous databases. The selection of materialized views must be based on cost and verifiable in the actual database environment. This paper describes an automated system that generates, selects, verifies, and maintains materialized views in the Oracle RDBMS; it presents a novel technique, called the extended covering sub-expression algorithm, for the automated generation of materialized views. An extensive set of experiments is described that demonstrates the feasibility and efficiency of this approach. This system has been fully implemented and is going to be deployed on the Oracle Autonomous Database on the Cloud.

[1]  Henrik Madsen,et al.  Introduction to Generalized Linear Models , 2012 .

[2]  Meikel Pöss,et al.  Of Snowstorms and Bushy Trees , 2014, Proc. VLDB Endow..

[3]  Hong Su,et al.  Cost-based query transformation in Oracle , 2006, VLDB.

[4]  Wolfgang Lehner,et al.  Efficient exploitation of similar subexpressions for query processing , 2007, SIGMOD '07.

[5]  Hamid Pirahesh,et al.  Recommending materialized views and indexes with the IBM DB2 design advisor , 2004, International Conference on Autonomic Computing, 2004. Proceedings..

[6]  Hiren Patel,et al.  Selecting Subexpressions to Materialize at Datacenter Scale , 2018, Proc. VLDB Endow..

[7]  Mohamed Ziauddin,et al.  Materialized Views in Oracle , 1998, VLDB.

[8]  Inderpal Singh Mumick,et al.  Selection of Views to Materialize Under a Maintenance Cost Constraint , 1999, ICDT.

[9]  Surajit Chaudhuri,et al.  Table of Contents (pdf) , 2007, VLDB.

[10]  Benoît Dageville,et al.  Automatic SQL Tuning in Oracle 10g , 2004, VLDB.

[11]  Eric R. Ziegel,et al.  An Introduction to Generalized Linear Models , 2002, Technometrics.

[12]  Surajit Chaudhuri,et al.  Automated Selection of Materialized Views and Indexes in SQL Databases , 2000, VLDB.

[13]  Surajit Chaudhuri,et al.  Database tuning advisor for microsoft SQL server 2005: demo , 2005, SIGMOD '05.

[14]  Sam Lightstone,et al.  DB2 Design Advisor: Integrated Automatic Physical Database Design , 2004, VLDB.

[15]  Surajit Chaudhuri,et al.  Automatically Indexing Millions of Databases in Microsoft Azure SQL Database , 2019, SIGMOD Conference.

[16]  Gregory Piatetsky-Shapiro.  The optimal selection of secondary indices is NP-complete , 1983, SGMD.

[17]  Surajit Chaudhuri,et al.  AutoAdmin “what-if” index analysis utility , 1998, SIGMOD '98.

[18]  Viktor Leis,et al.  How Good Are Query Optimizers, Really? , 2015, Proc. VLDB Endow..

[19]  S. Sudarshan,et al.  Efficient and Provable Multi-Query Optimization , 2015, PODS.

[20]  Sylvain Arlot,et al.  A survey of cross-validation procedures for model selection , 2009, 0907.4728.

[21]  Jonathan Goldstein,et al.  Optimizing queries using materialized views: a practical, scalable solution , 2001, SIGMOD '01.

[22]  Surajit Chaudhuri,et al.  An Efficient Cost-Driven Index Selection Tool for Microsoft SQL Server , 1997, VLDB.

[23]  Surajit Chaudhuri,et al.  Index selection for databases: a hardness study and a principled heuristic solution , 2004, IEEE Transactions on Knowledge and Data Engineering.

[24]  Prasan Roy,et al.  Efficient and extensible algorithms for multi query optimization , 1999, SIGMOD '00.

[25]  Rajeev Motwani,et al.  Towards estimation error guarantees for distinct values , 2000, PODS.

[26]  M. Kubát.  An Introduction to Machine Learning , 2017, Springer International Publishing.

[27]  Rada Chirkova,et al.  Exact and inexact methods for selecting views and indexes for OLAP performance improvement , 2008, EDBT '08.