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 the
database; evaluating the
data processing system characteristics; evaluating the database 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 number of rows, row size, and column statistics; generating a plurality of potential index candidates by evaluating the workload,
data processing system characteristics, database characteristics and 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.