Index Selection Problem Research Articles

Construction of Evaluation System of Water Resources Environmental Carrying Capacity in Island Tourism based on Frequency Analysis Method

Most evaluation systems don’t consider the problem of evaluation index selection, so the evaluation delay increases, and the evaluation results are not ideal, in order to solve the above problems effectively, a system to evaluate the water resources environmental carrying capacity in island tourism based on frequency analysis method is proposed and constructed. Through the study of the concept and connotation of water resources environmental carrying capacity in island tourism, combined with the system analysis theory, the principle of comprehensive evaluation indicator selection is established; the basic indicators are screened by frequency analysis method, and the evaluation system of water resources environmental carrying capacity in island tourism is established. According to the established system, a comprehensive evaluation model is set up to evaluate the environmental carrying capacity of water resources in island tourism. The simulation results show that the proposed system can effectively reduce the delay and improve the accuracy of the results to evaluate the environmental carrying capacity of water resources in island tourism.

The index selection problem with configurations and memory limitation: A scatter search approach

Within the physical designing process of relational databases, the Index Selection Problem aims at finding the subset of indexes to build for accessing the stored information. More precisely, given a database workload, each query must be served by at most one predefined set of indexes (named configuration) to maximize the net gain in terms of time. This gain is made up of the time gain obtained to serve the queries through the configurations minus the fixed time needed to create and maintain those configurations. The clustering of indexes into configurations and the limited amount of memory available to store the indexes characterize our variant of the problem. At the same time, established approaches in the literature have only considered those two aspects separately. We model this setting as a generalization of the Uncapacitated Facility Location Problem with budget constraint and propose an Integer Linear Programming formulation for it. Then, to find near-optimal solutions in a reasonable computational time, we develop a Scatter Search meta-heuristic exploiting the specific facility location features of the problem. We test our algorithm over a broad set of benchmark instances and compare it with an exact solver and an efficient state-of-the-art heuristic method.

Quantum Speedup for Index Modulation

This paper presents a quantum-assisted index modulation for next-generation IoT wireless networks. The NP-hard index selection problem is first formulated by a quadratic unconstrained binary optimization (QUBO) problem consisting of constraints of feasible solutions. To minimize the number of qubits required for a quantum circuit, this formulation is then simplified by a dictionary-based approach that partially exploits a classical computer. For both formulations, the numbers of required qubits and non-zero elements in QUBO matrices are analyzed algebraically, and found to be in close agreement with the actual measurement. It is observed that the Grover adaptive search can provide the quantum speedup for the index selection problem. This promising result implies that the on-off structure of index modulation is suitable for quantum computation, and future fault-tolerant quantum computers may be useful for obtaining high-performance index activation patterns.

Magic mirror in my hand, which is the best in the land?

Indexes are essential for the efficient processing of database workloads. Proposed solutions for the relevant and challenging index selection problem range from metadata-based simple heuristics, over sophisticated multi-step algorithms, to approaches that yield optimal results. The main challenges are (i) to accurately determine the effect of an index on the workload cost while considering the interaction of indexes and (ii) a large number of possible combinations resulting from workloads containing many queries and massive schemata with possibly thousands of attributes. In this work, we describe and analyze eight index selection algorithms that are based on different concepts and compare them along different dimensions, such as solution quality, runtime, multi-column support, solution granularity, and complexity. In particular, we analyze the solutions of the algorithms for the challenging analytical Join Order, TPC-H, and TPC-DS benchmarks. Afterward, we assess strengths and weaknesses, infer insights for index selection in general and each approach individually, before we give recommendations on when to use which approach.

A Survey on Query Performance Optimization by Index Recommendation

language access data from databases. With exponential growth of data, optimization techniques need to be adopt for better results. Query performance tuning and optimization can be achieved by query reformation and index selection. Searching tuples from millions of results is overhead and it degrades overall system performance. To reduce searching time is goal of index recommendation. Index Selection Problem (ISP) is optimization problem. This is NPH problem and it can be solve by different approaches like greedy approach, dynamic programming, linear programming, branch and bound, genetic algorithm, etc. In general, indexing is done on candidate keys but it will not give assurance of optimal solution. Researchers tried to resemble ISP with knapsack problem and variation of it. Different data structure are used for indexing like tree, hash, bitmap, etc. In composite column indexes, order of columns affects overall performance In- memory databases are fast databases and new data structures to be suggest for indexing. Usually indexing is done on only columns which will yield profit in query execution. Join operations executions are discussed briefly.

An integer programming approach for the view and index selection problem

