OPTASSIST: A RELATIONAL DATA WAREHOUSE OPTIMIZATION ADVISOR

Data warehouses store large amounts of data usually accessed by complex decision making queries with many selection, join and aggregation operations. To optimize the performance of the data warehouse, the administrator has to make a physical design. During physical designphase, the Data Warehouse Administrator has to select some optimization techniques to speed up queries. He must make many choices as optimization techniques to perform,their selection algorithms, parametersof these algorithms and the attributes and tables used by some of these techniques. We describe in this paper the nature of the difficulties encountered by the administrator during physical design. We subsequently present a tool which helps the administrator to make the right choicesfor optimization. We demonstrate the interactive use of this tool using a relational data warehouse created and populated from the APB-1 Benchmark

CoPhy: A Scalable, Portable, and Interactive Index Advisor for Large Workloads

Index tuning, i.e., selecting the indexes appropriate for a workload, is a crucial problem in database system tuning. In this paper, we solve index tuning for large problem instances that are common in practice, e.g., thousands of queries in the workload, thousands of candidate indexes and several hard and soft constraints. Our work is the first to reveal that the index tuning problem has a well structured space of solutions, and this space can be explored efficiently with well known techniques from linear optimization. Experimental results demonstrate that our approach outperforms state-of-the-art commercial and research techniques by a significant margin (up to an order of magnitude).Comment: VLDB201

A Framework for the Automatic Physical Configuration and Tuning of a Mysql Community Server

Manual physical configuration and tuning of database servers, is a complicated task requiring a high level of expertise. Database administrators must consider numerous possibilities, to determine a candidate configuration for implementation. In recent times database vendors have responded to this problem, providing solutions which can automatically configure and tune their products. Poor configuration choices, resulting in performance degradation commonplace in manual configurations, have been significantly reduced in these solutions. However, no such solution exists for MySQL Community Server. This thesis, proposes a novel framework for automatically tuning a MySQL Community Server. A first iteration of the framework has been built and is presented in this paper together with its performance measurements

Dépendances fonctionnelles (extraction et exploitation)

Les dépendances fonctionnelles fournissent une information sémantique sur les données d une table en mettant en lumière les liens de corrélation qui les unient. Dans cette thèse, nous traitons du problème de l extraction de ces dépendances en proposant un contexte unifié permettant la découverte de n importe quel type de dépendances fonctionnelles (dépendances de clé, dépendances fonctionnelles conditionnelles, que la validité soit complète ou approximative). Notre algorithme, ParaCoDe, s exécute en parallèle sur les candidats, réduisant ainsi le temps global de calcul. De ce fait, il est très compétitif vis-à-vis des approches séquentielles connues à ce jour. Les dépendances satisfaites sur une table nous servent à résoudre le problème de la matérialisation partielle du cube de données. Nous présentons une caractérisation de la solution optimale dans laquelle le coût de chaque requête est borné par un seuil de performance fixé préalablement et dont la taille est minimale. Cette spécification de la solution donne un cadre unique pour décrire et donc comparer formellement les techniques de résumé de cubes de données.Functional dependancies provide a semantic information over data from a table to exhibit correlation links. In this thesis, we deal with the dependancy discovery problem by proposing a unified context to extract any type of functional dependencies (key dependencies, conditional functional dependencies, with an exact or an approximate validity). Our algorithm, ParaCoDe, runs in parallel on candidates there by reducing the global time of computations. Hence, it is very competitive comparated to sequential appoaches known today. Satisfied dependencies on a table are used to solve the problem of partial materiali-zation of data cube. We present a characterization of the optimal solution in which the cost of each query is bounded by a before hand fixed performance threshold and its size is minimal. This specification of the solution gives a unique framework to describe and formally compare summarization techniques of data cubes.BORDEAUX1-Bib.electronique (335229901) / SudocSudocFranceF

Optimization of Progressive Queries via Materialized Views for Large Databases

There is an increasing demand to efficiently process emerging types of queries, such as progressive queries (PQ), on large scale databases from numerous contemporary applications including telematics, e-commerce, and social media. Unlike a conventional query, a PQ consists of a set of interrelated step-queries (SQ). A user formulates a new SQ on the fly based on the result(s) from the previously executed SQ(s). Processing PQs raises a number of new challenges. Existing database management systems were not designed to efficiently process such queries. In this dissertation, we propose a suite of novel materialized-view based techniques to efficiently process PQs. First, we propose a dynamic materialized-view based approach to efficiently processing a special type of PQs, called monotonic linear PQs. We introduce a so-called superior relationship graph to capture superior relationships among SQs of such a PQ and suggest a method to estimate the benefit of keeping the result of an SQ as a materialized view using the graph. To efficiently construct the superior relationship graph, we propose two algorithms: generating-based and pruning-based. To improve the view searching efficiency and quality, we design an algorithm with a special storage structure to store and manage the materialized views. Second, to handle generic PQs, we define a so-called multiple query dependency graph to capture the data source dependency relationships that exist among SQs and external tables of a generic PQ. Using the graph, a mathematical benefit estimation model, which takes both the impact and the effectiveness of materialization into consideration, is derived. A greedy method and a dynamic programming method to solve the view maintenance problem are proposed. Third, to efficiently find usable materialized views from the view space/set for answering a given SQ, we suggest a dynamic materialized view index method. A special index tree structure with nodes ordered by a two-level priority rule that facilitates efficient locating of different types of nodes is designed. Bitmaps encoded with special methods are also used to refine the pruning of unusable views during a search. Fourth, to support PQs in a big data environment like Hadoop, we propose an index based technique for performing a new column family join operation on Hbase tables. To efficiently process such a join operation, we suggest a multiple freedom family index. A parallel MapReduce algorithm to construct the index is developed. To perform a column family join on two Hbase tables using the indexes, we present two partitioning methods to balance the workload among map nodes in a MapReduce algorithm. The introduced column family join operation and its relevant processing technique can ensure the closure property that is essential to the processing of PQs. To examine the performance of the proposed techniques, we performed extensive empirical and theoretical analyses. Our studies show that the proposed techniques are quite promising in efficiently processing PQs. To our knowledge, our work is the first to apply the materialized-view based approach to efficiently processing progressive queries on large databases.Ph.D.College of Engineering and Computer ScienceUniversity of Michigan-Dearbornhttp://deepblue.lib.umich.edu/bitstream/2027.42/110311/1/ChaoZhu_Thesis_final.pdfDescription of ChaoZhu_Thesis_final.pdf : Dissertatio