公开(公告)号：CA2374271A1

公开(公告)日：2003-09-01

申请号：CA2374271

申请日：2002-03-01

Applicant: IBM CANADA

Inventor： KOO FRED ,  LEUNG TING Y

IPC: G06F17/30

Abstract: A query rewrite optimization method for eliminating a redundant join and equivalent subquery in an SQL query before generation and selection of the optimal quer y execution plan. The method evaluates a query for a particular join of a first and second quantifier each ranging over the same table. A determination is made whether the row set produced relative to the second quantifier subsumes the row set produced relative to the first quantifier. The query ma y be reformed to eliminate the join and the second quantifier. A further determination of the removability of the second quantifier may be required such as by evaluating a of a cardinality constraint when query output cardinality is material.

公开(公告)号：CA2365692A1

公开(公告)日：2002-12-21

申请号：CA2365692

申请日：2001-12-19

Applicant: IBM

Inventor： ZILIO DANIEL C ,  CHEUNG KIT MAN ,  LIGHTSTONE SAM S ,  ZUZARTE CALISTO P ,  BEYER KEVIN S ,  VALENTIN GARY ,  ZAHARIOUDAKIS MARKOS ,  LEUNG TING Y ,  LOHMAN GUY M ,  SIMMEN DAVID E ,  PIRAHESH MIR HAMID ,  COCHRANE ROBERTA J

IPC: G06F17/30 ,  G06F17/60

Abstract: The invention herein provides method and apparatus, including software for determining a set of materialized views or indices of the contents or a subset of the contents of a database in a data processing system to be created for one or more users of the database. The method and apparatus provide method and means for evaluating a workload presented by a user to th e database; evaluating the data processing system characteristics; evaluating the databa se characteristics; and, using the above evaluations for recommending a set of suitable materialized views or indices to the user. Another aspect of the invention, which may be used for a workload presented by a user of a database in a data processing system, provides method and apparatus, including software for determining a set of materialized views or indices of the contents or a subset of the contents of the database, by: generating a plurality of materialized view candidates from evaluation of the workload, data processing system characteristics and database characteristics; estimating statistics for the materialized view candidates such as the numbe r of rows, row size, and column statistics; generating a plurality of potential index candidates by evaluating the workload, data processing system characteristics, database characteristics a nd the materialized view candidates; and, from the materialized view candidates and index candidates selecting a set of suitable materialized views and/or indices for submission to the user.