Microsoft Technology Licensing, LLC

Mayur D Datar

Vivek Ravindranath Narasayya

Surajit J Chaudhuri

Rajeev Motwani

Sarajit Chaudhuri

An outlier index for a database and a given workload is generated by identifying sub-relations of tuples in the database induced by selection and group by conditions in queries in the workload. A variance is then generated for values in each sub-relation. Sub-relations having higher variances are selected, and outliers from such sub-relations having higher variances are generated.

Sampling for queries

A method of estimating results of a database query, the results are estimated by performing a sampling of weighted tuples in a database based on a probability of usage of tuples required in executing a workload. A probability is associated with each tuple sampled. An aggregate is computed over values in each sampled tuple while multiplying by the inverses of the probabilities associated with each tuple sampled.

Aggregation queries are performed by first identifying outlier values, aggregating the outlier values, and sampling the remaining data after pruning the outlier values. The sampled data is extrapolated and added to the aggregated outlier values to provide an estimate for each aggregation query. Outlier values are identified by selecting values outside of a selected sliding window of data having the lowest variance. An index is created for the outlier values. The outlier data is removed from the window of data, and separately aggregated. The remaining data without the outliers is then sampled to provide a statistically relevant sample that is then aggregated and extrapolated to provide an estimate for the remaining data. This sampled estimate is combined with the outlier aggregate to form an estimate for the entire set of data.

Database aggregation query result estimator

This disclosure describes leveraging workload information associated with executed database queries for estimating the result of a current database query. The workload information is analyzed to determine the usage of tuples in a database during query execution, such as how often a tuple is accessed and the number of different queries that accessed the tuple. A tuple is assigned a weight value that is based on the analyzed workload information. The particular tuples sampled for estimating a result for the current query is based on each tuple's weight value. The workload information may also be leveraged to generate an outlier index that identifies outlier tuples associated with the executed queries or that identifies outlier tuples associated with particular queries that are executed more frequently than other queries. The result for the current query can also be estimated using the sampled values along with the outlier tuples from the outlier index.

Aggregation queries are performed by first identifying outlier values, aggregating the outlier values, and sampling the remaining data after pruning the outlier values. The sampled data is extrapolated and added to the aggregated outlier values to provide an estimate for each aggregation query. Outlier values are identified by selecting values outside of a selected sliding window of data having the lowest variance. An index is created for the outlier values. The outlier data is removed from the window of data, and separately aggregated. The remaining data without the outliers is then sampled in one of many known ways to provide a statistically relevant sample that is then aggregated and extrapolated to provide an estimate for the remaining data. This sampled estimate is combined with the outlier aggregate to form an estimate for the entire set of data. Further methods involve the use of weighted sampling and weighted selection of outlier values for low selectivity queries, or queries having group by.

In a relational database system, a set of physical design structures is enumerated that optimizes database performance over a given workload consisting of workload entries that include queries and updates that have been executed against the database. An individual benefit is calculated for each candidate structure relevant to a given workload entry and these individual benefits are summed over the entries in the workload examined thus far. A workload entry is selected from the workload and a set of candidate structures relevant to the workload entry is identified. A benefit is assigned to each of the candidate structures by 1) evaluating the workload entry in the presence of subsets of candidate structures to obtain an actual benefit for each subset of candidate structures; 2) forming a set of constraints on the structure benefits of candidate structures based on the actual benefits determined for each subset of candidate structures; and 3) determining the individual benefit of each candidate structure by resolving the constraints. The set of physical design structures is enumerated based on the determined benefit for each candidate structure.

Growing community of inventors

Stanford, CA, United States of America

Mayur D Datar