Enhanced subquery optimizations in Oracle

Authors:
Srikanth Bellamkonda;Rafi Ahmed;Andrew Witkowski;Angela Amor;Mohamed Zait;Chun-Chieh Lin
Affiliations:
Oracle USA, Redwood Shores, CA;Oracle USA, Redwood Shores, CA;Oracle USA, Redwood Shores, CA;Oracle USA, Redwood Shores, CA;Oracle USA, Redwood Shores, CA;Oracle USA, Redwood Shores, CA
Venue:
Proceedings of the VLDB Endowment
Year:
2009

Citing 11
Cited 5

Extensible/rule based query rewrite optimization in Starburst

SIGMOD '92 Proceedings of the 1992 ACM SIGMOD international conference on Management of data
On optimizing an SQL-like nested query

ACM Transactions on Database Systems (TODS)
Of Nests and Trees: A Unified Approach to Processing Queries That Contain Nested Subqueries, Aggregates, and Quantifiers

VLDB '87 Proceedings of the 13th International Conference on Very Large Data Bases
Including Group-By in Query Optimization

VLDB '94 Proceedings of the 20th International Conference on Very Large Data Bases
Eager Aggregation and Lazy Aggregation

VLDB '95 Proceedings of the 21th International Conference on Very Large Data Bases
Improved Unnesting Algorithms for Join Aggregate SQL Queries

VLDB '92 Proceedings of the 18th International Conference on Very Large Data Bases
Spreadsheets in RDBMS for OLAP

Proceedings of the 2003 ACM SIGMOD international conference on Management of data
WinMagic: subquery elimination using window aggregation

Proceedings of the 2003 ACM SIGMOD international conference on Management of data
SQL:2003 has been published

ACM SIGMOD Record
Cost-based query transformation in Oracle

VLDB '06 Proceedings of the 32nd international conference on Very large data bases
Execution strategies for SQL subqueries

Proceedings of the 2007 ACM SIGMOD international conference on Management of data

Hirundo: a mechanism for automated production of optimized data stream graphs

ICPE '12 Proceedings of the 3rd ACM/SPEC International Conference on Performance Engineering
Optimization of analytic window functions

Proceedings of the VLDB Endowment
Efficient ESL-Event-to-SQL translation

IScIDE'12 Proceedings of the third Sino-foreign-interchange conference on Intelligent Science and Intelligent Data Engineering
Adaptive and big data scale parallel execution in oracle

Proceedings of the VLDB Endowment
Automatic optimization of stream programs via source program operator graph transformations

Distributed and Parallel Databases

Quantified Score

Hi-index	0.00

Visualization

Abstract

This paper describes enhanced subquery optimizations in Oracle relational database system. It discusses several techniques -- subquery coalescing, subquery removal using window functions, and view elimination for group-by queries. These techniques recognize and remove redundancies in query structures and convert queries into potentially more optimal forms. The paper also discusses novel parallel execution techniques, which have general applicability and are used to improve the scalability of queries that have undergone some of these transformations. It describes a new variant of antijoin for optimizing subqueries involved in the universal quantifier with columns that may have nulls. It then presents performance results of these optimizations, which show significant execution time improvements.