Estimating aggregates in time-constrained approximate queries in Oracle

Abstract

The concept of time-constrained SQL queries was introduced to address the problem of long-running SQL queries. A key approach adopted for supporting time-constrained SQL queries is to use sampling to reduce the amount of data that needs to be processed, thereby allowing completion of the query in the specified time constraint. However, sampling does make the query results approximate and hence requires the system to estimate the values of the expressions (especially aggregates) occurring in the select list. Thus, coming up with estimates for aggregates is crucial for time-constrained approximate SQL queries to be useful, which is the focus of this paper. Specifically, we address the problem of estimating commonly occurring aggregates (namely, SUM, COUNT, AVG, MEDIAN, MIN, and MAX) in timeconstrained approximate queries. We give both point and interval estimates for SUM, COUNT, AVG, and MEDIAN using Bernoulli sampling for various type of queries, including join processing with cross product sampling. For MIN (MAX), we give the confidence level that the proportion 100γ% of the population will exceed the MIN (or be less than the MAX) obtained from the sampled data.

Database Learning

“…Besides a few limited benefits (see Previous Approaches below), the work (both I/O and computation) performed for answering past queries is often wasted afterwards. However, in an approximate query processing context (e.g., [6,19,34,36,66,85]), one might be able to change this paradigm and reuse much of the previous work done by the database system based on the following observation: * This manuscript is an extended report of the work published in ACM SIGMOD conference 2017.…”

The analytical bootstrap

“…There has been a large body of research on using sampling to provide quick answers to database queries, on database systems [9,15,16,22,23,24,25,33,44], and data stream systems [12,31]. Approximate aggregate processing has been the focus of many of these works, which study randomized joins [24], optimal sample construction [9,16], sample reusing [44], and sampling plan in a stream setting [12,31].…”

“…Approximate aggregate processing has been the focus of many of these works, which study randomized joins [24], optimal sample construction [9,16], sample reusing [44], and sampling plan in a stream setting [12,31]. Most of them use statistical inequalities and the central limit theorem to model the confidence interval or variance of the approximate aggregate answers [9,16,22,23,24,44]. Recently, Pansare et al [33] develop a very sophisticated Bayesian framework to infer the confidence bounds of approximate aggregate answers.…”

“…The first approach [9,15,16,22,23,24,25,33,44] analytically derives closed-form error estimates for common aggregate functions in a database, such as SUM, AVG, etc. Although computationally appealing, analytic error quantification is restricted to a very limited set of queries.…”

Abs

“…This situation has brought even more attention to the already-active area of Approximate Query Processing (AQP). As a critical and general approach for coping with massive datasets, sampling is widely used in databases [4,6,9,11,12,13,18], Map-Reduce systems [5,16], and data stream management systems [7,17].…”