Automatic query rewrite using materialized views in operations optimization in databases

Krzysztof Czajkowski, Marek Banach

Abstract


The article discusses the operation of automatic query rewriting and its application in the optimization of the commands in the database. Solutions of this type allow you to significantly shorten the execution time of complex queries in complex systems, data warehouses and analytical environments. The article discusses the issues of additional mechanisms helpful in assisting the tuning process of rewriting queries, including analysis of views and queries, as well as statistics. Experiments were performed in selected database environments and their results are presented in the article.

Keywords


query; rewriting queries; materialized views; optimization

Full Text:

PDF (Polski)

References


Kozielski S., Wrembel R. (eds.): New Trends in Data Warehousing and Data Analysis. Annals of Information Systems, Vol. 3, Springer, 2009.

Wrembel R., Koncilia C.: Data Warehouses and OLAP. IRM Press, 2007.

Oracle Database Data Warehousing Guide 11g.

Puget Sound Oracle Users Group - http:/psoug.org/reference/dbms_mview.html.

Loney K.: Oracle Database 11g Kompendium administrator. Helion, Gliwice 2010.

Freeman R.G., Nanda A.: Oracle Database 11g Nowe możliwości. Helion, Gliwice 2009.

Hanson E.N., Angelov Y.: Statistics Used by the Query Optimizer in Microsoft SQL Server 2008. SQL Server Technical Article, February 2009.

Erickson G., Kollar L., Ward J.: Improving Performance with SQL Server 2008 Indexed Views. SQL Sewer Technical Article, October 2008.

Stawiarski K.: Oracle 11g - nowe cechy w strojeniu wydajności i tworzeniu aplikacji dla bazy danych. XIV Konferencja PLOUG, październik 2008.

Dageville B., Das D., Dias K., Yagoub K., Zait M., Ziauddin M.: Automatic SQL Tuning in Oracle 10g. Oracle Corp., 2006.

Lewis J.: Cost-Based Oracle Fundamentals. Springer-Verlag, New York 2006.

Thiyagarajan M., Kumar P.: Inline view query rewrite using a materialized view. 2009.

Hobbs L.: Oracle Materialized View & Query Rewrite. An Oracle White Paper, May 2005.

Gupta A., Witkowski A.: Rewrite of queries containing rank or row number min/max aggregate functions using a materialized views. San Jose 2006.

Antognini C.: Troubleshooting Oracle Performance. Springer-Verlag, New York 2008.

Bello R.G., Panchapagesan B., Yu T., Raitto J. D.: Rewriting a query to use a set of materialized views and database object. Reedwood Shores, 2008.

Freeman R.G.: Oracle Database 10g New Features. The McGraw-Hill, California 2004.

Hobbs L., Smith P., Lawande S.: Oracle Database 10g Data Warehousing. Elsevier, 2004.




DOI: http://dx.doi.org/10.21936/si2011_v32.n2B.294