More Web Proxy on the site http://driver.im/

Article

Cost-based query transformation in Oracle

Authors:

Andrew Witkowski,

Thierry CruanesAuthors Info & Claims

VLDB '06: Proceedings of the 32nd international conference on Very large data bases

Pages 1026 - 1036

Published: 01 September 2006 Publication History

Abstract

This 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 cost-based query transformation, the need for such a framework, possible interactions among some of the transformation, and efficient algorithms for enumerating the search space of cost-based transformations. It describes a practical technique to combine cost-based transformations with a traditional physical optimizer. Some of the challenges of cost-based transformation are highlighted. Our experience shows that some transformations when performed in a cost-based manner lead to significant execution time improvements.

References

[1]

{1} S. Chaudhuri and K. Shim, "Including Group-By in Query Optimization", Proceedings of the 20th VLDB Conference, Santiago, Chile, 1994.

Digital Library

[2]

{2} S. Chaudhuri and K. Shim, "Optimizing Queries with Aggregate Views", EDBT 1996.

Digital Library

[3]

{3} C.A. Galindo-Legaria, et al., "Outerjoin Simplification and Reordering for Query Optimization", TODS, 1997.

Digital Library

[4]

{4} R.A. Ganski and H. K. T. Wong, "Optimization of Nested SQL Queries Revisited". Proc. of ACM SIGMOD, 1987.

Digital Library

[5]

{5} H. Garcia-Molina, J. D. Ullman, and J. Widom, "Database System Implementation", Prentice Hall, 2000.

Digital Library

[6]

{6} G. Graefe and W. J. Mckenna, "The Volcano optimizer generator: Extensibility and Efficient Search", Proceedings of the 19th International Conf. on Data Engineering, 1993.

Digital Library

[7]

{7} A. Gupta, V. Harinarayan, and D. Quass, "Aggregate-Query Processing in Data Warehousing Environments", Proceedings of the 21th VLDB Conference, 1995.

Digital Library

[8]

{8} J.M. Hellerstein and M. Stonebraker, "Predicate migration: Optimizing Queries with Expensive Predicates," Proc. of ACM SIGMOD, Washington DC, 1993.

Digital Library

[9]

{9} A.Y. Levy, I.S. Mumick, and Y. Sagiv, "Query Optimization by Predicate Move-Around," Proceedings of the 20th VLDB Conference, Santiago, Chile, 1994.

Digital Library

[10]

{10} W. Kim. "On Optimizing an SQL-Like Nested Query", ACM TODS, September, 1982.

Digital Library

[11]

{11} T. Morzy, M. Matysiak, and S. Salza, "Tabu Search Optimization of Large Join Queries", EDBT, 1994.

Digital Library

[12]

{12} I.S. Mumick, S. Finkelstain, H. Pirahesh and R. Ramakrishnan, "Magic is Relevant", Proceedings of ACM SIGMOD, 1990.

Digital Library

[13]

{13} I.S. Mumick and H. Pirahesh and R. Ramakrishnan, "Implementation of Magic Sets in Relational Database Systems", Proceedings of ACM SIGMOD, 1994.

Digital Library

[14]

{14} M. Muralikrishna, "Improved Unnesting Algorithms for Join Aggregate SQL Queries", Proceedings of the 18th VLDB Conference, Vancouver, Canada, 1992.

Digital Library

[15]

{15} A. Pellenkoft, C.A. Galindo-Legaria, M. Kersen, "The Complexity of the Transformation-Based Join Enumeration. Proceedings of the 23rd VLDB Conference, Athens, Greece. 1997.

Digital Library

[16]

{16} J. Rao, H. Pirahesh, and C. Zuzarte, "Cannonical Abstractios for Outerjoin Optimization", Proceedings of ACM SIGMOD, Paris, France, 2004.

Digital Library

[17]

{17} 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.

Digital Library

[18]

