SQL query optimization through nested relational algebra

ACM Transactions on Database Systems

Volumn 32, Issue 3, 2007, Pages

  Cao, Bin ^a,b   Badia, Antonio ^a,b  

^a UNIVERSITY OF LOUISVILLE   (United States)

^b University of Louisville

Author keywords

Nested queries; Nested relational algebra; Nonrelational query processing

Indexed keywords

ALGEBRA; DATA STRUCTURES; MATHEMATICAL OPERATORS; OPTIMIZATION; QUERY PROCESSING; RELATIONAL DATABASE SYSTEMS;

ALGEBRAIC OPTIMIZATION RULES; NESTED QUERIES; NESTED RELATIONAL ALGEBRA;

QUERY LANGUAGES;

EID: 34548432956     PISSN: 03625915     EISSN: 15574644     Source Type: Journal    
DOI: 10.1145/1272743.1272748     Document Type: Article

References (57)

1. ABITEBOUL, S. AND BIDOIT, N. 1984. Non first normal form relations to represent hierarchically organized data. In Proceedings of the PODS Conference. 191-200.
2. AKINDE, M. O. AND BOHLEN, M. H. 2001. Generalized MD-joins: Evaluation and reduction to SQL. In Proceedings of the VLDB International Workshop on Databases in Telecommunications. 52-67.
3. AKINDE, M. AND BOHLEN, M. 2003. Efficient computation of subqueries in complex OLAP. In Proceedings of the IEEE International Conference on Data Engineering. IEEE Computer Society Press, Los Alamiton CA, 163-174.
4. BADIA, A. 2003a. Automatic generation of XML from relations: The nested relation approach. In Proceedings of the ER Workshop. 330-341.
5. BADIA, A. 2003b. Computing SQL subqueries with Boolean aggregates. In Proceedings of the DAWAK Conference. 391-400.
6. BAEKGAARD, L. AND MARK, L. 1995. Incremental computation of nested relational query expressions. ACM Trans. Datab. Syst. 20, 2, 111-148.
7. BULTINGSLOEWEN, G. V. 1987. Translating and optimizing SQL queries having aggregates. In Proceedings of the Conference on Very Large Data Bases. 235-243.
8. CERI, S. AND GOTTLOB, G. 1985. Translating SQL into relational algebra: Optimization, semantics, and equivalence of SQL queries. IEEE Trans. Softw. Eng. 11, 4, 324-345.
9. CHATZIANTONIOU, D., AKINDE, M. O., JOHNSON, T., AND KIM, S. 2001. MD-join: an operator for complex OLAP. In Proceedings of the IEEE International Conference on Data Engineering. IEEE Computer Society Press, Los Alamiton, CA, 524-533.
10. CODD, E. F. 1970. A relational model of data for large shared data banks. Commun. ACM 13, 6, 377-387.
11. COLBY, L. S. 1989. A recursive algebra and query optimization for nested relations. In Proceedings of the SIGMOD Conference. ACM, New York, 273-283.
12. DAYAL, U. 1987. Of nests and trees: A unified approach to processing queries that contain nested subqueries, aggregates, and quantifiers. In Proceedings of the Conference on Very Large Data Bases. 197-208.
13. GALINDO-LEGARIA, C. A. AND JOSHI, M. M. 2001. Orthogonal optimization of subqueries and aggregation. In Proceedings of the ACM SIGMOD Conference. ACM, New York, 571-581.
14. GALINDO-LEGARIA, C. AND ROSENTHAL, A. 1997. Outerjoin simplification and reordering for query optimization. ACM Trans. Datab. Syst. 22, 1, 43-74.
15. GANSKI, R. A. AND WONG, H. K. T. 1987. Optimization of nested SQL queries revisited. In Proceedings of the ACM SIGMOD Conference. ACM, New York, 23-33.
16. GARANI, G. AND JOHNSON, R. 2000. Joining nested relations and subrelations. Inf. Syst. 25, 4, 287-307.
17. GUPTA, A., HARINARAYAN, V., AND QUASS, D. 1995. Aggregate-query processing in data warehousing environments. In Proceedings of the Conference on Very Large Data Bases. 358-369.
18. GYSSENS, M. AND VAN GUCHT, D. 1988. The powerset algebra as a result of adding programming constructs to the nested relational algebra. In Proceedings of the SIGMOD Conference. ACM, New York, 225-232.
19. GYSSENS, M. AND VAN GUCHT, D. 1989. A uniform approach toward handling atomic and structured information in the nested relational database model. J. ACM 36, 4, 790-825.
20. HELMER, S. AND MOERKOTTE, G. 1997. Evaluation of main memory join algorithms for joins with set comparison join predicates. In Proceedings of the Conference on Very Large Data, Bases. 386-395.
21. JAESCHKE, G. AND SCHEK, H. J. 1982. Remarks on the algebra of non first normal form relationsl. In Proceedings of the PODS Conference. ACM, New York, 124-138.
22. JAN, Y. 1990. Algebraic optimization for nested relations. In Proceedings of the ICSS Conference. 278-287.
23. KIM, W. 1982. On optimizing an SQL-like nested query. ACM Trans. Datab. Syst. 7, 3, 443-469.
24. LEE, D., MANI, M., CHIU, F., AND CHU, W. W. 2001. Nesting-based relational-to-XML schema translation. In Proceedings of the WebDB Workshop. 61-66.
25. LEVENE, M. AND LOIZOU, G. 1993. Semantics for null extended nested relations. ACM Trans. Datab. Syst. 18, 3, 414-459.
26. LEVENE, M. AND LOIZOU, G. 1994. The nested universal relation data model. J. Comput. Syst. Sci. 49, 3, 683-717.
27. LIU, H.-C. AND RAMAMOHANARAO, K. 1994. Algebraic equivalences among nested relational expressions. In Proceedings of the CIKM Conference. 234-243.
28. LIU, H.-C. AND YU, J. X. 2005. Algebraic equivalences of nested relational operators. Inf. Syst. 30, 167-204.
29. MAKINOUCHI, A. 1977. A consideration on normal form of not-necessarily-normalized relation in the relational data model. In Proceedings of the Conference on Very Large Data Bases. 447-453.
30. MAMOULIS, N. 2003. Efficient processing of joins on set-valued attributes. In Proceedings of the SIGMOD Conference. ACM, New York, 157-168.
31. MELNIK, S. AND GARCIA-MOLINA, H. 2002. Divide-and-conquer algorithm for computing set containment joins. In Proceedings of the EDBT Conference. 427-444.
32. MELNIK, S. AND GARCIA-MOLINA, H. 2003. Adaptive algorithms for set containment joins. ACM Trans. Datab. Sys. 28, 1, 56-99.
33. MUMICK, I. S., FINKEKSTEIN, S. J., PIRAHESH, H., AND RAMAKRISHNAN, R. 1990. Magic is relevant. In Proceedings of the ACM SIGMOD Conference. ACM, New York, 247-258.
34. MUMICK, I. S. AND PIRAHESH, H. 1994. Implementation of magic-sets in a relational database system. In Proceedings of the ACM SIGMOD Conference. ACM, New York, 103-114.
35. MURALIKRISHNA, M. 1989. Optimization and dataflow algorithms for nested tree queries. In Proceedings of the Conference on Very Large Data Bases. 77-85.
36. MURALIKRISHNA, M. 1992. Improved unnesting algorithms for join aggregate SQL queries. In Proceedings of the Conference on Very Large Data Bases. 91-102.
37. OZSOYOGLU, G., OZSOYOGLU, Z. M., AND MATOS, V. 1987. Extending relational algebra and relational calculus with set-valued attributes and aggregate functions. ACM Trans. Datab. Syst. 12, 4, 566-592.
38. PAREDAENS, J., DE BRA, P., GYSSENS, M., AND VAN GUCHT, D. 1989. The Structure of the Relational Model. Springer-Verlag, New York.
39. RAMASAMY, K., PATEL, J. M., NAUGHTON, J. F., AND KAUSHIK, R. 2000. Set containment joins: The good, the bad and the ugly. In Proceedings of the SIGMOD Conference. ACM, New York, 351-362.
40. RAO, J., LINDSAY, B., LOHMAN, G., PIRAHESH, H., AND SIMMEN, D. 2001. Using EELs, a practical approach to outerjoin and antijoin reordering. In Proceedings of the IEEE International Conference on Data Engineering. IEEE Computer Society Press, Los Alamiton, CA, 585-594.
41. RAO, J. AND ROSS, K. A. 1998. Reusing invariants: a new strategy for correlated queries. In Proceedings of the ACM SIGMOD Conference. ACM, New York, 37-48.
42. ROTH, M. A., KORTH, H. F., AND SILBERSCHATZ, A. 1988. Extended relational algebra and calculus for nested relational databases. ACM Trans. Datab. Syst. 13, 4, 389-417.
43. ROTH, M. A., KORTH, H. F., AND SILBERSCHATZ, A. 1989. Null values in nested relational databases. Acta Inf. 26, 7, 615-642.
44. ROY, P., SESHADRI, S., SUDARSHAN, S., AND BHOBE, S. 2000. Efficient and extensible algorithms for multi query optimization. In Proceedings of the SIGMOD Conference. ACM, New York, 249-260.
45. SCHEK, H. J. AND SCHOLL, M. H. 1986. The relational model with relation-valued attributes. Inf. Syst. 11, 2, 137-147.
46. SCHOLL, M. H. 1986. Theoretical foundation of algebraic optimization utilizing unnormalized relations. In Proceedings of the ICDT Conference. 380-396.
47. SELINGER, P. G., ASTRAHAN, M. M., CHAMBERLIN, D. D., LORIE, R. A., AND PRICE, T. G. 1979. Access path selection in a relational database management system. In Proceedings of the ACM SIGMOD Conference. ACM, New York, 23-34.
48. SESHADRI, P., HELLERSTEIN, J. M., PIRAHESH, H., LEUNG, T. Y. C., RAMAKRISHNAN, R., SRIVASTAVA, R., STUCKEY, P. J., AND SUDARSHAN, S. 1996a. Cost-based optimization for magic: Algebra and implementation. In Proceedings of the ACM SIGMOD Conference. ACM, New York, 435-446.
49. SESHADRI, P., PIRAHESH, H., AND LEUNG, T. Y. C. 1996b. Complex query decorrelation. In Proceedings of the ICDE Conference. 450-458.
50. THOMAS, S. J. AND FISCHER, P. C. 1986. Nested relational structures. Adv. Comput. Res. 3,269-307.
51. TRANSACTION PROCESSING PERFORMANCE COUNCIL. The TPC-H benchmark, http://www.tpc.org/tpch.
52. VAN GUCHT, D. 1987. On the expressive power of the extended relational algebra for the unnormalized relational model. In Proceedings of the PODS Conference. 302-312.
53. VAN GUCHT, D., AND FISCHER, P. C. 1986. Some classes of multilevel relational structures. In Proceedings of the PODS Conference. 60-69.
54. VOSSEN, G. 1991. Data Models, Database Language and Database Management Systems. Addison-Wesley, Reading, MA.
55. YAN, W. P., AND LARSON, P. 1994. Performing group-by before join. In Proceedings of the IEEE International Conference on Data Engineering. IEEE Computer Society Press, Los Alamiton, CA, 89-100.
56. ZHANG, C., NAUGHTON, J., DEWITT, D., LUO, Q., AND LOHMAN, G. 2001. On supporting containment queries in relational database management systems. In Proceedings of the Conference on Very Large Data Bases. 425-436.
57. ZUZARTE, C., PIRAHESH, H., MA, W., CHENG, Q., LIU, L., AND WONG, K. 2003. WinMagic: Subquery elimination using window aggregation. In Proceedings of the ACM SIGMOD Conference. ACM, New York, 652-656.