David Simmen
Abstract
Decision support applications are growing in popularity as more business data is kept on-line. Such applications typically include complex SQL queries that can test a query optimizer's ability to produce an efficient access plan. Many access plan strategies exploit the physical ordering of data provided by indexes or sorting. Sorting is an expensive operation, however. Therefore, it is imperative that sorting is optimized in some way or avoided all together. Toward that goal, this paper describes novel optimization techniques for pushing down sorts in joins, minimizing the number of sorting columns, and detecting when sorting can be avoided because of predicates, keys, or indexes. A set of fundamental operations is described that provide the foundation for implementing such techniques. The operations exploit data properties that arise from predicate application, uniqueness, and functional dependencies. These operations and techniques have been implemented in IBM's DB2/CS.
- Ant93 G. Antosheknov. Query processing in dec rdb: Major issues and future challenges. In IEEE Bulletin on the Technical Comittee on Data Engineer:ng, December 1993.Google Scholar
- BB79 C. Beeri and P. Bernstein. Computational problems related to the design of normal form relational schemas. In A CM Transactions on Database Systems, March 1979. Google ScholarDigital Library
- BD83 D. Bitton and D. DeWitt. Duplicate record elimination in large data files. In A CM Transactions on Database Systems, June 1983. Google ScholarDigital Library
- BE76 M. Blasgen and K. Eswaran. On the evaluation of queries in a relational data base system. Technical Report 1745, IBM Santa Teresa Lab, April 1976.Google Scholar
- CS93 S. Chaudhuri and K. Shim. Including group-by in query optimization. In Proceedings of the 19th International Conference on Very Large Data Bases, August 1993. Google ScholarDigital Library
- DD92 H. Darwen and C. Date. The role of functional dependencies in query decomposition. In Relatzonal Database Writings 1989-1991~ Addison Wesley, 1992.Google Scholar
- DKO+84 D. DeWitt, R. Katz, F. Olken, L. Shapiro, M. Stonebraker, and D. Wood. Implementation techniques for main memory database systems. In Proceedings of the 198~ A CM SIGMOD International Conference on Management of Data, June 1984. Google ScholarDigital Library
- Eng95 S. Englert. Tpc benchmark d. In Transact:on Processing Performance Council, 777 N. First St, Suite 600, San Jose CA 95112-6311, October 1995.Google Scholar
- GD87 G. Graefe and D. DeWitt. The exodus optimizer generator. In Proceedings of the 1987 A CM SIG- MOD International Conference on Management of Data, June 1987. Google ScholarDigital Library
- Gra93 G. Graefe. Query evaluation techniques for large databases. In A CM Computing Surveys, June 1993. Google ScholarDigital Library
- Hel94 J. Hellerstein. Pratical predicate placement. In Proceedings of the 1994 A CM SIGMOD Internatzonal Conference on Management of Data, June 1994. Google ScholarDigital Library
- HFLP89 L. Haas, J. Freytag, G. Lohman, and H. Pirahesh. Extensible query processing in starburst. In Proceedings of the 1989 A CM SIGMOD International Conference on Management of Data, June 1989. Google ScholarDigital Library
- JV84 M. Jarke and Y. Vassi}iou. Query optimization in database systems. In A CM Computing Surveys, June 1984. Google ScholarDigital Library
- Loh88 G. Lohman. Grammar-like functional rules for representing query optimization alternatives. In Proceedings of the 1988 A CM SIGMOD Internatzonal Conference on Management of Data, June 1988. Google ScholarDigital Library
- OL90 K. Ono and G. Lohman. Measuring the complexity of join enumeration in query optimization. In Proceedings of the 16th International Conference on Very Large Data Bases, August 1990. Google ScholarDigital Library
- PHH92 H. Pirahesh, J. Hellstein, and W. Hasan. Extensible rule based query rewrite optimization in starburst. In Proceedings of the I99~ A CM SIG- MOD International Conference on Management of Data, June 1992. Google ScholarDigital Library
- PL94 G. PauUey and P. Larson. Exploiting uniqueness in query optimization. In International Conference on Data Engineering, February 1994. Google ScholarDigital Library
- SAC+79 P. Se}inger, M. Astrahan, D. Chamber}in, R. Lorie, and T. Price. Access path selection in a relational database system. In Proceedings of the 1979 A CM SIGMOD Internatzonal Conference on Management of Data, June 1979. Google ScholarDigital Library
- YL93 P. Yah and P. Larson. Performing group-by before join. In International Conference on Data Engineemng, February 1993. Google ScholarDigital Library
Index Terms
- Fundamental techniques for order optimization
Recommendations
Fundamental techniques for order optimization
SIGMOD '96: Proceedings of the 1996 ACM SIGMOD international conference on Management of dataDecision support applications are growing in popularity as more business data is kept on-line. Such applications typically include complex SQL queries that can test a query optimizer's ability to produce an efficient access plan. Many access plan ...
Query optimization techniques for partitioned tables
SIGMOD '11: Proceedings of the 2011 ACM SIGMOD International Conference on Management of dataTable partitioning splits a table into smaller parts that can be accessed, stored, and maintained independent of one another. From their traditional use in improving query performance, partitioning strategies have evolved into a powerful mechanism to ...
Coral: federated query join order optimization based on deep reinforcement learning
AbstractThe rise of diversified data engines has created the need for federated queries. A federated query can take a query and provide data analysis based on data from various data engines. Since the query data originates from multiple data engines, ...
Comments