Optimasi Query Oracle untuk Skala Jutaan Row

Saat saya pertama kali handle modul layanan publik di Dinas Pariwisata, saya kaget liat satu query JOIN antar 4 tabel besar bisa makan waktu 12 detik untuk eksekusi. Padahal itu jalur kritis — dipakai user setiap mereka login. Dari situ saya mulai serius belajar optimasi Oracle.

1. Pahami Execution Plan dulu, baru optimasi

Sebelum nyentuh apa-apa, jalanin dulu:

EXPLAIN PLAN FOR
SELECT * FROM permohonan p
JOIN pemohon u ON u.id = p.pemohon_id
WHERE p.status = 'PENDING';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Yang harus diperhatikan:

• TABLE ACCESS FULL → tabel di-scan semua, indikasi missing index
• NESTED LOOPS vs HASH JOIN → strategy join yang dipilih optimizer
• Cost → estimasi cost relatif

2. Index — bukan asal pasang

Index B-Tree biasa cocok untuk kolom dengan high cardinality (banyak unique value). Tapi untuk kolom kayak status yang cuma punya 3-4 nilai, Bitmap Index lebih efisien:

CREATE BITMAP INDEX idx_permohonan_status ON permohonan(status);

⚠️ Catatan: Bitmap Index buruk untuk OLTP yang banyak UPDATE concurrent. Pakai cuma untuk read-heavy table.

3. Partitioning untuk tabel >10 juta row

Saat tabel audit_log saya tembus 50 juta row, query laporan bulanan jadi lambat banget. Solusinya: range partitioning by tanggal:

CREATE TABLE audit_log (
  id NUMBER,
  created_at TIMESTAMP,
  payload CLOB
)
PARTITION BY RANGE (created_at) (
  PARTITION p_2024 VALUES LESS THAN (TO_DATE('2025-01-01','YYYY-MM-DD')),
  PARTITION p_2025 VALUES LESS THAN (TO_DATE('2026-01-01','YYYY-MM-DD'))
);

Query yang filter by date otomatis cuma scan partition relevan (partition pruning).

4. Hindari fungsi di kolom indexed

Ini classic mistake:

-- ❌ Index di created_at jadi nggak kepake
WHERE TRUNC(created_at) = TO_DATE('2026-04-01', 'YYYY-MM-DD')

-- ✅ Range condition, index kepake
WHERE created_at >= TO_DATE('2026-04-01', 'YYYY-MM-DD')
  AND created_at <  TO_DATE('2026-04-02', 'YYYY-MM-DD')

5. Hint, tapi jangan jadi habit

Oracle hint kayak /*+ INDEX(t idx_name) */ powerful tapi rapuh. Kalau data distribution berubah, hint lama bisa malah memperlambat. Pakai cuma kalau optimizer beneran salah pilih plan, dan dokumentasiin alasannya.

Kesimpulan

Dari 12 detik turun ke 0.8 detik bukan karena satu trick magis — kombinasi composite index yang tepat, query rewrite buang SELECT *, dan partitioning. Ukur dulu, optimasi belakangan. Jangan pre-optimize tanpa data.

Tulisan oleh M. Hidayatullah — Software Engineer Indonesia.