scispace - formally typeset
Search or ask a question
Proceedings ArticleDOI

Materialized view construction using linear regression on attributes

TL;DR: This paper proposes a new methodology for materialized view creation by quantifying the association among the independent data attributes based on the usage of different attributes in the recently executed set of queries.
Abstract: Materialized view creation is an important aspect for large data centric applications. Materialized views create an abstraction over the actual database tables to the users. Users are not aware about the existence of these materialized views. However, these help in faster execution of query. Materialized views should contain the data that users are currently accessing, and possibly those that would be accessed in near future. Availability of the user-requested data in a materialized view indicates the efficacy of the materialized view creation process. A review of the existing research work reveals a gap in analyzing the inter-attribute affinity while creating the materialized views. This paper proposes a new methodology for materialized view creation by quantifying the association among the independent data attributes. This is done based on the usage of different attributes in the recently executed set of queries. Statistical analysis on existing query set help to predict the attributes likely to be used for future queries. The materialized views are generated accordingly.
Citations
More filters
Book ChapterDOI
05 Nov 2014
TL;DR: This paper has proposed materialized view construction methodology at first by analyzing the attribute similarity based on Jaccard Index then clustering methodology is applied using similarity based weighted connected graph and the clusters are validated to check the correctness of the materialized views.
Abstract: Materialized view is important to any data intensive system where answering queries at runtime is subject of interest. Users are not aware about the presence of materialized views in the system but the presence of these results in fast access to data and therefore optimized execution of queries. Many techniques have evolved over the period to construct materialized views. However the survey work reveals a few attempts to construct materialized views based on attribute similarity measure by statistical similarity function and thereafter applying the clustering techniques. In this paper we have proposed materialized view construction methodology at first by analyzing the attribute similarity based on Jaccard Index then clustering methodology is applied using similarity based weighted connected graph. Further the clusters are validated to check the correctness of the materialized views.

10 citations


Cites background or methods from "Materialized view construction usin..."

  • ...The drawback of [12] and [13] is that both compute attribute relationship as a pair (two attributes) and this knowledge is guiding materialized view construction process....

    [...]

  • ...In another research work linear regression [13] is used to measure the inter-association among attributes and this knowledge is used to form materialized view....

    [...]

Book ChapterDOI
01 Jan 2017
TL;DR: An algorithm which generates a materialized view by considering the frequencies of the multiple attributes at a time taken from a database with the help of Apriori algorithm is proposed, which supports scalabilisalubrityty as well as flexibility.
Abstract: Analysis of data is an inherent part in the world of business to identify interesting patterns underlying in the data set. The size of the data is usually huge in the modern day application. Searching the data from the huge data set with a lesser time complexity is always a subject of interest. These data are mostly stored in tables based on relational model. Data are fetched from these tables using SQL queries. Query response time is an important quality factor for this type of system. Materialized view formation is the most common way of enhancing the query execution speed across industries. Different approaches have been applied over the time to generate materialized views. However few attempts have been made to construct materialized views with the help of Association based mining algorithms and none of those existing Association based methods measure the performance of the views in terms of both Hit-Miss ratio and view size scalability. This paper proposes an algorithm which generates a materialized view by considering the frequencies of the multiple attributes at a time taken from a database with the help of Apriori algorithm. Apriori algorithm is used to generate frequent attribute sets which are further considered for materialization. Moreover by varying the support count, changing the sizes of the frequent attributes sets; proposed methodology supports scalabilisalubrityty as well as flexibility. Experimental results are given to prove the enhanced results over existing inter-attribute analysis based materialized view formation.

5 citations

Book ChapterDOI
01 Jan 2016
TL;DR: This research work applies to recently executed query set to analyze the attribute affinity and then the materialized view is formed based on the result of attribute affinity to keep the benefit of both the approaches.
Abstract: Query processing at runtime is an important issue for data-centric applications. A faster query execution is highly required which means searching and returning the appropriate data of database. Different techniques have been proposed over the time and materialized view construction is one of them. The efficiency of a materialized view (MV) is measured based on hit ratio, which indicates the ratio of number of successful search to total numbers of accesses. Literature survey shows that few research works has been carried out to analyze the relationship between the attributes based on nonlinear equations for materialized view creation. However, as nonlinear regression is slower, in this research work they are mapped into linear equations to keep the benefit of both the approaches. This approach is applied to recently executed query set to analyze the attribute affinity and then the materialized view is formed based on the result of attribute affinity.