The view- and index-selection problem is a combinatorial optimization problem that arises in the context of on-line analytical processing (OLAP) in database-management systems. We propose an integer programming (IP) model for this problem and study the properties of the views and indexes that appear in the optimal solution for this model. We then use these properties to remove a number of variables and constraints from the corresponding IP model and obtain a model that is significantly smaller, yet its optimal solution is guaranteed to be optimal for the original problem. This allows us to solve realistic-size instances of the problem in reasonable time using commercial IP solvers. Subsequently, we propose heuristic strategies to further reduce the size of this IP model and dramatically reduce its execution time, although we no longer guarantee that the reduced IP model offers a globally optimal solution for the original problem. Finally, we carry out an extensive computational study to evaluate the effectiveness of these IP models for solving the OLAP view- and index-selection problem.

관계형 데이터 웨어하우스의 복잡한 질의의 처리 효율 향상을 위한 비트맵 조인 인덱스 선택에 관한 연구

As the size of the data warehouse is large, the selection of indices on the data warehouse affects the efficiency of the query processing of the data warehouse. Indices induce the lower query processing cost, but they occupy the large storage areas and induce the index maintenance cost which are accompanied by database updates. The bitmap join indices are well applied when we optimize the star join queries which join a fact table and many dimension tables and the selection on dimension tables in data warehouses. Though the bitmap join indices with the binary representations induce the lower storage cost, the task to select the indexing attributes among the huge candidate attributes which are generated is difficult. The processes of index selection are to reduce the number of candidate attributes to be indexed and then select the indexing attributes. In this paper on bitmap join index selection problem we reduce the number of candidate attributes by the data mining techniques. Compared to the existing techniques which reduce the number of candidate attributes by the frequencies of attributes we consider the frequencies of attributes and the size of dimension tables and the size of the tuples of the dimension tables and the page size of disk. We use the mining of the frequent itemsets as mining techniques and reduce the great number of candidate attributes. We make the bitmap join indices which have the least costs and the least storage area adapted to storage constraints by using the cost functions applied to the bitmap join indices of the candidate attributes. We compare the existing techniques and ours and analyze them in order to evaluate the efficiencies of ours.

Numerical constraints on XML data

Boundaries occur naturally in everyday life. This paper introduces numerical constraints into the framework of XML to take advantage of the benefits that result from the explicit specification of such boundaries. Roughly speaking, numerical constraints restrict the number of elements in an XML data fragment based on the data values of selected subelements. Efficient reasoning about numerical constraints provides effective means for predicting the number of answers to XQuery and XPath queries, the number of updates when using the XQuery update facility, and the number of encryptions or decryptions when using XML encryption. Moreover, numerical constraints can help to optimise XQuery and XPath queries, to exclude certain choices of indices from the index selection problem, and to generate views for efficient processing of common queries and updates. We investigate decision problems associated with numerical constraints in order to capitalise on the range of applications in XML data processing. To begin with we demonstrate that the implication problem is strongly coNP-hard for several classes of numerical constraints. These sources of potential intractability direct our attention towards the class of numerical keys that permit the specification of positive upper bounds. Numerical keys are of interest as they are reminiscent of cardinality constraints that are widely used in conceptual data modelling. At the same time, they form a natural generalisation of XML keys that are popular in XML theory and practice. We show that numerical keys are finitely satisfiable and establish a finite axiomatisation for their implication problem. Finally, we propose an algorithm that decides numerical key implication in quadratic time using shortest path methods.

An index selection method without repeated optimizer estimations

The index selection problem (ISP) concerns the selection of an appropriate index set to minimize the total cost for a given workload containing read and update queries. Since the ISP has been proven to be an NP-hard problem, most studies focus on heuristic algorithms to obtain approximate solutions. However, even approximate algorithms still consume a large amount of computing time and disk space because these systems must record all query statements and frequently request from the database optimizers the cost estimation of each query in each considered index. This study proposes a novel algorithm without repeated optimizer estimations. When a query is delivered to a database system, the optimizer evaluates the costs of various query plans and chooses an access path for the query. The information from the evaluation stage is aggregated and recorded with limited space. The proposed algorithm can recommend indexes according to the readily available information without querying the optimizer again. The proposed algorithm was tested in a PostgreSQL database system using TPC-H data. Experimental results show the effectiveness of the proposed approach.

A Data Mining Approach for Selecting Bitmap Join Indices

