View Of Materials Research Articles

UniView: A Unified Autonomous Materialized View Management System for Various Databases

Materialized views (MVs) are critical for improving query performance of database systems, especially in online analytical processing (OLAP) databases. Typically, MVs are maintained by DBAs, which relies on prior knowledge and manual operations. Recently, autonomous solutions are designed for specific databases. However, a data warehouse for OLAP is typically hierarchical, which uses different database engines at different stages. Hence, existing methods have limitations in terms of autonomy and unification to support practical applications. Motivated by these, we develop UniView, a unified autonomous materialized view management system that supports various popular databases, including Spark SQL, PostgreSQL, and ClickHouse. Moreover, we provide a cross-platform web user interface, where users can carry out the process of materialized views and evaluate the optimization performance. In the demonstration, we show that UniView is user-friendly and can achieve superior performance in the practical industry scenarios.

AutoView: An Autonomous Materialized View Management System with Encoder-Reducer

Materialized views (MVs) can significantly optimize the query processing in databases. However, it is hard to generate MVs for ordinary users because it relies on background knowledge, and existing methods rely on DBAs to generate and maintain MVs. However, DBAs cannot handle large-scale databases, especially cloud databases that have millions of database instances and support millions of users. Thus it calls for an autonomous MV management system. In this paper, we propose an autonomous materialized view management system. It analyzes query workloads, estimates the costs and benefits of materializing queries as views, and selects MVs to maximize the benefit within a space budget. We propose a serialization and encoding method that can encode the features of both queries and views into vectors. Then we design a sequence-to-sequence model, Encoder-Reducer, to estimate MVs' cost/benefit by taking the encoding vectors as input. Next, we propose a deep reinforcement learning model to select high-quality MVs, which enriches the state representation with Encoder-Reducer's output. Experimental results show that our method outperforms existing studies in terms of MV selection quality.

The evolution of Amazon redshift

The evolution of Amazon redshift

Human behavior based particle swarm optimization for materialized view selection in data warehousing environment

Because of the Materialized View (MV) space value and repair cost limitation in Data Warehouse (DW) environment, the materialization of all views was practically impossible thus suitable MV selection was one of the smart decisions in building DW to get optimal efficiency, at the same time in the modern world, techniques for enhancing DW quality were appeared continuously such as swarm intelligence. Therefore, this paper presents first framework for speeding up query response time depending on Human Particle Swarm Optimization (HPSO) algorithm for determining the best locations of the views in the DW. The results showed that the proposed method for selecting best MV using HPSO algorithm is better than other algorithms via calculating the ratio of query response time on the base tables of DW and compare it to the response time of the same queries on the MVs. Ratio of implementing the query on the base table takes 14 times more time than the query implementation on the MVs. Where the response time of queries through MVs access equal to 106 milliseconds while by direct access queries equal to 1066 milliseconds. This outlines that the performance of query through MVs access is 1471.698% better than those directly access via DW-logical.

MATERIALIZED VIEWS QUANTUM OPTIMIZED PICKING for INDEPENDENT DATA MARTS QUALITY

Particular and timely unified information along with quick and effective query response times is the basic fundamental requirement for the success of any collection of independent data marts (data warehouse) which forms Fact Constellation Schema or Galaxy Schema. Because of the materialized view storage area, the materialization of all views is practically impossible thus suitable materialized views (MVs) picking is one of the intelligent decisions in designing a Fact Constellation Schema to get optimal efficiency. This study presents a framework for picking best-materialized view using Quantum Particle Swarm Optimization (QPSO) algorithm where it is one of the stochastic algorithm in order to achieve the effective combination of good query response time, low query handling cost and low view maintenance cost. The results reveals that the proposed method for picking best-materialized view using QPSO algorithm is better than other techniques via computing the ratio of query response time and compare it to the response time of the same queries on the materialized views. Ratio of implementing the query on the base table takes five times more time than the query implementation on the materialized views. Where the response time of queries through MVs access were found 0.084 seconds while by direct access queries were found 0.422 seconds. This outlines that the performance of query through materialized views access is 402.38% better than those directly access via data warehouse-logical.

Materialized view selection problem using genetic algorithm for manufacturing execution system