4 citations

Proceedings ArticleDOI
04 Dec 2014
TL;DR: In this paper authors adopt an incremental view maintenance policy based on attribute affinity to update the materialized views at run time without using extra space and minimizing the data transfer between the secondary memory and primary memory (where the active materialization views reside).
Abstract: View materialization is being practiced over several years in large data centric applications like database, data warehouse, data mining etc. for faster query processing. Initially the materialized views are formed based on some methodologies, however the performance (hit-miss ratio) of the materialized views may degrade after certain time if the incoming query pattern changes. This situation could be handled efficiently by employing a view maintenance scheme which works dynamically during query execution at run time. As these materialized views involves huge amount of data, consideration of time and space complexity during the maintenance process plays an important role. In this paper authors adopt an incremental view maintenance policy based on attribute affinity to update the materialized views at run time without using extra space and minimizing the data transfer between the secondary memory and primary memory (where the active materialized views reside). This in turn reduces time complexity and supports incremental maintenance eliminating the requirement of full replacement of existing materialized views.

4 citations


Cites background or methods from "Materialized view construction usin..."

  • ...*/ Begin: fori=ltoC Vi = NULL Sli = round( Si * Th/l0 ) for every ith attribute choose jth attribute such that Temp[i, j] = minimum(AAM[i.k] ) ,providedj not in Vi already....

    [...]

  • .../* This method check that whether a query can answer from a existing materialized view or not....

    [...]

  • ...The moment it doesn't have enough memory it calls another method named "Refresh_View()" */ Call method View_Maintenance()...

    [...]

  • ...B. Algorithm Materialized_View_Maintenance...

    [...]

Proceedings ArticleDOI
11 Sep 2014
TL;DR: In this paper, a new materialized view maintenance scheme is proposed using markov's analysis to ensure consistent performance, which is based on Markov analysis is chosen here to predict steady state probability over initial probability.
Abstract: Materialized view is used in large data centric applications to expedite query processing. The efficiency of materialized view depends on degree of result found against the queries over the existing materialized views. Materialized views are constructed following different methodologies. Thus the efficacy of the materialized views depends on the methodology based on which these are formed. Construction of materialized views are often time consuming and moreover after a certain time the performance of the materialized views degrade when the nature of queries change. In this situation either new materialized views could be constructed from scratch or the existing views could be upgraded. Fresh construction of materialized views has higher time complexity hence the modification of the existing views is a better solution. Modification process of materialized view is classified under materialized view maintenance scheme. Materialized view maintenance is a continuous process and the system could be tuned to ensure a constant rate of performance. If a materialized view construction process is not supported by materialized view maintenance scheme that system would suffer from performance degradation. In this paper a new materialized view maintenance scheme is proposed using markov’s analysis to ensure consistent performance. Markov’s analysis is chosen here to predict steady state probability over initial probability. Keywords— View maintenance; markov; steady state probability;

3 citations

References
More filters
Proceedings ArticleDOI
01 Jun 1996
TL;DR: In this article, a lattice framework is used to express dependencies among views and greedy algorithms are presented to determine a good set of views to materialize, with a small constant factor of optimal.
Abstract: Decision support applications involve complex queries on very large databases. Since response times should be small, query optimization is critical. Users typically view the data as multidimensional data cubes. Each cell of the data cube is a view consisting of an aggregation of interest, like total sales. The values of many of these cells are dependent on the values of other cells in the data cube. A common and powerful query optimization technique is to materialize some or all of these cells rather than compute them from raw data each time. Commercial systems differ mainly in their approach to materializing the data cube. In this paper, we investigate the issue of which cells (views) to materialize when it is too expensive to materialize all views. A lattice framework is used to express dependencies among views. We present greedy algorithms that work off this lattice and determine a good set of views to materialize. The greedy algorithm performs within a small constant factor of optimal under a variety of models. We then consider the most common case of the hypercube lattice and examine the choice of materialized views for hypercubes in detail, giving some good tradeoffs between the space used and the average time to answer a query.

1,499 citations