Index selection is one of the most important decisions to take in the physical design of relational data warehouses. Indices reduce significantly the cost of processing complex OLAP queries, but require storage cost and induce maintenance overhead. Two main types of indices are available: mono-attribute indices (e.g., B-tree, bitmap, hash, etc.) and multi-attribute indices (join indices, bitmap join indices). To optimize star join queries characterized by joins between a large fact table and multiple dimension tables and selections on dimension tables, bitmap join indices are well adapted. They require less storage cost due to their binary representation. However, selecting these indices is a difficult task due to the exponential number of candidate attributes to be indexed. Most of approaches for index selection follow two main steps: (1) pruning the search space (i.e., reducing the number of candidate attributes) and (2) selecting indices using the pruned search space. In this paper, we first propose a data mining driven approach to prune the search space of bitmap join index selection problem. As opposed to an existing our technique that only uses frequency of attributes in queries as a pruning metric, our technique uses not only frequencies, but also other parameters such as the size of dimension tables involved in the indexing process, size of each dimension tuple, and page size on disk. We then define a greedy algorithm to select bitmap join indices that minimize processing cost and verify storage constraint. Finally, in order to evaluate the efficiency of our approach, we compare it with some existing techniques.

Index selection for databases: a hardness study and a principled heuristic solution

We study the index selection problem: Given a workload consisting of SQL statements on a database, and a user-specified storage constraint, recommend a set of indexes that have the maximum benefit for the given workload. We present a formal statement for this problem and show that it is computationally hard to solve or even approximate it. We develop a new algorithm for the problem which is based on treating the problem as a knapsack problem. The novelty of our approach lies in an LP (linear programming) based method that assigns benefits to individual indexes. For a slightly modified algorithm, that does more work, we prove that we can give instance specific guarantees about the quality of our solution. We conduct an extensive experimental evaluation of this new heuristic and compare it with previous solutions. Our results demonstrate that our solution is more scalable while achieving comparable quality.

Separating lifted odd-hole inequalities to solve the index selection problem

The Index Selection Problem (ISP) is a phase of fundamental importance in the physical design of databases, calling for a set of indexes to be built in a database so as to minimize the overall execution time for a given database workload. The problem is a generalization of the well-known Uncapacitated Facility Location Problem (UFLP). In an earlier publication [A. Caprara, J.J. Salazar González, TOP 4 (1996) 135–163], we formulate ISP as a set packing problem, showing that our mathematical model contains all the clique inequalities, and describe a branch-and-cut algorithm based on the separation of odd-hole inequalities. In this paper, we describe an effective exact separation procedure for a suitably-defined family of lifted odd-hole inequalities, obtained by applying a Chvátal-Gomory derivation to the clique inequalities. Our analysis goes in the direction of determining a new class of inequalities over which efficient separation is possible, rather than introducing new classes of (facet-defining) inequalities that later turn out to be difficult to separate. Our separation procedure is embedded within our branch-and-cut algorithm for the exact solution of ISP. Computational results on two different classes of instances are given, showing the effectiveness of the new approach. We also test our algorithm on UFLP instances both taken from the literature and randomly generated.

Near optimal multiple choice index selection for relational databases

Index selection for relational databases is an important issue which has been researched quite extensively [1–5]. In the literature, in index selection algorithms for relational databases, at most one index is considered as a candidate for each attribute of a relation. However, it is possible that more than one different type of indexes with different storage space requirements may be present as candidates for an attribute. Also, it may not be possible to eliminate locally all but one of the candidate indexes for an attribute due to different benefits and storage space requirements associated with the candidates. Thus, the algorithms available in the literature for optimal index selection may not be used when there are multiple candidates for each attribute and there is a need for a global optimization algorithm in which at most one index can be selected from a set of candidate indexes for an attribute. The problem of index selection in the presence of multiple candidate indexes for each attribute (which we call the multiple choice index selection problem) has not been addressed in the literature. In this paper, we present the multiple choice index selection problem, show that it is NP-hard, and present an algorithm which gives an approximately optimal solution within a user specified error bound in a logarithmic time order.

A branch-and-cut algorithm for a generalization of the Uncapacitated Facility Location Problem

We introduce a generalization of the well-know Uncapacitated Facility Location Problem, in which clients can be served not only by single facilities but also by sets of facilitities. The problem, calledGaneralized Uncapacitated Facility Lacition Problem (GUFLP), was inspired by the Index Selection Problem in physical database design. We for mulate GUFLP as a Set Packing Problem, showing that our model contains all the clique inequalities (in polynomial number). Moreover, we describe and exact separation procedure for odd-hole inequalities, based on the particular structure of the problem. These results are used within a branch-and-cut algorithm for the exact solution of GUFLP. Computational results on two different classes of test problems are given.

Exact and approximate algorithms for the index selection problem in physical database design

