A Survey of Query Optimization in Centralized Database Systems

Database management systems will continue to manage large data volumes. Thus, efficient algorithms for accessing and manipulating large sets and sequences will be required to provide acceptable performance. The advent of object-oriented and extensible database systems will not solve this problem. On the contrary, modern data models exacerbate the problem: In order to manipulate large sets of complex objects as efficiently as today's database systems manipulate simple records, query processing algorithms and software will become more complex, and a solid understanding of algorithm and architectural issues is essential for the designer of database management software.

Query Optimization is an important process in Relational databases. With the current load on databases increasing, the need to optimize queries in batches is a promising way out. Studies have shown that sharing among common sub-expressions can as well be beyond the optimal plans of the constituent queries. But challenges of excessively large sample space, buffer management, establishment of optimal order of optimization, and identification of disjoint queries remain in place. In this dissertation, We propose how We can efficiently establish the extent of inter-query shareability and exploit it so as to compute common sub-expressions once and share the output among the queries. We also propose the optimal order of optimization so that the sharing is done in a more cost saving and time conserving manner. v

Software: Practice and Experience, 1989

This paper describes an algorithm for evaluating database queries represented as expressions in a logical language. Such a database query expression can be evaluated efficiently by focusing on the variable dependencies. The algorithm recursively computes the values of subexpressions to evaluate the input expression, but it avoids re-evaluation of those subexpressions whose values are not affected by new variable assignments. The input expression is internally structured as a directed acyclic graph. Two additional techniques to improve efficiency of the evaluation are discussed: transformations of the input expression and special primitive database operations. Finally, its implementation in the natural language question-answering system SPICOS is described.

The work reported here is supported by DFG (Deutsche Forschungsgemeinschaft) under grant No. Be1786/1-1.

IEEE Transactions on Knowledge and Data Engineering, 1989

The query optimizer is the component in a relational database system that identifies efficient execution plans for input queries. Modern optimizers generally explore many alternative query plans in a cost-based manner. Specifically, the resource consumption and associated cost of each candidate plan is estimated, and the plan with the least expected cost is chosen for execution. The cost estimation for a plan depends on several factors, including resource availability during execution, the specific operators that compose the plan, and the size of intermediate results that would be generated during the plan execution. Among these factors, the intermediate-result size (or cardinality) estimation is the main source of inaccuracies during optimization: cardinality estimation typically relies on several simplifying assumptions that often do not hold in practice. Optimizers then sometimes base their decisions on inaccurate information and produce low-quality execution plans. To address this limitation, in this thesis we introduce the concept of SITs, which are statistics built on query expressions. SITs directly and accurately model intermediate results in a query execution plan, and therefore avoid error-prone simplifying assumptions during cardinality estimation. If optimizers have appropriate SITs available during optimization, the resulting query plans can be dramatically better than otherwise.

SKIT Research Journal

Now a day's, query optimization has become a popular subject for research. The most interest in this area of research arises due to the new trends within the usage of databases. Initially, databases were meant for transaction-based processing of operative information. In present times, it helps to report as well as analysis integrated and historical data. Thus, the importance of database systems is increasing day by day. This role has resulted in complications in data queries due to the increased need of accuracy in query processing. Query processing is really a process of translating a question written in an application-oriented language into lowlevel data manipulation operations. Query processing is related to the implementation of the query. It involves the processes of extraction of data from a knowledge warehouse. In query processing, one of the foremost critical and important steps is query optimization. Query optimization is the way to manufacture an optimal feasible and practical framework for a given query. It aims at supplying minimal reaction time and more and more throughput. A number of the techniques are statistics, histograms, sampling and parametric techniques. Any error within the result size estimates increases the number of joins. Thus, most operation of query optimizer includes transforming queries, estimating and generating plans. The present article is an effort to debate the fundamentals of query optimization. It reveals the varied studies concerned with the topic and also presents the essential techniques and significance of query optimization.

2013

One of the most critical functional requirements of a DBMS is its ability to process queries in a timely manner. This is particularly true for very large, applications such as weather forecasting, banking systems and aeronautical applications, which can contain millions and even trillions of records. The need for faster and faster, and immediate results never ceases. Thus, a great deal of research and resources is required on creating smarter, highly efficient query optimization techniques. Some of the basic techniques of query processing and optimization have been presented in this paper. This paper highlights the basic concepts of query processing and query optimization in the relational database domain. The results of the experiment presented have been verified using Query Analyzer.

