Keyset Pagination di Oracle: Atasi I/O Tinggi dari OFFSET FETCH

Klausa ANSI SQL OFFSET ... FETCH NEXT yang diperkenalkan pada Oracle Database 12c mempermudah penulisan pagination. Namun, pada tabel berukuran jutaan baris, pagination berbasis offset (offset-based pagination) mengalami degradasi performa I/O secara linear saat menavigasi ke halaman dalam (deep pagination). Solusi deterministik untuk masalah ini adalah Keyset Pagination (dikenal juga sebagai Seek Method).

Akar Masalah: Mengapa OFFSET Memicu I/O Tinggi?

Secara internal, database tidak dapat melompat langsung ke baris ke-500.001. Oracle harus membaca seluruh baris dari indeks atau tabel mulai dari baris ke-1 hingga baris ke-500.050, mengurutkannya, lalu membuang 500.000 baris pertama untuk mengembalikan 50 baris yang diminta.

Proses pembuangan baris ini memicu consistent gets (buffer gets) yang sangat tinggi di buffer cache. Jika ukuran data yang harus disortir melebihi kuota PGA_AGGREGATE_TARGET untuk sesi tersebut, Oracle terpaksa melakukan tumpahan data (spill) ke TEMP Tablespace, memicu I/O disk fisik sekunder.

Analisis Execution Plan: OFFSET FETCH

Simulasikan skenario deep page pada tabel orders dengan 10 juta baris:

SELECT /*+ GATHER_PLAN_STATISTICS */ id, order_number, amount, created_at
FROM orders
ORDER BY created_at DESC, id DESC
OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY;

Periksa profil eksekusi riil via DBMS_XPLAN.DISPLAY_CURSOR:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

Keluaran execution plan menunjukkan pola kerja berikut:

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |              |      1 |        |     50 |00:00:01.42 |  148K   |       |       |          |
|*  1 |  VIEW                    |              |      1 | 500050 |     50 |00:00:01.42 |  148K   |       |       |          |
|*  2 |   WINDOW SORT PUSHED RANK|              |      1 | 500050 | 500050 |00:00:01.35 |  148K   |    38M|  2412K|   34M (0)|
|   3 |    TABLE ACCESS FULL     | ORDERS       |      1 |     10M|     10M|00:00:00.89 |  120K   |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

Operasi WINDOW SORT PUSHED RANK memproses 500.050 baris riil (A-Rows), menghabiskan 148.000 Buffers (I/O logis), dan mengonsumsi puluhan megabyte memori PGA sebelum mengembalikan 50 baris data.

Implementasi Keyset Pagination (Seek Method)

Keyset pagination mengeliminasi pembacaan baris yang tidak dibutuhkan dengan memanfaatkan klausa WHERE berindeks sebagai jangkar (anchor). Query melompat langsung ke leaf node indeks yang relevan melalui INDEX RANGE SCAN.

1. Membuat Composite Index

Pagination deterministik memerlukan kolom pengurutan utama dan kolom unik sebagai tie-breaker untuk mencegah data terlewat jika terdapat duplikasi nilai pada kolom pengurutan utama (misalnya created_at yang identik):

CREATE INDEX idx_orders_keyset ON orders (created_at DESC, id DESC);

2. Pola Query Keyset

Pada halaman pertama, aplikasi mengeksekusi:

SELECT id, order_number, amount, created_at
FROM orders
ORDER BY created_at DESC, id DESC
FETCH NEXT 50 ROWS ONLY;

Aplikasi menyimpan nilai created_at dan id dari baris terakhir (misal: :last_created_at dan :last_id). Halaman berikutnya diambil langsung menggunakan kondisi filter:

-- Pola 1: Expanded Boolean Logic (Disarankan untuk CBO Oracle)
SELECT id, order_number, amount, created_at
FROM orders
WHERE (created_at < :last_created_at)
   OR (created_at = :last_created_at AND id < :last_id)
ORDER BY created_at DESC, id DESC
FETCH FIRST 50 ROWS ONLY;

Meskipun Oracle mendukung sintaks tuple comparison WHERE (created_at, id) < (:last_created_at, :last_id), penulisan logika Boolean eksplisit (Pola 1) memberikan stabilitas pemilihan execution plan yang lebih konsisten pada optimizer Oracle di berbagai release updates.

Analisis Execution Plan: Keyset Pagination

Uji kembali query keyset menggunakan execution plan statistik aktual:

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |      1 |        |     50 |00:00:00.01 |       6 |       |       |
|*  1 |  VIEW                          |                   |      1 |     50 |     50 |00:00:00.01 |       6 |       |       |
|*  2 |   WINDOW NOSORT STOPKEY        |                   |      1 |     50 |     50 |00:00:00.01 |       6 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID | ORDERS            |      1 |     50 |     50 |00:00:00.01 |       6 |       |       |
|*  4 |     INDEX RANGE SCAN           | IDX_ORDERS_KEYSET |      1 |     50 |     50 |00:00:00.01 |       3 |       |       |
---------------------------------------------------------------------------------------------------------------------------------

Optimasi mengubah alur eksekusi:

• Operasi sort di memori dihilangkan (WINDOW NOSORT STOPKEY).
• Pencarian langsung menuju leaf block indeks melalui INDEX RANGE SCAN.
• Hanya 6 buffer gets yang dikonsumsi, menghasilkan latency sub-milidetik.

Perbandingan Metrik: Deep Page (Row 500.001 - 500.050)

Batasan dan Trade-Offs

• Tidak Ada Navigasi Bebas (Random Access): Pengguna tidak dapat melompat langsung dari halaman 1 ke halaman 50. Model ini dioptimalkan untuk pola infinite scroll, feed, atau tombol navigasi Next/Previous.
• Navigasi Mundur (Previous Page): Membutuhkan pembalikan arah operator (>) dan ORDER BY (ASC), lalu membalikkan array hasil sebelum dikirim ke client.
• Ketergantungan Indeks: Kolom yang digunakan pada keyset wajib terindeks secara tepat sesuai arah sorting. Perubahan filter dinamis pada API mewajibkan penyesuaian composite index yang sesuai.