Index advisor for star-join queries based on the semantics of star schema
Original Publication Date: 2002-Oct-16
Included in the Prior Art Database: 2003-Jun-21
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.