The index selection problem (ISP) is an important optimization problem in the physical design of databases. The aim of this paper is to show that ISP, although NP-hard, can in practice be solved effectively through well-designed algorithms. We formulate ISP as a 0-1 integer linear program and describe an exact branch-and-bound algorithm based on the linear programming relaxation of the model. The performance of the algorithm is enhanced by means of procedures to reduce the size of the candidate index set. We also describe heuristic algorithms based on the solution of a suitably defined knapsack subproblem and on Lagrangian decomposition. Finally, computational results on several classes of test problems are given. We report the exact solution of large-scale ISP instances involving several hundred indexes and queries. We also evaluate one of the heuristic algorithms we propose on very large-scale instances involving several thousand indexes and queries and show that it consistently produces very tight approximate (and sometimes provably optimal) solutions. Finally, we discuss possible extensions and future directions of research.

On the selection of secondary indices in relational databases

An important problem in the physical design of databases is the selection of secondary indices. In general, this problem cannot be solved in an optimal way due to the complexity of the selection process. Often use is made of heuristics such as the well-known ADD and DROP algorithms. In this paper it will be shown that frequently used cost functions can be classified as super- or submodular functions. For these functions several mathematical properties have been derived which reduce the complexity of the index selection problem. These properties will be used to develop a tool for physical database design and also give a mathematical foundation for the success of the before-mentioned ADD and DROP algorithms.

Comments on an optimal set of indices for a relational database

M. Y. L. Ip et al., (see ibid., vol.SE-9, p.135-43, 1983) solved the index selection problem for a relational database by reducing it to a classical knapsack problem and then applying an approximation algorithm. It is shown that this reduction process does not work in general by providing a counterexample, and its practical significance is discussed. It turns out that the main idea of Ip et al. need not be discarded. In particular, the approximation algorithm used can be adapted fairly easily to take care of the problems which were raised by the counterexample. In spite of its simplicity, this modification can lead to a reduced number of indices, which is rather attractive from a practical point of view. >

Optimal selection of secondary indexes

When planning a database, the problem of index selection is of particular interest. The authors examine a transaction model that includes queries, updates, insertions, and deletions, and they define a function that calculates the transaction's total cost when an index set is used. Their aim is to minimize the function cost in order to identify the optimal set. The algorithms proposed in other studies require an exponential time in the number of attributes in order to solve the problem. The authors propose a heuristic algorithm based on some properties of the cost function that produces an almost optimal set in polynomial time. In many cases, the cost function properties make it possible to prove that the solution obtained is the optimal one. >

Selection of indexes to memory-resident entities for semantic data models

A variation of the index selection problem for an extended relational model when all encoding of information is memory resident is discussed. The data model is the relational model extended in two ways that are common with semantic data models. One consequence of memory residence is that the search space of possible indexes is enlarged to the extent that previous methods requiring some consideration of each possibility are no longer possible. An instance of the index selection problem that includes a set of partial match queries in addition to the input schema is given. It is assumed that the set is determined by an initial phase of query optimization when applied to a fixed set of more general forms of queries that characterize the way in which information is accessed for an application. An initial choice of indexes is made, only considering their suitability for answering the partial match queries.< <ETX xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:xlink="http://www.w3.org/1999/xlink">&gt;</ETX>

Physical database design for relational databases

This paper describes the concepts used in the implementation of DBDSGN, an experimental physical design tool for relational databases developed at the IBM San Jose Research Laboratory. Given a workload for System R (consisting of a set of SQL statements and their execution frequencies), DBDSGN suggests physical configurations for efficient performance. Each configuration consists of a set of indices and an ordering for each table. Workload statements are evaluated only for atomic configurations of indices, which have only one index per table. Costs for any configuration can be obtained from those of the atomic configurations. DBDSGN uses information supplied by the System R optimizer both to determine which columns might be worth indexing and to obtain estimates of the cost of executing statements in different configurations. The tool finds efficient solutions to the index-selection problem; if we assume the cost estimates supplied by the optimizer are the actual execution costs, it finds the optimal solution. Optionally, heuristics can be used to reduce execution time. The approach taken by DBDSGN in solving the index-selection problem for multiple-table statements significantly reduces the complexity of the problem. DBDSGN's principles were used in the Relational Design Tool (RDT), an IBM product based on DBDSGN, which performs design for SQL/DS, a relational system based on System R. System R actually uses DBDSGN's suggested solutions as the tool expects because cost estimates and other necessary information can be obtained from System R using a new SQL statement, the EXPLAIN statement. This illustrates how a system can export a model of its internal assumptions and behavior so that other systems (such as tools) can share this model.