Performance Improvement of Periodic Reports with Materialized Views on Oracle Database System

Abstract

Periodic reports in Management Information Systems (MIS) play a crucial role in organizational decision-making. However, rapid data growth can degrade query performance. This study aims to enhance the performance of periodic reports in the State Revenue Collection Module (SIM MPN) of the Ministry of Finance by utilizing Materialized Views (MVs) in the Oracle database system. A dataset comprising 294,503,898 rows from 2021 to 2023 was used as a sample. An analysis was conducted on eight types of periodic report queries that traditionally relied on standard views. These queries were then converted into MVs to reduce execution time. Testing was performed by comparing execution times between views and MVs across 24 queries executed in two different environments. The results showed that the average execution time using views was 2,047,417 ms, whereas with MVs, execution times were reduced to 41 ms in the first test, 17 ms in the second, and 12 ms in the third. These findings confirm that MVs significantly improve the performance of periodic reports by accelerating query execution. The practical implication of this study is the recommendation to implement MVs in systems with large data volumes to optimize report access speed. Future research can focus on optimizing MV refresh times and further analyzing factors affecting execution time under various usage scenarios.

Keywords

• execution time, materialized views, oracle database, periodic reports, management information system