Query is a statement or group of statement that adequately execute some basic database operations viz. " Read " , " Write " , " Delete " , and " Update ". It plays a consequential role in managing and retrieving data. In general, distributed queries are more complex and complicated as compared to centralized queries. Queries can be categorized as data creation and data destruction, Data management queries, Data control quarry, OLTP and DSS quarries. In data creation and data destruction quarries create, insert and drop quarries are used. In data management quarry data is managed and manipulate, data can be insert, delete and update. In data control query, one can save data using commit command; permission can be granted using grant command [1][2][3]. In online transaction processing (OLTP) the work analysis and query optimization is done. In decision support system (DSS) queries used to retrieve data from large database. The execution time is not predictable in DSS query. Decision support system (DSS) queries are more complex as compare to online transaction processing queries (OLTP). The running time of DSS queries are unpredictable as compare to OLTP. The process of optimization in Decision support system (DSS) queries is complex as compare to OLTP queries. A distributed DSS query is used to retrieve data from multiple sites. In online transaction processing system (OLTP); real updates are performed. However, DSS queries execute batches as compared to real time updates. Online transaction processing (OLTP) database applications are optimal for managing changing data; these applications typically have many users who are performing transaction at the same time that change real time data, in other words OLTP is a live database. On other side the tables in a decision support database are heavily indexed and the raw data is frequently preprocessed and organized to support various types of queries to be used. The OLTP and DSS queries can be differentiated on the basis of different parameters as mentioned below [1][4][5][6]: A number of heuristics have been applied in recent times, which proposed new algorithms for substantially improving the performance of a query[1][2][3]. As stated by Manik Sharma et al. (2015) there are two major types of database queries called DSS and OLTP queries. To optimize a DSS query on the basis of usage of system resources, one has to find an optimal query execution plan which minimizes the Total Costs of a query. For finding the optimal query execution plan, the costs of

IEEE Transactions on Computers, 2000

A model is developed for determining the optimal policy for processing a given relational model query. The model is based on operating cost (processing cost and communication cost), which is a function of selection of sites for processing query operations, sequence of operations, file size, and data reduction functions. The optimal policy specifies the site selection and sequence of operations that yield minimum operating cost. The query is first decomposed into a set of relational algebra operations whose precedence relationships are expressed as a query tree. Additional query trees may be generated by permuting these operations. A set of query processing graphs is then generated for a given query tree. Each node of a query processing graph represents the execution of a set of operations at a single site. Since the neighboring nodes represent distinct processing sites, the arcs between nodes represent the communication cost among sites. Theorems based on the cost model and the query processing graphs are developed for determining the optimal sites for processing the operations and for selecting the local optimal graphs from the set of query processing graphs. Use of these theorems greatly reduces the computation requirements in determining the optimal query processing policy. An example is given to illustrate the model. Index Terms-Distributed database, local operation group, optimal query processing, query operating cost, query processing graph, query tree, relational algebra, relational database.

IEEE Transactions on Software Engineering, 1985

Object-oriented database systems began developing in the mid-80's out of a necessity to meet the requirements of applications beyond the data processing applications which were [are] served by relational database systems. We propose in this paper a new approach that permits to enrich technique of query optimization existing in the object-oriented databases and the comparative analysis of query optimization for relational databases and object oriented database based on cost, cardinality and no of bytes. Seen the success of query optimization in the relational model, our approach inspires itself of these optimization techniques and enriched it so that they can support the new concepts introduced by the object databases.

International Journal of Computer Applications, 2012

Semantic query optimization is applied to relational databases using the inductive learning approach. This approach generates an alternate query using the learning framework and the algorithm. The alternate query should be semantically equivalent to original query. The semantically equivalent query generated should be less expensive than the original query. These can be implemented in SQL using the SQL hints. These hints allow user to implement the desired plan for the query.

ELK Asia Pacific Journal of Library Management and Information Technology, 2015

2003

The performance of a database management system (DBMS) is fundamentally dependent on the access methods and query processing techniques available to the system. Traditionally, relational DBMSs have relied on well-known access methods, such as the ubiquitous B+-tree, hashing with chaining, and, in some cases, linear hashing [52]. Object-oriented and object-relational systems have also adopted these structures to a great extend.