Srikanth Bellamkonda
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.
- W. Kim. "On Optimizing an SQL-Like Nested Query", ACM TODS, September 1982. Google Scholar
Digital Library
- U. Dayal, "Of Nests and Trees: A Unified Approach to Processing Queries that Contain Nested Subqueries, Aggregates, and Quantifiers", Proceedings of the 13th VLDB Conference, Brighton, U.K., 1987. Google ScholarDigital Library
- M. Muralikrishna, "Improved Unnesting Algorithms for Join Aggregate SQL Queries", Proceedings of the 18th VLDB Conference, Vancouver, Canada, 1992. Google ScholarDigital Library
- H. Pirahesh, J. M. Hellerstein, and W. Hasan, "Extensible Rule Based Query Rewrite Optimizations in Starburst". Proc. of ACM SIGMOD, San Diego, U.S.A., 1992. Google ScholarDigital Library
- S. Chaudhuri and K. Shim, "Including Group-By in Query Optimization", Proceedings of the 20th VLDB Conference, Santiago, Chile, 1994. Google ScholarDigital Library
- W. P. Yan and A. P. Larson, "Eager Aggregation and Lazy Aggregation", Proceedings of the 21th VLDB Conference, Zurich, Switzerland, 1995. Google ScholarDigital Library
- A. Witkowski, et al, "Spreadsheets in RDBMS for OLAP", Proceedings of ACM SIGMOD, San Diego, USA, 2003. Google ScholarDigital Library
- R. Ahmed, et al, "Cost-Based Query Transformation in Oracle", Proceedings of the 32nd VLDB Conference, Seoul, S. Korea, 2006. Google ScholarDigital Library
- M. Elhemali, C. Galindo-Legaria, et al, "Execution Strategies for SQL Subqueries", Proceedings of ACM SIGMOD, Beijing, China, 2007. Google ScholarDigital Library
- A. Eisenberg, K. Kulkarni, et al, "SQL: 2003 Has Been Published", SIGMOD Record, March 2004. Google ScholarDigital Library
- F. Zemke. "Rank, Moving and reporting functions for OLAP," 99/01/22 proposal for ANSI-NCITS.Google Scholar
- C. Zuzarte et al, "Method for Aggregation Subquery Join Elimination", http://www.freepatentsonline.com/7454416.htmlGoogle Scholar
- C. Zuzarte, H. Pirahesh, et al, "WinMagic: Subquery Elimination Using Window Aggregation", Proceedings of ACM SIGMOD, San Diego, USA, 2003. Google ScholarDigital Library
- TPC Benchmark H (Decision Support), Standard Specification Rev 2.8, http://tpc.org/tpch/spec/tpch2.8.0.pdfGoogle Scholar
- TPC-DS Specification Draft, Rev 32, http://www.tpc.org/tpcds/spec/tpcds32.pdfGoogle Scholar
Index Terms
- Enhanced subquery optimizations in Oracle
Recommendations
Cost-based query transformation in Oracle
VLDB '06: Proceedings of the 32nd international conference on Very large data basesThis paper describes cost-based query transformation in Oracle relational database system, which is a novel phase in query optimization. It discusses a suite of heuristic- and cost-based transformations performed by Oracle. It presents the framework for ...
Subquery Elimination: A Complete Unnesting Algorithm for an Extended Relational Algebra
ICDE '97: Proceedings of the Thirteenth International Conference on Data EngineeringSummary form only given, as follows. Research in the area of subquery unnesting algorithms has mostly focused on the problem of making queries more efficient at run-time by transforming subqueries into joins. Unnesting rules describe a transformation of ...
Efficient Query Re-optimization with Judicious Subquery Selections
PACMMODQuery re-optimization is an adaptive query processing technique that re-invokes the optimizer at certain points in query execution. The goal is to dynamically correct the cardinality estimation errors using the statistics collected at runtime to adjust ...
Comments