{18} A. Rosenthal and Cesar A. Galindo-Legaria, "Query Graphs, Implementing Trees, and Freely-reorderable Outerjoins", Proceedings of ACM SIGMOD, 1990.

Digital Library

[19]

{19} P. Seshadri, et al, "Cost-Based Optimization for Magic Algebra and Implementation", Proceedings of ACM SIGMOD, Monteral, Canada 1996.

Digital Library

[20]

{20} A. Swami and A. Gupta, "Optimization of Large Join Queries", Proceedings of ACM SIGMOD, Chicago, U.S.A, 1988.

Digital Library

[21]

{21} A. Swami, "Optimization of Large Join Queries: Combining Heuristics and Combinatorial Techniques", ACM SIGMOD, Portland, Oregon, U.S.A., 1989.

Digital Library

[22]

{22} 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.

Digital Library

[23]

{23} W.P. Yan and A.P. Larson, "Performing Group By Before Join", IEEE ICDE, Houston, Texas, 1994.

Digital Library

[24]

{24} W.P. Yan and A.P. Larson, "Eager Aggregation and Lazy Aggregation", Proceedings of the 21th VLDB Conference, Zurich, Swizerland, 1995.

Digital Library

[25]

{25} A. Witkowski, et al, "Spreadsheets in RDBMS for OLAP", Proceedings of ACM SIGMOD, San Diego, USA, 2003.

Digital Library

[26]

{26} A. Witkowski, et al, "Spreadsheets in RDBMS for OLAP", ACM TODS, 2005.

Cited By

Ahmed RPasupuleti KTirupattur SSheng LSu HZiauddin M(2024)Grouping, Subsumption, and Disjunctive Join Optimizations in OracleProceedings of the VLDB Endowment10.14778/3685800.368583717:12(4200-4212)Online publication date: 8-Nov-2024
https://dl.acm.org/doi/10.14778/3685800.3685837
Pasupuleti KLi JSu HZiauddin M(2023)Automatic SQL Error Mitigation in OracleProceedings of the VLDB Endowment10.14778/3611540.361156816:12(3835-3847)Online publication date: 1-Aug-2023
https://dl.acm.org/doi/10.14778/3611540.3611568
Pasupuleti KDas DValluri SZait MAl Hasan MXiong L(2022)Observability of SQL Hints in OracleProceedings of the 31st ACM International Conference on Information & Knowledge Management10.1145/3511808.3557124(3441-3450)Online publication date: 17-Oct-2022
https://dl.acm.org/doi/10.1145/3511808.3557124
Show More Cited By

Index Terms

Cost-based query transformation in Oracle
1. Information systems
  1. Data management systems
  2. Information retrieval
    1. Evaluation of retrieval results
    2. Information retrieval query processing
2. Theory of computation
  1. Theory and algorithms for application domains
    1. Database theory
      1. Database query processing and optimization (theory)

Recommendations

Query transformation
View-based query containment
PODS '03: Proceedings of the twenty-second ACM SIGMOD-SIGACT-SIGART symposium on Principles of database systems

Query containment is the problem of checking whether for all databases the answer to a query is a subset of the answer to a second query. In several data management tasks, such as data integration, mobile computing, etc., the data of interest are only ...
View-based query processing: On the relationship between rewriting, answering and losslessness

As a result of the extensive research in view-based query processing, three notions have been identified as fundamental, namely rewriting, answering, and losslessness. Answering amounts to computing the tuples satisfying the query in all databases ...

Comments

Please enable JavaScript to view thecomments powered by Disqus.

Information & Contributors

Information

Published In

cover image ACM Conferences

VLDB '06: Proceedings of the 32nd international conference on Very large data bases

September 2006

1269 pages

Sponsors

