Abstract: A method for providing workload information in a structured workload information data structure format that is organized according to a workload schema to be conducive to a given end usage of the information. The structured workload information can be made accessible using standard database analytical server applications to facilitate ad-hoc querying of the structured workload information to summarize and analyze the database workload or to facilitate exchange of workload information. A structured workload information (SWI) is constructed according to a SWI schema to facilitate a desired end usage of the workload information. The query information is extracted from the workload and stored in a structured workload information (SWI) data structure according to the schema based on the desired end usage of the information such as ad hoc querying or information exchange.

Abstract: A database server supports weighted and unweighted sampling of records or tuples in accordance with desired sampling semantics such as with replacement (WR), without replacement (WoR), or independent coin flips (CF) semantics, for example. The database server may perform such sampling sequentially not only to sample non-materialized records such as those produced as a stream by a pipeline in a query tree for example, but also to sample records, whether materialized or not, in a single pass. The database server also supports sampling over a join of two relations of records or tuples without requiring the computation of the full join and without requiring the materialization of both relations and/or indexes on the join attribute values of both relations.

Abstract: A database server supports weighted and unweighted sampling of records or tuples in accordance with desired sampling semantics such as with replacement (WR), without replacement (WoR), or independent coin flips (CF) semantics, for example. The database server may perform such sampling sequentially not only to sample non-materialized records, such as those produced as a stream by a pipeline in a query tree for example, but also to sample records, whether materialized or not, in a single pass. The database server also supports sampling over a join of two relations of records or tuples without requiring the computation of the full join and without requiring the materialization of both relations and/or indexes on the join attribute values of both relations.

Abstract: In a database system, a method for determining a set of essential statistics. A set of potentially relevant, but unconstructed, statistics for a user query comprising a plurality of operators is compiled. A first value is assigned to the unconstructed statistics and a first projected query cost is computed for the query wherein the unconstructed statistics have the first value. A second value is assigned to the unconstructed statistics and a second projected query cost is computed for the query wherein the unconstructed statistics have the second value. The first and second projected query costs are compared and the determination of essential statistics is discontinued if the first and second projected query costs are equivalent. If the first and second projected query costs are not equivalent, an unconstructed statistic is selected to build and process is repeated until the first and second projected query costs are equivalent or all the unconstructed potentially relevant statistics have been constructed.

Abstract: A database server supports weighted and unweighted sampling of records or tuples in accordance with desired sampling semantics such as with replacement (WR), without replacement (WoR), or independent coin flips (CF) semantics, for example. The database server may perform such sampling sequentially not only to sample non-materialized records, such as those produced as a stream by a pipeline in a query tree for example, but also to sample records, whether materialized or not, in a single pass. The database server also supports sampling over a join of two relations of records or tuples without requiring the computation of the full join and without requiring the materialization of both relations and/or indexes on the join attribute values of both relations.

Abstract: A method for estimating the result of a query on a database wherein the database has data records arranged in tables and the database has a given workload consisting of queries that have been executed on the database. An expected workload is derived comprising a set of queries that can be executed on the database. A sample of a table is constructed by partitioning the data records into regions based on the queries in the expected workload such that no query in the expected workload selects a proper subset of any region. The estimation error is expressed as a function of a number of records selected per region and a number of records is allocated to each region in a manner that minimizes the estimation error. The query accesses the sample and is executed on the sample, returning an estimated query result.

Abstract: An essential statistics identification utility tool attempts to reduce or minimize the overhead associated with statistics by identifying from an initial set of statistics a set of essential statistics that provide a query optimizer with the ability to choose among query execution plans with minimized loss in accuracy as compared to using the initial set of statistics. The set of essential statistics is identified as a subset of the initial set of statistics that is equivalent to the initial set of statistics with respect to each query of a workload. The subset of statistics is equivalent to the initial set of statistics if an execution plan for each query using the subset of statistics is the same as an execution plan for that query using the initial set of statistics and/or if a cost estimate to execute each query against the database using the subset of statistics is within a predetermined amount of a cost estimate to execute that query against the database using the initial set of statistics.

Abstract: Using adaptive random sampling with cross-validation helps determine when enough data of a database has been sampled to construct histograms on one or more columns of one or more tables of the database within a desired or predetermined degree of accuracy. An adaptive random sampling histogram construction tool constructs an approximate equi-height k-histogram using an initial sample of data values from the database and iteratively updates the histogram using an additional sample of data values from the database until the histogram is within the desired degree of accuracy. The accuracy of the histogram is cross-validated against the additional sample at each iteration, and the additional sample is used to update the histogram to help improve its accuracy. The accuracy of the histogram may be measured by an error in distribution of the additional sample over the histogram as compared to a threshold error using a suitable error metric.

Abstract: What-if index analysis utility provides the ability to analyze the performance of the existing configuration of a database system with respect to one or more workloads of queries and to propose a hypothetical configuration for the database system to analyze its potential impact on the performance of the database system. The utility may be used, for example, to perform an impact analysis of the set of indexes selected by an index selection tool, for example, with respect to a workload of queries and may also be used to explore what-if scenarios for the database system by analyzing the impact of hypothetical sets of indexes with respect to the execution of various workloads over projected sizes of a database. The utility may be used to perform summarizations of workloads, configurations, and the performance of workloads with respect to the existing configuration and hypothetical configurations.

Abstract: An index merge tool helps form, for use by a database server in accessing a database in accordance with a workload of queries, an index configuration or set of indexes that consumes relatively less storage space. The index merge tool identifies from an initial set of indexes one or more combinations of two or more indexes on the same table of the database and merges each identified combination of indexes to form a merged set of indexes. The index merge tool identifies and merges each combination of indexes by identifying and merging one pair of indexes at a time. The index merge tool uses the merged set of indexes as the index configuration for use in executing queries against the database so long as the storage saved by the merged set of indexes exceeds a threshold amount and so long as any increase in the cost to execute queries against the database using the merged set of indexes is limited. Otherwise, the index merge tool uses the initial set of indexes as the index configuration.

Abstract: An index selection tool helps reduce costs in time and memory in selecting an index configuration or set of indexes for use by a database server in accessing a database in accordance with a workload of queries. The index selection tool attempts to reduce the number of indexes to be considered, the number of index configurations to be enumerated, and the number of invocations of a query optimizer in selecting an index configuration for the workload.

Abstract: An index selection tool helps reduce costs in time and memory in selecting an index configuration or set of indexes for use by a database server in accessing a database in accordance with a workload of queries. The index selection tool attempts to reduce the number of indexes to be considered, the number of index configurations to be enumerated, and the number of invocations of a query optimizer in selecting an index configuration for the workload.

Abstract: An index selection tool helps reduce costs in time and memory in selecting an index configuration or set of indexes for use by a database server in accessing a database in accordance with a workload of queries. The index selection tool attempts to reduce the number of indexes to be considered, the number of index configurations to be enumerated, and the number of invocations of a query optimizer in selecting an index configuration for the workload.

Abstract: An index selection tool helps reduce costs in time and memory in selecting an index configuration or set of indexes for use by a database server in accessing a database in accordance with a workload of queries. The index selection tool attempts to reduce the number of indexes to be considered, the number of index configurations to be enumerated, and the number of invocations of a query optimizer in selecting an index configuration for the workload.

Abstract: An index selection tool helps reduce costs in time and memory in selecting an index configuration or set of indexes for use by a database server in accessing a database in accordance with a workload of queries. The index selection tool attempts to reduce the number of indexes to be considered, the number of index configurations to be enumerated, and the number of invocations of a query optimizer in selecting an index configuration for the workload.