{"status":"ok","message-type":"work","message-version":"1.0.0","message":{"indexed":{"date-parts":[[2025,12,4]],"date-time":"2025-12-04T18:35:14Z","timestamp":1764873314025},"reference-count":15,"publisher":"Association for Computing Machinery (ACM)","issue":"2","content-domain":{"domain":[],"crossmark-restriction":false},"short-container-title":["Proc. VLDB Endow."],"published-print":{"date-parts":[[2009,8]]},"abstract":"<jats:p>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.<\/jats:p>","DOI":"10.14778\/1687553.1687563","type":"journal-article","created":{"date-parts":[[2014,6,24]],"date-time":"2014-06-24T12:17:57Z","timestamp":1403612277000},"page":"1366-1377","source":"Crossref","is-referenced-by-count":34,"title":["Enhanced subquery optimizations in Oracle"],"prefix":"10.14778","volume":"2","author":[{"given":"Srikanth","family":"Bellamkonda","sequence":"first","affiliation":[{"name":"Oracle USA, Redwood Shores, CA"}]},{"given":"Rafi","family":"Ahmed","sequence":"additional","affiliation":[{"name":"Oracle USA, Redwood Shores, CA"}]},{"given":"Andrew","family":"Witkowski","sequence":"additional","affiliation":[{"name":"Oracle USA, Redwood Shores, CA"}]},{"given":"Angela","family":"Amor","sequence":"additional","affiliation":[{"name":"Oracle USA, Redwood Shores, CA"}]},{"given":"Mohamed","family":"Zait","sequence":"additional","affiliation":[{"name":"Oracle USA, Redwood Shores, CA"}]},{"given":"Chun-Chieh","family":"Lin","sequence":"additional","affiliation":[{"name":"Oracle USA, Redwood Shores, CA"}]}],"member":"320","published-online":{"date-parts":[[2009,8]]},"reference":[{"key":"e_1_2_1_1_1","doi-asserted-by":"publisher","DOI":"10.1145\/319732.319745"},{"key":"e_1_2_1_2_1","volume-title":"Proceedings of the 13th VLDB Conference","author":"Dayal U.","year":"1987","unstructured":"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 . 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."},{"key":"e_1_2_1_3_1","volume-title":"Proceedings of the 18th VLDB Conference","author":"Muralikrishna M.","year":"1992","unstructured":"M. Muralikrishna , \" Improved Unnesting Algorithms for Join Aggregate SQL Queries\" , Proceedings of the 18th VLDB Conference , Vancouver, Canada , 1992 . M. Muralikrishna, \"Improved Unnesting Algorithms for Join Aggregate SQL Queries\", Proceedings of the 18th VLDB Conference, Vancouver, Canada, 1992."},{"key":"e_1_2_1_4_1","doi-asserted-by":"publisher","DOI":"10.1145\/130283.130294"},{"key":"e_1_2_1_5_1","volume-title":"Proceedings of the 20th VLDB Conference","author":"Chaudhuri S.","year":"1994","unstructured":"S. Chaudhuri and K. Shim , \" Including Group-By in Query Optimization \", Proceedings of the 20th VLDB Conference , Santiago, Chile , 1994 . S. Chaudhuri and K. Shim, \"Including Group-By in Query Optimization\", Proceedings of the 20th VLDB Conference, Santiago, Chile, 1994."},{"key":"e_1_2_1_6_1","volume-title":"Proceedings of the 21th VLDB Conference","author":"Yan W. P.","year":"1995","unstructured":"W. P. Yan and A. P. Larson , \" Eager Aggregation and Lazy Aggregation \", Proceedings of the 21th VLDB Conference , Zurich, Switzerland , 1995 . W. P. Yan and A. P. Larson, \"Eager Aggregation and Lazy Aggregation\", Proceedings of the 21th VLDB Conference, Zurich, Switzerland, 1995."},{"key":"e_1_2_1_7_1","doi-asserted-by":"publisher","DOI":"10.1145\/872757.872767"},{"key":"e_1_2_1_8_1","volume-title":"Proceedings of the 32nd VLDB Conference","author":"Ahmed R.","year":"2006","unstructured":"R. Ahmed , Cost-Based Query Transformation in Oracle\" , Proceedings of the 32nd VLDB Conference , Seoul, S. Korea , 2006 . R. Ahmed, et al, \"Cost-Based Query Transformation in Oracle\", Proceedings of the 32nd VLDB Conference, Seoul, S. Korea, 2006."},{"key":"e_1_2_1_9_1","doi-asserted-by":"publisher","DOI":"10.1145\/1247480.1247598"},{"key":"e_1_2_1_10_1","doi-asserted-by":"publisher","DOI":"10.1145\/974121.974142"},{"key":"e_1_2_1_11_1","unstructured":"F. Zemke. \"Rank Moving and reporting functions for OLAP \" 99\/01\/22 proposal for ANSI-NCITS.  F. Zemke. \"Rank Moving and reporting functions for OLAP \" 99\/01\/22 proposal for ANSI-NCITS ."},{"key":"e_1_2_1_12_1","unstructured":"C. Zuzarte et al \"Method for Aggregation Subquery Join Elimination\" http:\/\/www.freepatentsonline.com\/7454416.html  C. Zuzarte et al \"Method for Aggregation Subquery Join Elimination\" http:\/\/www.freepatentsonline.com\/7454416.html"},{"key":"e_1_2_1_13_1","doi-asserted-by":"publisher","DOI":"10.1145\/872757.872840"},{"key":"e_1_2_1_14_1","unstructured":"TPC Benchmark H (Decision Support) Standard Specification Rev 2.8 http:\/\/tpc.org\/tpch\/spec\/tpch2.8.0.pdf  TPC Benchmark H (Decision Support) Standard Specification Rev 2.8 http:\/\/tpc.org\/tpch\/spec\/tpch2.8.0.pdf"},{"key":"e_1_2_1_15_1","unstructured":"TPC-DS Specification Draft Rev 32 http:\/\/www.tpc.org\/tpcds\/spec\/tpcds32.pdf  TPC-DS Specification Draft Rev 32 http:\/\/www.tpc.org\/tpcds\/spec\/tpcds32.pdf"}],"container-title":["Proceedings of the VLDB Endowment"],"original-title":[],"language":"en","link":[{"URL":"https:\/\/dl.acm.org\/doi\/pdf\/10.14778\/1687553.1687563","content-type":"unspecified","content-version":"vor","intended-application":"similarity-checking"}],"deposited":{"date-parts":[[2022,12,28]],"date-time":"2022-12-28T10:57:17Z","timestamp":1672225037000},"score":1,"resource":{"primary":{"URL":"https:\/\/dl.acm.org\/doi\/10.14778\/1687553.1687563"}},"subtitle":[],"short-title":[],"issued":{"date-parts":[[2009,8]]},"references-count":15,"journal-issue":{"issue":"2","published-print":{"date-parts":[[2009,8]]}},"alternative-id":["10.14778\/1687553.1687563"],"URL":"https:\/\/doi.org\/10.14778\/1687553.1687563","relation":{},"ISSN":["2150-8097"],"issn-type":[{"value":"2150-8097","type":"print"}],"subject":[],"published":{"date-parts":[[2009,8]]}}}