It is estimated that over 90% of the tuning session is spent on query optimization . Running time is less, when heuristic searching techniques are applied. However, this introduces inaccuracy in the search result, causing the selection of sub-optimal results. In this paper, we propose a new cache replacement algorithm to optimize the search queries. It is based on the time taken for the database query. Introduction A database is a collection of related data. A database management system (DB’S) is a suit of computer software providing the interface between a user and database(s).
The performance of a database system depends crucially on its physical design. An effective physical design must match the traits of the workload. It is mandatory that within the constraints of a budget, we need to identify a configuration, that results in optimal cost for a given workload. Applications of DB’S are on the increase. Most complex applications require support of large database. This indicates the need to optimize database query. Tuning of physical design of the database is one way of achieving this aim.
The physical design tuning problem can be formally defined as even a query workload W and a storage budget B, the task is to find the set of physical structures, or configuration that fits in B and results in the lowest execution cost for W . Many approaches have been proposed to solve this problem. All of them need to evaluate the expected cost of a query, for the given configuration, in the database. To obtain the best solution, queries have to be executed for various configurations and the cost has to be calculated. This is not practical. An alternate solution is the what-if optimization as proposed by .
This method simulates a hypothetical configuration In ten toaster. Queries are given to 1 this hypothetical configuration and the optimum solution is obtained. This too does not significantly reduce the overhead associated with regular optimization calls. In this paper, we present a novel algorithm for tuning the database. The algorithm keeps track of the time taken for searching each data item. When a cache miss occurs, the cache replacement algorithm comes to play. It replaces the data that required minimum search time. Data that became available after expensive search are retained in the cache. 2. 1 Related Works Cascade Framework The work of  is an early attempt to optimize database query. The cascade framework proposed by them modeled predicates and operators as part of the query. The operators were also inserted into a plan based on explicit rules. This provided a substantial improvement over the existing solutions. 2. 2 Copy Dash and Lambkin  proposed Combinatorial Optimization for Physical Design (Copy) approach for pruning the search space. The objective function in this case is to find out the plan with the minimum cost for the query.
It does not prune the search space heuristically, as in greedy approaches. Instead it applies combinatorial optimization techniques for pruning the search space. It expresses interesting constraints and provide the DAB with information about the distance of the current solution from the optimal one. Thus this approach allowed the DABS to trade off the execution time against the quality of suggested solutions. 2. 3 Relaxation Based Approach Many of the approaches chose search paths heuristically and then performed a bottom-up search to identify the best overall configuration.
Bruno and Chuddar  simplified such heuristic based approaches and proposed an architecture geared awards avoiding guess work. This approach reduces the assumptions and heuristics used in the earlier approaches. 2. 4 INdex Usage Model (MINIMUM) is a concept proposed by  for query optimization. This is a cost estimation technique. It returns the same results as a query optimizer. But it is faster by three orders. This is achieved by efficiently caching and reusing a few key optimizer calls. 2. 5 C-POGO Most of the automated physical design tuning for database systems explore the space of solutions by sending repeated queries.
This approach is scalable. However it offers from long waiting time for results from the query optimizer. 2 It is found that 90% of the time is spent on this . Configuration-Parametric Query Optimization (C-POGO) avoids such delay by issuing a single optimization call for each query. It then obtains a representation of the optimization search space. It operates on a top-down, transformational query optimizer having functionality similar to the cascade Optimization framework optimizer. C-POGO binds the process on SQL servers top-down optimizer. Therefore it does not deal with how to port C- POGO to another query optimizer.