SIGMOD: ACM Special Interest Group on Management of Data
K.I.S.S. SIG on Databases
AJU Information Technology Co., Ltd
US Army ITC-PAC Asian Research Office
Google Inc.
The Database Society of Japan
Samsung SOS
Advanced Information Technology Research Center
Naver
Microsoft: Microsoft
Korea Info Sci Society: Korea Information Science Society
SK telecom
Systems Applications Products
ORACLE: ORACLE
International Business Management
Air Force Office of Scientific Research/Asian Office of Aerospace R&D
Kosef
Kaist
LG Electronics
CCF-DBS

Publisher

VLDB Endowment

Publication History

Published: 01 September 2006

Check for updates

Qualifiers

Article

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

22
Total Citations
View Citations
3,082
Total Downloads

Downloads (Last 12 months)57
Downloads (Last 6 weeks)12

Reflects downloads up to 07 Jan 2025

Other Metrics

View Author Metrics

Citations

Cited By

Ahmed RPasupuleti KTirupattur SSheng LSu HZiauddin M(2024)Grouping, Subsumption, and Disjunctive Join Optimizations in OracleProceedings of the VLDB Endowment10.14778/3685800.368583717:12(4200-4212)Online publication date: 8-Nov-2024
https://dl.acm.org/doi/10.14778/3685800.3685837
Pasupuleti KLi JSu HZiauddin M(2023)Automatic SQL Error Mitigation in OracleProceedings of the VLDB Endowment10.14778/3611540.361156816:12(3835-3847)Online publication date: 1-Aug-2023
https://dl.acm.org/doi/10.14778/3611540.3611568
Pasupuleti KDas DValluri SZait MAl Hasan MXiong L(2022)Observability of SQL Hints in OracleProceedings of the 31st ACM International Conference on Information & Knowledge Management10.1145/3511808.3557124(3441-3450)Online publication date: 17-Oct-2022
https://dl.acm.org/doi/10.1145/3511808.3557124
Makor LRajan H(2021)Run-time data analysis in dynamic runtimesCompanion Proceedings of the 2021 ACM SIGPLAN International Conference on Systems, Programming, Languages, and Applications: Software for Humanity10.1145/3484271.3484973(6-8)Online publication date: 17-Oct-2021
https://dl.acm.org/doi/10.1145/3484271.3484973
Ahmed RBello RWitkowski AKumar P(2020)Automated generation of materialized views in OracleProceedings of the VLDB Endowment10.14778/3415478.341553313:12(3046-3058)Online publication date: 14-Sep-2020
https://dl.acm.org/doi/10.14778/3415478.3415533
Chakkappen SBudalakoti SKrishnamachari RValluri SWood AZait M(2017)Adaptive statistics in Oracle 12cProceedings of the VLDB Endowment10.14778/3137765.313778510:12(1813-1824)Online publication date: 1-Aug-2017
https://dl.acm.org/doi/10.14778/3137765.3137785
Bursztyn DGoasdoué FManolescu I(2016)Teaching an RDBMS about ontological constraintsProceedings of the VLDB Endowment10.14778/2994509.29945329:12(1161-1172)Online publication date: 1-Aug-2016
https://dl.acm.org/doi/10.14778/2994509.2994532
Hölsch JGrossniklaus MScholl MÖzcan FKoutrika GMadden S(2016)Optimization of Nested Queries using the NF2 AlgebraProceedings of the 2016 International Conference on Management of Data10.1145/2882903.2915241(1765-1780)Online publication date: 26-Jun-2016
https://dl.acm.org/doi/10.1145/2882903.2915241
Ahmed RSen RPoess MChakkappen S(2014)Of snowstorms and bushy treesProceedings of the VLDB Endowment10.14778/2733004.27330177:13(1452-1461)Online publication date: 1-Aug-2014
https://dl.acm.org/doi/10.14778/2733004.2733017
Pan KWu XXie T(2014)Guided test generation for database applications via synthesized database interactionsACM Transactions on Software Engineering and Methodology10.1145/249152923:2(1-27)Online publication date: 4-Apr-2014
https://dl.acm.org/doi/10.1145/2491529
Show More Cited By

View Options

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Publication

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Media

Figures

Other

Tables

View Table of Contents