Physical query plans for Relational Division
Abstract
The Relational Division operator does not have a specific command in the SQL language. Therefore, the same query can be written in different ways, but the execution performance of them are not the same. Based on some representations of the Relational Division in SQL, this article investigates the use of two strategies for optimization: the use of a composite index and the direct use of the Hash Join operator in the query execution plan, while performing the join of tuples. The experiments were executed on the PostregeSQL DBMS and the obtained results show that the proposed strategies improved the performance in 47% regarding to the database query planner.
Keywords:
relational division, query plan, hash join
References
Celko, J. (2009). Divided we stand: The SQL of relational division.
Codd, E. F. (1972). Relational completeness of data base sublanguages. Res. Report, 987.
Date, C. J. (2015). SQL and Relational Theory. O’Reilly, 3rd edition.
Gonzaga, A. S. and Cordeiro, R. L. F. (2016). Fast and scalable relational division on database systems. In Simposio Brasileiro de Banco de Dados.
Gonzaga, A. S. and Cordeiro, R. L. F. (2019). The similarity-aware relational division database operator with case studies in agriculture and genetics. Inform. Systems, 82.
Leinders, D. and Bussche, J. V. d. (2007). On the complexity of division and set joins in the relational algebra. Journal of Computer and System Sciences, 73.
Matos, V. M. and Grasser, R. (2002). A simpler (and better) SQL approach to relational division. Journal of Information Systems Education, 13.
Tamani, N., Liétard, L., and Rocacher, D. (2013). A relational division based on a fuzzy bipolar r-implication operator. In IEEE International Conference on Fuzzy Systems.
Vasconcelos, G. Q., Kaster, D. S., and Cordeiro, R. L. F. (2018). On the support of the similarity-aware division operator in a commercial RDBMS. In Advances in Databases and Information Systems.
Vaverka, O. and Vychodil, V. (2016). Relational division in rank-aware databases. Information Sciences, 366.
Codd, E. F. (1972). Relational completeness of data base sublanguages. Res. Report, 987.
Date, C. J. (2015). SQL and Relational Theory. O’Reilly, 3rd edition.
Gonzaga, A. S. and Cordeiro, R. L. F. (2016). Fast and scalable relational division on database systems. In Simposio Brasileiro de Banco de Dados.
Gonzaga, A. S. and Cordeiro, R. L. F. (2019). The similarity-aware relational division database operator with case studies in agriculture and genetics. Inform. Systems, 82.
Leinders, D. and Bussche, J. V. d. (2007). On the complexity of division and set joins in the relational algebra. Journal of Computer and System Sciences, 73.
Matos, V. M. and Grasser, R. (2002). A simpler (and better) SQL approach to relational division. Journal of Information Systems Education, 13.
Tamani, N., Liétard, L., and Rocacher, D. (2013). A relational division based on a fuzzy bipolar r-implication operator. In IEEE International Conference on Fuzzy Systems.
Vasconcelos, G. Q., Kaster, D. S., and Cordeiro, R. L. F. (2018). On the support of the similarity-aware division operator in a commercial RDBMS. In Advances in Databases and Information Systems.
Vaverka, O. and Vychodil, V. (2016). Relational division in rank-aware databases. Information Sciences, 366.
Published
2023-09-25
How to Cite
LIMA, Letícia de Campos; SANTOS, Lúcio F. D.; BEDO, Marcos V. N.; SERAPHIM, Enzo; CARVALHO, Luiz Olmes.
Physical query plans for Relational Division. In: BRAZILIAN SYMPOSIUM ON DATABASES (SBBD), 38. , 2023, Belo Horizonte/MG.
Anais [...].
Porto Alegre: Sociedade Brasileira de Computação,
2023
.
p. 384-389.
ISSN 2763-8979.
DOI: https://doi.org/10.5753/sbbd.2023.233409.