Nowadays, a lot of equipment and machines in factories generating a high volume of production data at high speed which are then stored in a data warehouse. These data are used for analytic reporting purposes, to assist the decision-maker in strategizing their business. To speed up the report generation queries, materialized views (MVs) are used. An MV is a database object that contains the results of a query. However, each MV has different query time and maintenance cost. Therefore, it is crucial to select the best set of MVs in order to generate the report while minimizing the incurred cost. Genetic Algorithm is proposed to find the best set of MVs with minimum cost and time, and at the same time maximizing the number MVs selected. Experimental results demonstrate that the proposed GA delivered better solutions compared to the conventional approach

Materialized View Maintenance: Issues, Classification, and Open Challenges

Views materialization is a powerful technique for optimization of query evaluation. However, when data sources are updated, these materialized views (MVs) should be maintained in order to get appropriate queries’ answers. This work provides an overview of the research field of view maintenance, focusing on view maintenance issues and dimensions that cover the most important ones proposed in the literature review. We provide a classification and review the state-of-the-art of view maintenance approaches according to several parameters characterizing their algorithms, including information considered during the view maintenance process, principle used, and the execution mechanism. We provide the most important performance evaluation metrics of view maintenance process while performing a comparative study of some important approaches. Lastly, we give comprehensive perspectives on view maintenance in the different promising environments of MV applications.

English

In recent years, materialized views (MVs) are widely used to enhance the database performance by storing pre-calculated results of resource-intensive queries in the physical memory. In order to identify which queries may be potentially materialized, database transaction log for a long period of time should be analyzed. The goal of analysis is to distinguish resource-intensive and frequently used queries collected from database log, and optimize these queries by implementation of MVs. In order to achieve greater efficiency of MVs, they were used not only for the optimization of single queries, but also for entire groups of queries that are similar in syntax and execution results. Thus, the problem stated in this article is the development of approach that will allow forming groups of queries with similar syntax around the most resource-intensive queries in order to identify the list of potential candidates for materialization. For solving this problem, we have applied the algorithm of categorical data clustering to the query grouping problem on the step of database log analysis and searching candidates for materialization. In the current work CLOPE algorithm was modified to cover the introduced problem. Statistical and timing indicators were taken into account in order to form the clusters around the most resource intensive queries. of modified algorithm CLOPE allowed to decrease calculable complexity of clustering and to enhance the quality of formed groups. ACM CCS (2012) Classification : Information systems→ Data management systems → Database management system engines→Query optimization; Computing methodologies→Cluster analysis *To cite this article: K. Novokhatska and O. Kungurtsev, Application of Clustering Algorithm CLOPE to the Query Grouping Problem in the Field of Materialized View Maintenance, CIT. Journal of Computing and Information Technology , vol. 24, no. 1, pp. 79-89, 2016.

Development of technology for automated creation of materialized views

One of the urgent tasks in the DBMS optimization by the materialized views (MV) - the saved query evaluation results - is selecting queries, the result of which can be materialized. The problem is characterized by high computational complexity since it requires an analysis of the DBMS transaction log over a long period of time. The choice of the MV should be based on the cost of their future support, as well as the execution frequency and cost of queries, optimized by the MV data. It is effective to create the MP not for individual queries, but to form groups of similar queries and create views for them. When searching for the same type of queries, the SQL syntax should be taken into account. The paper considers the technology for automated creation of MV. Query evaluation was proposed, which takes into account the statistical and time indicators of query execution, as well as update frequency of the base tables. The query tokenization algorithm, which takes into account various syntactic structures of SQL was developed. The model of presenting queries in the form of numeric vectors was described. The pre-clustering stage was introduced to reduce the volume of the data processed. The improved clustering algorithm has allowed to form groups around the most resource-intensive and often executed queries. An algorithm for grouping and forming the central query, specifying the results of the preliminary clustering was developed. The described technology allows to automate the full creation cycle of MV. Experimental data showed that technology improvement has allowed to increase performance and reduce the resource intensity of the MV generation process, as well as enhance the quality of the developed MV compared to previous solutions.

Stale view cleaning

Materialized views (MVs), stored pre-computed results, are widely used to facilitate fast queries on large datasets. When new records arrive at a high rate, it is infeasible to continuously update (maintain) MVs and a common solution is to defer maintenance by batching updates together. Between batches the MVs become increasingly stale with incorrect, missing, and superfluous rows leading to increasingly inaccurate query results. We propose Stale View Cleaning (SVC) which addresses this problem from a data cleaning perspective. In SVC, we efficiently clean a sample of rows from a stale MV, and use the clean sample to estimate aggregate query results. While approximate, the estimated query results reflect the most recent data. As sampling can be sensitive to long-tailed distributions, we further explore an outlier indexing technique to give increased accuracy when the data distributions are skewed. SVC complements existing deferred maintenance approaches by giving accurate and bounded query answers between maintenance. We evaluate our method on a generated dataset from the TPC-D benchmark and a real video distribution application. Experiments confirm our theoretical results: (1) cleaning an MV sample is more efficient than full view maintenance, (2) the estimated results are more accurate than using the stale MV, and (3) SVC is applicable for a wide variety of MVs.

