Query optimization using restructured views: Theory and experiments

  • Authors:

  • Dongfeng Chen;Rada Chirkova;Fereidoon Sadri

  • Affiliations:

  • Computer Science Department, NC State University, Raleigh, NC 27695, United States;Computer Science Department, NC State University, Raleigh, NC 27695, United States;Computer Science Department, University of North Carolina at Greensboro, Greensboro, NC 27402, United States

  • Venue:
  • Information Systems
  • Year:
  • 2009

Quantified Score

Hi-index 0.00

Visualization

Abstract

We study optimization of relational queries using materialized views, where views may be regular or restructured. In a restructured view, some data from the base table(s) are represented as metadata-that is, schema information, such as table and attribute names-or vice versa. Using restructured views in query optimization opens up a new spectrum of views that were not previously available, and can result in significant additional savings in query-evaluation costs. These savings can be obtained due to a significantly larger set of views to choose from, and may involve reduced table sizes, elimination of self-joins, clustering produced by restructuring, and horizontal partitioning. In this paper we propose a general query-optimization framework that treats regular and restructured views in a uniform manner and is applicable to SQL select-project-join queries and views without or with aggregation. Within the framework we provide (1) algorithms to determine when a view (regular or restructured) is usable in answering a query and (2) algorithms to rewrite queries using usable views. Semantic information, such as knowledge of the key of a view, can be used to further optimize a rewritten query. Within our general query-optimization framework, we develop techniques for determining the key of a (regular or restructured) view, and show how this information can be used to further optimize a rewritten query. It is straightforward to integrate all our algorithms and techniques into standard query-optimization algorithms. Our extensive experimental results illustrate how using restructured views (in addition to regular views) in query optimization can result in a significant reduction in query-processing costs compared to a system that uses only regular views.