Stringent performance requirements in DB applications have led to the use of parallelism for database processing. To allow the database system to take advantage of the performance of parallel shared-nothing systems, the physical DB design must be appropriate for the DB structure and the workload. We develop decision algorithms that will select a good physical DB design both when the DB is first loaded into the system (static decision) and while the DB is being used by the workload (dynamic decision). Our decision algorithms take the database structure, workload, and system characteristics as inputs. The static (or initial) physical DB design decision algorithm involves: • selecting a partitioning attribute for each relation that determines how the relation is fragmented across the nodes (allowing for high I/O bandwidth); • selecting indexes on the relation attributes to allow faster accesses compared to sequential file scans; • selecting the attributes by which to cluster a relation in order to take advantage of the prefetching and caching involved in I/O access; • grouping of relations to allow DB operations (joins) on relation pairs to be executed locally

An important problem in the physical design of databases is the selection of secondary indices. In general, this problem can not 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. Keywords: Physical database design, Secondary index selection, ADD and DROP algorithms, Supermodular functions, Submodular functions. 1 Introduction Physical database design is an important step in designing databases and aims to generate efficient storage structures for the data....

Abstract. In relational database management systems indexes are used to accelerate specific queries. The selection of indexes is an important task when tuning a database which is performed by a database administrator or an index propagation tool which suggests a set of suitable indexes. In this paper we introduce a new index approach, called keyoriented XML index (KeyX), that uses specific XML element or attribute values as keys referencing arbitrary nodes in the XML data. KeyX is selective to specific queries avoiding efforts spent for elements which are never queried. This concept reduces memory consumption and unproductive index updates. We transfer the Index Selection Problem (ISP) to XDBMS. Applying the ISP, a workload of database operations is analyzed and a set of selective indexes that minimizes the total execution time for the workload is suggested. Because the workload is analyzed periodically and suitable indexes are created or dropped automatically our implementation of KeyX guarantees high performance over the total life time of a database. 1

The design of an optimal physical database entails an exponential time complexity. This is the main reason which has shifted the attention of researchers to the problem of determining a good physical database design. Two kinds of approaches have been proposed to find a good physical design, an optimizer and a knowledge based approach. An optimizer based approach is characterized by generating physical designs on basis of extracted information from the optimizer. The knowledge based approach is characterized by representing the knowledge of experts, not necessarily knowledge with regard to optimizers, into rules. In this paper we will propose a framework of a tool based on a combination of both approaches which can assist in finding a good physical design. In our approach the emphasis lies on the knowledge based aspects such as how to represent knowledge into rules and how to combine different rules. Knowledge based approaches for physical design take ad hoc decisions for these subjects...

In recent years the support for index tuning as part of physical database design has gained focus in research and product development, which resulted in index and design advisors. Nevertheless, these tools provide a one-off solution for a continuous task and are not deeply integrated with the DBMS functionality by only applying the query optimizer for index recommendation and profit estimation and decoupling the decision about and execution of index configuration changes from the core system functionality. In this paper we propose an approach that continuously collects statistics for recommended indexes and based on this, repetitively solves the Index Selection Problem (ISP). A key novelty is the on-the-fly index generation during query processing implemented by new query plan operators IndexBuildScan and SwitchPlan. Finally, we present the implementation and evaluation of the introduced concepts as part of the PostgreSQL system. 1.

Abstract. In the world of Relational Database Management Systems (RDBMS) indexes are used to accelerate specific queries. The selection of indexes is an important task in database-tuning which is performed by a database administrator or an index selection tool which suggests a set of suitable indexes. In this paper we transfer the concept of specific indexes to XML Database Management Systems (XDBMS) and present an implementation that uses occurring queries to optimize the performance of an XML database system by automatically creating suitable indexes. We introduce an index approach, called key oriented XML index, that uses specific XML element values and attribute values as keys referencing arbitrary nodes in the data. We transfer the wellknown Index Selection Problem (ISP) to XDBMS. Solving the ISP, a workload of database operations is analyzed and a set of specific indexes that minimizes the total execution time is suggested. Because the ISP is an NP complete problem, we apply heuristics to find a solution with reduced complexity. Experimental results of the prototypical implementation of the key oriented XML indexes on top of a native XDBMS demonstrate that our approach significantly improves the query execution time with only moderate additional storage requirements. Because the workload is analyzed periodically and suitable indexes are created or dropped automatically by solving the ISP, our approach guarantees high performance over the total life time of a database. 1

Defining suitable indexes is a major task when optimizing a database. Usually, a human database administrator defines a set of indexes in the design phase of the database. This can be done manually or with the help of so called index wizard tools analyzing predefined database operations. Even having an optimal initial set of indexes when setting up a database, there is no guarantee that these indexes will suit future demands. Rather, it is realistic that the typical usage of the database will change after a while because new queries appear, for instance. In consequence, the existing indexes are suboptimal. The typical way to handle this problem is that a database administrator maintains the database permanently. In XML database management systems (XDBMS) this problem becomes even worse: Because XML queries cover both content and structure the number of possible queries and indexes is significantly higher. Additionally, for XML data without schema information, queries and indexes cannot be defined in advance, because the structure and the content of the data is not restricted. Both facts tend to result in higher maintenance costs for XML indexes compared to relational indexes. In this paper we show by performance measurements that an adaptive XDBMS that analyzes its workload periodically and creates/drops XML indexes automatically guarantees a high performance over the total life time of a database. Although we present our index system called KeyX the idea and the results are transferable to other XML indexing approaches.

A common problem in using and running RDBMS is performance, which highly depends on the quality of the database schema design and the resulting structure of the tables and the logical relations between them. In production reality, the performance mainly depends on the data that is stored in an RDBMS and on the semantics comprised in the data and the corresponding queries. We implemented a system based on SWI-Prolog. The system is capable of importing any relational database schema and any SQL statement from an XML representation. This information can be queried and transformed, thus allowing modification and dynamic processing of the schema data. Visualization of relationships and join paths is integrated, too. Using an online connection, SQUASH queries current data from the RDBMS, such as join selectivities or table sizes. The system allows for tuning the database schema according to the load profile induced by the application. SQUASH proposes changes in the database schema such as the creation of indexes, partitioning, splitting or further normalization. SQL statements are adapted simultaneously upon modification of the schema. SQL statements are rewritten in consideration of RDBMS-specific rules for processing statements including the integration of optimizer hints. The resulting statements are executed more efficiently, thus reducing application response times. 1

Index structures are a widely used function of Database Management Systems (DBMS) in order to tune them for a special purpose. Finding the right index configuration is on the one hand extremely complex (NP-problem [6]) and a variation of the Knapsack Problem [9] but on the other hand manual configuration requires high administrative effort by cost-intensive experts

Materialized views and indexes are physical structures for accelerating data access that are casually used in data warehouses. However, these data structures generate some maintenance overhead. They also share the same storage space. Most existing studies about materialized view and index selection consider these structures separately. In this paper, we adopt the opposite stance and couple materialized view and index selection to take view-index interactions into account and achieve efficient storage space sharing. Candidate materialized views and indexes are selected through a data mining process. We also exploit cost models that evaluate the respective benefit of indexing and view materialization, and help select a relevant configuration of indexes and materialized views among the candidates. Experimental results show that our strategy performs better than an independent selection of materialized views and indexes. Keywords: Data mining, Cost models.