Proceedings Article
10 Sep 2000
TL;DR: This paper presents an end-to-end solution to the problem of selecting materialized views and indexes for SQL databases, and describes results of extensive experimental evaluation that demonstrate the effectiveness of the techniques.
Abstract: Automatically selecting an appropriate set of materialized views and indexes for SQL databases is a non-trivial task. A judicious choice must be cost-driven and influenced by the workload experienced by the system. Although there has been work in materialized view selection in the context of multidimensional (OLAP) databases, no past work has looked at the problem of building an industry-strength tool for automated selection of materialized views and indexes for SQL workloads. In this paper, we present an end-to-end solution to the problem of selecting materialized views and indexes. We describe results of extensive experimental evaluation that demonstrate the effectiveness of our techniques. Our solution is implemented as part of a tuning wizard that ships with Microsoft SQL Server 2000.

690 citations


"Materialized view construction usin..." refers background in this paper

  • ...Availability of desired data in the materialized views is termed as hit and the non-availability of desired data is termed as miss....

    [...]

Journal ArticleDOI
TL;DR: This work presents polynomial-time heuristics for a selection of views to optimize total query response time under a disk-space constraint, for some important special cases of the general data warehouse scenario, viz. an AND view graph, where each query/view has a unique evaluation, and extends this heuristic to the general AND-OR view graphs.
Abstract: A data warehouse stores materialized views of data from one or more sources, with the purpose of efficiently implementing decision-support or OLAP queries One of the most important decisions in designing a data warehouse is the selection of materialized views to be maintained at the warehouse The goal is to select an appropriate set of views that minimizes total query response time and the cost of maintaining the selected views, given a limited amount of resource, eg, materialization time, storage space, etc In This work, we have developed a theoretical framework for the general problem of selection of views in a data warehouse We present polynomial-time heuristics for a selection of views to optimize total query response time under a disk-space constraint, for some important special cases of the general data warehouse scenario, viz: 1) an AND view graph, where each query/view has a unique evaluation, eg, when a multiple-query optimizer can be used to general a global evaluation plan for the queries, and 2) an OR view graph, in which any view can be computed from any one of its related views, eg, data cubes We present proofs showing that the algorithms are guaranteed to provide a solution that is fairly close to (within a constant factor ratio of) the optimal solution We extend our heuristic to the general AND-OR view graphs Finally, we address in detail the view-selection problem under the maintenance cost constraint and present provably competitive heuristics

341 citations


"Materialized view construction usin..." refers background in this paper

  • ...∑ Y = m a + b ∑X ∑ XY = a ∑ X + b ∑ X(2) optimize total query response time under a disk space constraint for data warehouse applications presented in [3]....

    [...]

Proceedings ArticleDOI
01 May 2001
TL;DR: A fast and scalable algorithm for determining whether part or all of a query can be computed from materialized views and how it can be incorporated in transformation-based optimizers is presented.
Abstract: Materialized views can provide massive improvements in query processing time, especially for aggregation queries over large tables. To realize this potential, the query optimizer must know how and when to exploit materialized views. This paper presents a fast and scalable algorithm for determining whether part or all of a query can be computed from materialized views and describes how it can be incorporated in transformation-based optimizers. The current version handles views composed of selections, joins and a final group-by. Optimization remains fully cost based, that is, a single “best” rewrite is not selected by heuristic rules but multiple rewrites are generated and the optimizer chooses the best alternative in the normal way. Experimental results based on an implementation in Microsoft SQL Server show outstanding performance and scalability. Optimization time increases slowly with the number of views but remains low even up to a thousand.

338 citations

Proceedings ArticleDOI
27 May 1997
TL;DR: This work addresses some issues related to determining this set of shared views to be materialized in order to achieve the best combination of good performance and low maintenance, and provides an algorithm for achieving this goal.
Abstract: Data warehouses are accessed by different queries with different frequencies. The portions of data accessed by a query can be treated as a view. When these views are related to each other and defined over overlapping portions of the base data, then it may be more efficient not to materialize all the views, but rather to materialize certain "shared views" from which the query results can be generated. We address some issues related to determining this set of shared views to be materialized in order to achieve the best combination of good performance and low maintenance, and provide an algorithm for achieving this goal.

74 citations


"Materialized view construction usin..." refers methods in this paper

  • ...A heuristic algorithm [2] was described to utilize Multiple View Processing Plan (MVPP) to obtain an optimal materialized view selection....

    [...]

Trending Questions (1)
How can I make SQL Server insert query faster?

However, these help in faster execution of query.