Supporting time-constrained SQL queries in oracle

• Authors: Ying Hu;Seema Sundara;Jagannathan Srinivasan
• Venue: VLDB '07 Proceedings of the 33rd international conference on Very large data bases
• Year: 2007

The growing nature of databases, and the flexibility inherent in the SQL query language that allows arbitrarily complex formulations, can result in queries that take inordinate amount of time to complete. To mitigate this problem, strategies that are optimized to return the 'first-few rows' or 'top-k rows' (in case of sorted results) are usually employed. However, both these strategies can lead to unpredictable query processing times. Thus, in this paper we propose supporting time-constrained SQL queries. Specifically, a user issues a SQL query as before but additionally provides nature of constraint (soft or hard), an upper bound for query processing time, and acceptable nature of results (partial or approximate). The DBMS takes the criteria (constraint type, time limit, quality of result) into account in generating the query execution plan, which is expected (guaranteed) to complete in the allocated time for soft (hard) time constraint. If partial results are acceptable then the technique of reducing result set cardinality (i.e. returning first few or top-k rows) is used, whereas if approximate results are acceptable then sampling is used, to compute query results within the specified time limit. For the latter case, we argue that trading off quality of results for predictable response time is quite useful. However, for this case, we provide additional aggregate functions to estimate the aggregate values and to compute the associated confidence interval. This paper presents the notion of time-constrained SQL queries, discusses the challenges in supporting such a construct, describes a framework for supporting such queries, and outlines its implementation in Oracle Database by exploiting Oracle's cost-based optimizer and extensibility capabilities.