Analysis of optimization capabilities of selected database management systems

The study is devoted to the analysis of optimization features and mechanisms available in Oracle Database, SQL Server, and PostgreSQL. The purpose of the study was to identify differences in the available functionalities and to assess the impact of selected mechanisms on SQL query performance. The research included a theoretical analysis based on database system documentation and a practical part conducted in a prepared test environment. The research scenarios examined the impact of indexes, materialized views, data partitioning, and query optimizer behaviour. The analysis indicated that Oracle Database offers the most extensive set of optimization functionalities compared with the other examined systems. The results of the practical tests showed that the use of appropriate optimization mechanisms can significantly reduce query execution time, while the scale of improvement depends on the type of query and the way in which a given mechanism is implemented.