Enhanced Subquery Optimizations in Oracle

Summary: Oracle’s optimizer removes subquery redundancies via coalescing, window-function rewrites, and GROUP BY view elimination. It adds scalable parallel execution and a null-aware antijoin for universal-quantifier queries, yielding substantial speedups. (summarized by gpt-5.6-luna on Jul 24 2026)

Paper ID: 10084
Venue: VLDB
Year: 2009
Pagerank: 9.1803718e-05
Overall Rank: 2,095 | 85.63%
DOI: 10.14778/1687553.1687563

Incoming Non-self Citations Over Time

Authors

1. Srikanth Bellamkonda (Oracle)
2. Rafi Ahmed (Oracle)
3. Andrew Witkowski (Oracle)
4. Angela Amor (Oracle)
5. Mohamed Zait (Oracle)
6. Chun-Chieh Lin (Oracle)

BibTeX Citation

@article{bellamkonda_vldb09,
        title = {{Enhanced Subquery Optimizations in Oracle}},
        author = {Bellamkonda, Srikanth and Ahmed, Rafi and Witkowski, Andrew and Amor, Angela and Zait, Mohamed and Lin, Chun-Chieh},
        journal = {PVLDB},
        series = {{VLDB} '09},
        doi = {10.14778/1687553.1687563},
        url = {https://doi.org/10.14778/1687553.1687563},
        year = {2009}
}

Incoming Citations (Sorted by Pagerank)

Showing 20 of 20 citing papers.

Rank	Citing Paper	Year	Venue	Pagerank
2,202	Quantifying TPC-H Choke Points and Their Optimizations	2020	VLDB	8.9639459e-05
2,664	Adaptive and Big Data Scale Parallel Execution in Oracle	2013	VLDB	8.2816537e-05
3,673	Efficient Processing of Window Functions in Analytical SQL Queries	2015	VLDB	7.2113383e-05
4,020	Optimization of Analytic Window Functions	2012	VLDB	6.9513881e-05
4,091	Of Snowstorms and Bushy Trees	2014	VLDB	6.9090244e-05
4,223	Accelerating Queries with Group-By and Join by Groupjoin	2011	VLDB	6.8224393e-05
4,329	Dimensions Based Data Clustering and Zone Maps	2017	VLDB	6.75682e-05
4,603	The Art of Balance: A RateupDB Experience of Building a CPU/GPU Hybrid Database Product	2021	VLDB	6.6105578e-05
5,983	Adaptive and Robust Query Execution for Lakehouses at Scale	2024	VLDB	6.0206841e-05
6,015	Dear User-Defined Functions, Inlining isn't working out so great for us. Let's try batching to make our relationship work. Sincerely, SQL	2024	CIDR	6.008272e-05
6,623	Teaching an RDBMS about ontological constraints	2016	VLDB	5.8206367e-05
7,148	Optimization of Nested Queries using the NF2 Algebra	2016	SIGMOD	5.6896396e-05
7,787	A Practical Approach to Groupjoin and Nested Aggregates	2021	VLDB	5.5449593e-05
8,483	Optimization of Common Table Expressions in MPP Database Systems	2015	VLDB	5.4157148e-05
10,136	On the Vexing Difficulty of Evaluating IN Predicates	2026	CIDR	5.093636e-05
10,697	Query Decorrelation in the Fabric Data Warehouse	2025	SIGMOD	5.093636e-05
11,234	Window Function Expression: Let the Self-join Enter	2024	VLDB	5.093636e-05
11,297	Grouping, Subsumption, and Disjunctive Join Optimizations in Oracle	2024	VLDB	5.093636e-05
11,537	Redundancy Elimination in Distributed Matrix Computation	2022	SIGMOD	5.093636e-05
11,625	Accelerating Complex Analytics using Speculation	2021	CIDR	5.093636e-05

Outgoing Citations (Sorted by Pagerank)

Showing 7 of 7 cited papers.

Citations counted here include only citations to other VLDB/SIGMOD/CIDR/PODS papers in this database.

Rank	Cited Paper	Year	Venue	Pagerank
71	Including Group-By in Query Optimization	1994	VLDB	0.00038021159
95	Of Nests and Trees: A Unified Approach to Processing Queries That Contain Nested Subqueries, Aggregates, and Quantifiers	1987	VLDB	0.00034607909
110	Extensible/Rule Based Query Rewrite Optimization in Starburst	1992	SIGMOD	0.0003309592
206	Eager Aggregation and Lazy Aggregation	1995	VLDB	0.00025092273
1,012	Improved Unnesting Algorithms for Join Aggregate SQL Queries	1992	VLDB	0.00012682631
1,362	Execution Strategies for SQL Subqueries	2007	SIGMOD	0.00011032204
2,021	WinMagic : Subquery Elimination Using Window Aggregation	2003	SIGMOD	9.297379e-05

Semantically Similar Papers

#	Overall Rank	Paper	Year	Venue
1	290	An Overview of Query Optimization in Relational Systems	1998	PODS
2	151	Efficient Mid-Query Re-Optimization of Sub-Optimal Query Execution Plans	1998	SIGMOD
3	1,887	Extending the Algebraic Framework of Query Processing to Handle Outerjoins	1984	VLDB
4	2,104	Optimization of Multi-Way Join Queries for Parallel Execution	1991	VLDB
5	2,664	Adaptive and Big Data Scale Parallel Execution in Oracle	2013	VLDB
6	9,756	Efficient Query Re-optimization with Judicious Subquery Selections	2023	SIGMOD
7	1,154	Efficient Exploitation of Similar Subexpressions for Query Processing	2007	SIGMOD
8	543	Orthogonal Optimization of Subqueries and Aggregation	2001	SIGMOD
9	1,362	Execution Strategies for SQL Subqueries	2007	SIGMOD
10	11,297	Grouping, Subsumption, and Disjunctive Join Optimizations in Oracle	2024	VLDB