"Data warehousing and on-line analytical processing (OLAP) are essential elements for decision support applications. Since most OLAP queries are complex and are often executed over huge volumes of data, the solution in practice is to employ materialized views to improve query performance. One important issue for utilizing materialized views is to maintain the view consistency upon source changes. However, most prior work focused on simple SQL views with distributive aggregate functions, such as SUM and COUNT. This dissertation proposes to consider broader types of views than previous work. First, we study views with complex aggregate functions such as variance and regression. Such statistical functions are of great importance in practice. We propose a workarea function model and design a generic framework to tackle incremental view maintenance and answering queries using views for such functions. We have implemented this approach in a prototype system of IBM DB2. An extensive performance study shows significant performance gains by our techniques. Second, we consider materialized views with PIVOT and UNPIVOT operators. Such operators are widely used for OLAP applications and for querying sparse datasets. We demonstrate that the efficient maintenance of views with PIVOT and UNPIVOT operators requires more generalized operators, called GPIVOT and GUNPIVOT. We formally define and prove the query rewriting rules and propagation rules for such operators. We also design a novel view maintenance framework for applying these rules to obtain an efficient maintenance plan. Extensive performance evaluations reveal the effectiveness of our techniques. Third, materialized views are often integrated from multiple data sources. Due to source autonomicity and dynamicity, concurrency may occur during view maintenance. We propose a generic concurrency control framework to solve such maintenance anomalies. This solution extends previous work in that it solves the anomalies under both source data and schema changes and thus achieves full source autonomicity. We have implemented this technique in a data warehouse prototype developed at WPI. The extensive performance study shows that our techniques put little extra overhead on existing concurrent data update processing techniques while allowing for this new functionality."

[pt] O framework Outer-Tuning serve para apoiar a sintonia fina de índices (automática ou não) em um sistema de banco de dados. Trata-se de uma abordagem que oferece transparência acerca das alternativas disponíveis para possíveis cenários de sintonia fina, possibilitando combinar estratégias independentes para obter um melhor desempenho do SGBD e permitindo a discussão de justificativas para as ações realizadas. Através do uso de uma ontologia específica para sintonia fina de bancos de dados relacionais, é possível adicionar semântica ao processo com o entendimento dos conceitos envolvidos e gerar, de maneira (semi)automática, novas práticas de sintonia fina, que podem ser inferidas a partir das práticas existentes ou de novas regras e conceitos que venham a surgir no futuro. Este trabalho de pesquisa apresenta como contribuição inicial o projeto e implementação do framework Outer-Tuning por meio da formalização de uma arquitetura de software que atende aos requisitos funcionais especificados. Este trabalho também contribui com a extensão da ontologia de domínio e a inclusão de novas heurísticas na ontologia de tarefas para contemplar soluções de sintonia fina com o uso de visões materializadas. Desta forma, passa a ser possível propor o uso de heurísticas para realizar a sintonia fina tanto para índices como também para visões materializadas. / [en] The Outer-tuning framework may be used to support automatic (or not) database tuning, particularity index. It is an approach that offers transparency about the available alternatives to feasible tuning scenarios, making it possible to combine either independent strategies or allow discussion of justifications for actions performed in order to obtain better performances. Using a specific ontology for fine tuning relational databases, we add semantics to the process with the understanding of the concepts involved and generate (semi)automatic new tuning actions, which can be inferred from existing practices or new rules and concepts that arise in the future. This research presents as an initial contribution the actual design and implementation of the Outer-tuning framework through the formalization of a software architecture that meets the specified functional requirements. This work also contributes with the extension of the domain ontology and the inclusion of new heuristics to a task ontology, in order to accomplish fine tuning solutions with the use of materialized views. Thus, it becomes possible to propose the use of tuning heuristics for indexes as well as for materialized views.

" Materialized integration views constructed by integrating data from multiple distributed data sources help to achieve better access, reliable performance, and high availability for a wide range of applications. In this dissertation, we propose parallel, adaptive, and grouping techniques to address scalability challenges in high-performance integration view computation and maintenance due to increasingly large data sources and high rates of source updates. State-of-the-art parallel integration view computation makes the common assumption that the maximal pipelined parallelism leads to superior performance. We instead propose segmented bushy parallel processing that combines pipelined parallelism with alternate forms of parallelism to achieve an overall more effective strategy. Experimental studies conducted over a cluster of high-performance PCs confirm that the proposed strategy has an on average of 50\% improvement in terms of total processing time in comparison to existing solutions. Run-time adaptation becomes critical for parallel integration view computation due to its long running and memory intensive nature. We investigate two types of state level adaptations, namely, state spill and state relocation, to address the run-time memory shortage. We propose lazy-disk and active-disk approaches that integrate both adaptations to maximize run-time query throughput in a memory constrained environment. We also propose global throughput-oriented state adaptation strategies for computation plans with multiple state intensive operators. Extensive experiments confirm the effectiveness of our proposed adaptation solutions. Once results have been computed and materialized, it's typically more efficient to maintain them incrementally instead of full recomputation. However, state-of-the-art incremental view maintenance require O($n^2$) maintenance queries with n being the number of data sources that the view is defined upon. Moreover, they do not exploit view definitions and data source processing capabilities to further improve view maintenance performance. We propose novel grouping maintenance algorithms that dramatically reduce the number of maintenance queries to (O(n)). A cost-based view maintenance framework has been proposed to generate optimized maintenance plans tuned to particular environmental settings. Extensive experimental studies verify the effectiveness of our maintenance algorithms as well as the maintenance framework. "

[pt] Enquanto muitos trabalhos de pesquisa propõem uma forma de listar um conjunto de opções de sintonia fina para uma determinada carga de trabalho, poucos oferecem uma maneira de ajudar o DBA a tomar melhores decisões ao encontrar um conjunto de ações disponíveis. TuningChef é o resultado do desenvolvimento de uma proposta do passo a passo desse processo de decisão. Dado um conjunto de opções de sintonia fina, recomendamos um subconjunto com boa proporção de custo-benefício, com contexto suficiente para que o DBA entenda a motivação por trás de cada decisão, incluindo a possibilidade de deixar o usuário construir seu próprio subconjunto e verificar o impacto esperado. Também são apresentados resultados experimentais que demonstram a importância do processo de decisão, onde dentro de um subconjunto de 50+ ações de sintonia fina sugeridas por uma ferramenta externa, apenas 8 mostram-se como benéficas para a carga de trabalho utilizada. / [en] While many research works propose a way to list a set of fine-tuning options for a given workload, only a few offer a way to help the DBA make better decisions when encountering a set of available options, especially when taking his possibilities into consideration. We propose and develop a step-by-step decision process. Given a set of fine-tuning options, we recommend a subset with good cost-benefit proportion. Enough context for the DBA accompanies the recommendation to understand its reasoning, with the possibility of letting the user build his own subset and check the expected impact. Some experimental results are also described, showing the importance of the decision step when fine tuning a database, where in a set on 50+ fine tuning actions suggested by an external tool, only 8 are considered beneficial for the a specific workload.