An Association Rule Mining for Materialized View Selection and View Maintanance

Data warehouse (DW) is a repository with query interface in support of Decision support systems. DW required answering many complex queries, managerial level queries and analytical queries, needing to develop advanced computing techniques. The DW system process involving data modeling, ETL process, query interface and reporting system. Materialized views (MV) are the pre calculated views which are used to increase the DW system performance. MV selection and maintenance need to adopt new trends and techniques. Data mining (DM) is the process of extracting hidden useful information from huge data bases .Literature of Data Mining (DM) involving algorithms and techniques related to association, classification and clustering. Recent researches shown Data mining can also be used in optimization of calculating MV‟s. In addition to DM techniques are also used in efficient calculation of Data cubes. This paper proposed frequent rule mining on of the Data mining approach for the selection and maintenance of MV‟S. By using the advanced concepts of frequent mining algorithm the query response time can be decreased. The approach also combines the advanced techniques to accommodate the changes in updating the base data so as to increase the performance of existing MV‟S selection and maintenance approaches.

DETERMINATION OF THE PARAMETERS OF PERIODIC ON / OFF MATERIALIZED VIEW IN THE INFORMATION SYSTEM

A method for analyze the sequence of requests with the purpose to identify the IS parameters needed to control the periodic enabling and disabling materialized views (MV) is proposed it this article.

CORADD

We describe an automatic database design tool that exploits correlations between attributes when recommending materialized views (MVs) and indexes. Although there is a substantial body of related work exploring how to select an appropriate set of MVs and indexes for a given workload, none of this work has explored the effect of correlated attributes (e.g., attributes encoding related geographic information) on designs. Our tool identifies a set of MVs and secondary indexes such that correlations between the clustered attributes of the MVs and the secondary indexes are enhanced, which can dramatically improve query performance. It uses a form of Integer Linear Programming (ILP) called ILP Feedback to pick the best set of MVs and indexes for given database size constraints. We compare our tool with a state-of-the-art commercial database designer on two workloads, APB-1 and SSB (Star Schema Benchmark---similar to TPC-H). Our results show that a correlation-aware database designer can improve query performance up to 6 times within the same space budget when compared to a commercial database designer.

Answering complex SQL queries using automatic summary tables

We investigate the problem of using materialized views to answer SQL queries. We focus on modern decision-support queries, which involve joins, arithmetic operations and other (possibly user-defined) functions, aggregation (often along multiple dimensions), and nested subqueries. Given the complexity of such queries, the vast amounts of data upon which they operate, and the requirement for interactive response times, the use of materialized views (MVs) of similar complexity is often mandatory for acceptable performance. We present a novel algorithm that is able to rewrite a user query so that it will access one or more of the available MVs instead of the base tables. The algorithm extends prior work by addressing the new sources of complexity mentioned above, that is, complex expressions, multidimensional aggregation, and nested subqueries. It does so by relying on a graphical representation of queries and a bottom-up, pair-wise matching of nodes from the query and MV graphs. This approach offers great modularity and extensibility, allowing for the rewriting of a large class of queries.

Indexing medium-dimensionality data in Oracle

With the advent of GIS, multi-media, and warehousing technologies, database systems have started focusing on storage and access of multi-dimensional data such as spatial, OLAP, image, audio, and video attributes. As a step in this direction, Oracle% launched the interMedia product to support spatial and image data, and Materialized Views (MV) to support warehousing applications. Although 2-dimensional spatial data is efficiently indexed using OracleBi Spatial, and highdimensional image data using a combination of bitmap indexing and the Visual Information Retrieval (VIR) product, there is still a need for efficient indexing mechanisms for medium-dimensionality data such as OLAP, and CAD/CAM applications. In this paper, we describe the implementation of a new indextype, called the R-tree, to support medium-dimensionality data (i.e., data whose dimensionality is in the range of 310) using the extensible indexing framework [DDSS95] of OracleSi. This indextype combines some of the best features of existing R-tree variants [Gut84, BKSSSO, WJ96, LLE97, RKV95].