COMPARISON OF INDEX, PARTITION, AND MATERIALIZED VIEW METHODS ON THE ORACLE DATABASE STUDY ON CENTRAL GOVERNMENT FINANCIAL REPORTS (LKPP)

M Harviandi Rachman; Samidi Samidi; Eko Aprianto

doi:10.52436/1.jutif.2024.5.4.1962

M Harviandi Rachman Faculty of Information Technology, Master of Computer Science Study Program, Universitas Budi Luhur, Indonesia
Samidi Faculty of Information Technology, Master of Computer Science Study Program, Universitas Budi Luhur, Indonesia
Eko Aprianto Faculty of Information Technology, Master of Computer Science Study Program, Universitas Budi Luhur, IndonesiaUniversitas Budi Luhur

DOI: https://doi.org/10.52436/1.jutif.2024.5.4.1962

Keywords: Indexing, Materialized View (MV), Performance optimization, Query optimization, Table partitioning

Abstract

The Indonesian Central Government Financial Report (LKPP) is a financial document prepared to increase transparency and accountability in the implementation of the State Revenue and Expenditure Budget (APBN). It is prepared within a tight schedule, hence changes made by each entity must be updated promptly. Therefore, this research focuses on the optimal table design for presenting financial reports. Query optimization is a major concern in database design, with the use of indexing concepts to increase data search speed. Table partitioning is also a strategy to consider, namely dividing a table into parts that form separate data ranges. The use of a Materialized View (MV) is another alternative, providing increased performance with the space-for-time trade-off principle. Experiments were carried out by comparing the response time of applying index, partition, and materialized views to produce financial report data. Experimental results indicate that materialized views can provide significant advantages when faced with large volumetric data. The decision to choose a materialized view can be considered contextually, depending on the specific needs and characteristics of the data encountered in a database system.

Downloads

Download data is not yet available.

References

Kementerian Keuangan. “Laporan Keuangan Pemerintah Pusat Tahun 2020 Audited.” Kementerian Keuangan, 2020.

N. M. Khushairi, N. A. Emran, and M. M. M. Yusof, "Database performance tuning methods for manufacturing execution system." World Applied Sciences Journal, 30 (30 A), 2014, doi: 10.5829/idosi.wasj.2014.30.icmrp.14.

K. Mózsi, and A. Kiss, "A session-based approach to autonomous database tuning." Acta Polytechnica Hungarica, vol. 17, no. 1, 2020, doi: 10.12700/APH.17.1.2020.1.1.

KARAGKOUNI, Dimitra, et al. "DIANA-LncBase v3: indexing experimentally supported miRNA targets on non-coding transcripts." Nucleic acids research, 48.D1: D101-D110, 2020.

R. Chopade and V. Pachghare, "MongoDB Indexing for Performance Improvement." Advances in Intelligent Systems and Computing, p. 1077, 2020, doi: 10.1007/978-981-15-0936-0_56.

A. D. Fuentes, A. C. Almeida, R. L. de C. Costa, V. Braganholo, and S. Lifschitz, "Database Tuning with Partial Indexes.", 2020, doi: 10.5753/sbbd.2018.22229.

P. Bednarczuk, "OPTIMIZATION IN VERY LARGE DATABASES BY PARTITIONING TABLES." Informatyka, Automatyka, Pomiary w Gospodarce i Ochronie Srodowiska, vol. 10, no. 3, 2020, doi: 10.35784/iapgos.2056.

P. Bednarczuk and A. Borsuk, "EFFICIENTLY PROCESSING DATA IN TABLE WITH BILLIONS OF RECORDS." Informatyka, Automatyka, Pomiary w Gospodarce i Ochronie Srodowiska, vol. 12, no. 4, 2020, doi: 10.35784/iapgos.3058.

Samidi, Fadly, Y. Virmansyah, R. Y. Suladi, and A. B. Lesmana, "Optimasi Database dengan Metode Index dan Partisi Tabel Database Postgresql pada Aplikasi E-Commerce. Studi pada Aplikasi Tokopintar." Jurnal Pendidikan Tambusai, vol. 6, no. 1, 2022.

M. Kechar and L. Bellatreche, "Safeness: Suffix Arrays Driven Materialized View Selection Framework for Large-Scale Workloads." Lecture Notes in Computer Science (Including Subseries Lecture Notes in Artificial Intelligence and Lecture Notes in Bioinformatics), 13428 LNCS, 2022, doi: 10.1007/978-3-031-12670-3_7.

G. Li, X. Zhou, J. Sun, X. Yu, Y. Han, L. Jin, W. Li, T. Wang, and S. Li, "Opengauss: An autonomous database system." Proceedings of the VLDB Endowment, vol. 14, no. 12, 2021, doi: 10.14778/3476311.3476380.

R. Ahmed, R. Bello, A. Witkowski, and P. Kumar, "Automated Generation of Materialized Views in Oracle." Proceedings of the VLDB Endowment, vol. 13, no. 12, 2020 doi: 10.14778/3415478.3415533.

M. Bandle, J. Giceva, and T. Neumann, "To Partition, or Not to Partition, That is the Join Question in a Real System." Proceedings of the ACM SIGMOD International Conference on Management of Data, 2021, doi: 10.1145/3448016.3452831.

A. C. Almeida, F. Baião, S. Lifschitz, D. Schwabe, and M. L. M. Campos, "Tun-OCM: A model-driven approach to support database tuning decision making." Decision Support Systems, p. 145, 2021, doi: 10.1016/j.dss.2021.113538.

M. Malcher and D. Kuhn, "Views, Synonyms, and Sequences." In Pro Oracle Database 18c Administration, 2019, doi: 10.1007/978-1-4842-4424-1_9.

E. Witono and Parno. "Perbandingan Response Time Penggunaan Index, Views, dan Materialized Views Database Mysql." Jurnal Sains Komputer & Informatika (J-SAKTI, vol. 6, no. 1, 2022.