Browse Prior Art Database

Index advisor for star-join queries based on the semantics of star schema

IP.com Disclosure Number: IPCOM000016086D
Original Publication Date: 2002-Oct-16
Included in the Prior Art Database: 2003-Jun-21
Document File: 2 page(s) / 47K

Publishing Venue

IBM

Abstract

This disclosure describes an efficient architecture and algorithm for the star join index advisor that generates a recommendation for indexes which are critical to the performance of a set of star-join queries defined on a workload.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 52% of the total text.

Page 1 of 2

Index advisor for star-join queries based on the semantics of star schema

  This disclosure describes an efficient architecture and algorithm for the star join index advisor that generates a recommendation for indexes which are critical to the performance of a set of star-join queries defined on a workload.

A typical star schema database involves one or more fact tables with a large number of records. Due to the size of the fact table, the use of indexes is critical for the query performance. On the other hand, excessive number of indexes increases the DASD consumption and maintenance overhead for index creation, table reorganization, and insert, delete and update operations. Therefore, how to determine the optimal set of indexes for a certain workload is essential to the design of data warehousing systems.

Many database vendors provide index advisors for designing indexes on general workloads. These solutions are often insufficient for star-join queries because:

Certain statistics are critical for the estimate of dimension selectivity and the correlation among dimensions. Dimension selectivity and correlation are, in turn, critical to the selection of dimensions which are joined with the fact table for screening. The existing index advisors are based on general predicate analysis which often leads to unsatisfactory results for queries of special semantics, such as star schema queries.

The New Method

The two main characteristics of the new methods are summarized below.

    A statistics advisor is combined to enhance the decision made by the index advisor. The statistics will only be selected for a small subset of possible indexes which results in a big time saving during statistic collection time. The architecture and algorithm of this invention is a semantic based. It is highly specialized for star schema. The usage of the dimension reduction ratio is the foundation of this enumeration algorithm. With this architecture and algorithm proposed in this invention, the quality of the recommendation is expected to be significantly improved and the elapsed time for the index advisor will be significantly reduced, especially for large business warehouse workloads.

The new star join index advisor consists of the GUI part and the RDBMS server enhancements. The server enhancements include the supporting algorithms to identify possible indexes in the star join...