The like regular expression predicate has been part of the SQL standard since at least 1989. However, despite its popularity and wide usage, database vendors provide only limited indexing support for regular expression queries which almost always require a full table scan. In this paper we propose a rigorous and robust approach for providing indexing support for regular expression queries. Our approach consists of formulating the indexing problem as a combinatorial optimization problem. We begin with a database, abstracted as a collection of strings. From this data set we generate a query workload. The input to the optimization problem is the database and the workload. The output is a set of multigrams (substrings) which can be used as keys to records which satisfy the query workload. The multigrams can then be integrated with the data structure (like B+ trees) to provide indexing support for the queries. We provide a deterministic and a randomized approximation algorithm (with provable guarantees) to solve the optimization problem. Extensive experiments on synthetic data sets demonstrate that our approach is accurate and efficient. We also present a case study on PROSITE patterns - which are complex regular expression signatures for classes of proteins. Again, we are able to demonstrate the utility of our indexing approach in terms of accuracy and efficiency. Thus, perhaps for the first time, there is a robust and practical indexing mechanism for an important class of database queries.
Hore formalizes the multigram selection problem as an instance of the Budgeted Maximum Coverage (BMC) @cite_5 problem. Specifically the cover set forms the Budgeted' part while the utility of the index is captured in the Coverage' component. The main principle of the BEST algorithm is to select multigrams which increase the index hit rate so more queries can utilize the algorithm. However, a major weakness of the BEST algorithm is that it neither scales to large datasets nor large query workloads because it uses a cartesian product of query workload and database as the search space.
@cite_5
2080379754
In this paper we describe novel techniques that make it possible to build an industrial-strength tool for automating the choice of indexes in the physical design of a SQL database. The tool takes as input a workload of SQL queries, and suggests a set of suitable indexes. We ensure that the indexes chosen are effective in reducing the cost of the workload by keeping the index selection tool and the query optimizer "in step". The number of index sets that must be evaluated to find the optimal configuration is very large. We reduce the complexity of this problem using three techniques. First, we remove a large number of spurious indexes from consideration by taking into account both query syntax and cost information. Second, we introduce optimizations that make it possible to cheaply evaluate the “goodness” of an index set. Third, we describe an iterative approach to handle the complexity arising from multicolumn indexes. The tool has been implemented on Microsoft SQL Server 7.0. We performed extensive experiments over a range of workloads, including TPC-D. The results indicate that the tool is efficient and its choices are close to optimal. 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. We present a novel approach for a tool that assists the database administrator in designing an index configuration for a relational database system. A new methodology for collecting usage statistics at run time is developed which lets the optimizer estimate query execution costs for alternative index configurations. Defining the workload specification required by existing index design tools may be very complex for a large integrated database system. Our tool automatically derives the workload statistics. These statistics are then used to efficiently compute an index configuration. Execution of a prototype of the tool against a sample database demonstrates that the proposed index configuration is reasonably close to the optimum for test query sets. Biometric features are extracted from a complex pattern and stored as high dimensional data. These data do not follow traditional sorting order like numerical and alphabetical data. Hence, a linear search method makes the identification process extremely slow as well as increases the false acceptance rate beyond an acceptable range. To address this problem, we propose an efficient indexing mechanism to retrieve iris biometric templates using Gabor energy features. The Gabor energy features are calculated from the preprocessed iris texture in different scales and orientations to generate a 12-dimensional index key for an iris template. An index space is created based on the values of index keys of all individuals. A candidate set is retrieved from the index space based on the values of query index key. Next, we rank the retrieved candidates according to their occurrences. If the identity of the query template is matched, then it is a hit, otherwise a miss. We have experimented our approach with Bath, CASIA-V3-Interval, CASIA-V4-Thousand, MMU2, and WVU iris databases. Our proposed approach gives 11.3 , 14.5 , 16.3 , 13.5 , and 10.3 penetration rates and 98.2 , 91.1 , 90.7 , 85.2 , and 96 hit rates for Bath, CASIA-V3-Interval, CASIA-V4-Thousand, MMU2, and WVU iris database, respectively, when we consider the retrieving templates up to the fifth rank. Experiments substantiate that our approach is capable of retrieving biometric data with a higher hit rate and lower penetration rate compared to the existing approaches. Application of Gabor energy features to index iris data proves to be effective for fast and accurate retrieval. With our proposed approach, it is possible to retrieve a set of iris templates similar to the query template in the order of milliseconds and is independent of sizes of databases.