1. Pengertian Indeks
Menurut Ramakrishnan dan Gehrke (2005, p276), indeks adalah struktur data yang mengatur record data pada disk untuk melakukan optimasi bermacam-macam operasi pencarian keterangan. Dengan menggunakan indeks, kondisi pencarian pada record-record dapat dipermudah dengan field kunci pencarian. Cara lainnya adalah membuat indeks tambahan pada kumpulan data, masing-masing dengan kunci pencarian yang berbeda, untuk mempercepat operasi pencarian yang tidak didukung oleh organisasi file.
2. Teknik-teknik Indexing
Menurut Immanuel Chan (2008, p2-11), ada berbagai tipe indexing yang dapat dilakukan, antara lain:
B-Tree Indexes
Merupakan teknik indeks yang standar dengan keunggulan untuk primary key dan indeks dengan pemilihan selektif yang tinggi. Indeks dengan teknik B-tree ini dapat digunakan untuk mengembalikan data yang diurutkan berdasarkan indeks pada kolom.
Bitmap indexes
Teknik ini cocok untuk data dengan kardinalitas yang minimum. Melalui kompresi data, teknik ini dapat menghasilkan row-id dalam jumlah yang besar dengan penggunaan I/O yang minimal. Kombinasi teknik indeks bitmap pada kolom yang tidak diseleksi dapat memberikan efisiensi penggunaan operasi AND dan OR dengan menghasilkan row-id dalam jumlah yang besar dan penggunaan I/O yang minimal. Teknik ini secara khusus efektif dalam query dengan perintah COUNT().
Function-based Indexes
Teknik ini dapat membuat akses melalui B-tree pada nilai yang diturunkan dari fungsi yang ada pada data dasar. Teknik ini memiliki batasan dengan penggunaan NULL dan membutuhkan penggunaan optimasi query. Teknik function-based indexes ini secara khusus berguna ketika melakukan query pada kolom-kolom campuran untuk menghasilkan data yang diturunkan atau untuk menanggulangi batasan data yang disimpan dalam basis data.
Partitioned Indexed
Indeks dengan partisi dapat dilakukan dengan 2 cara, yakin partisi indeks global dan partisi indeks secara lokal. Indeks global digambarkan dengan hubungan "one-too-many", dengan satu partisi indeks yang akan dipetakan ke banyak partisi tabel. Global indeks hanya dapat digunakan dengan partisi dengan jangkauan tertentu. Indeks lokal digambarkan dengan pemetaan hubungan "one-to-one" antara partisi indeks dan partisi tabel. Secara umum, indeks lokal mengijinkan pendekatan "divide and conquer" untuk menghasilkan eksekusi perintah SQL dengan cepat.
Indeks terpartisi secara lokal
Dengan menggunakan indeks yang terpartisi secara lokal, DBA dapat mengambil partisi tunggal dari tabel dan indeks secara "offline" untuk tujuan pemeliharaan (reorganisasi) tanpa mempengaruhi partisi tabel dan indeks lain. Dalam indeks partisi secara lokal, nilai kunci dan jumlah partisi indeks akan disesuaikan dengan partisi yang ada pada tabel dasar.
Gambar 2.39 Contoh pembuatan indeks terpartisi lokal
CREATE INDEX year_idx on all_fact(order_date)
LOCAL
(PARTITION name_idx1),
(PARTITION name_idx2), (PARTITION name_idx3);
ORACLE secara otomatis akan menggunakan indeks yang disesuaikan dengan jumlah partisi indeks pada tabel yang bersangkutan. Misalnya pada gambar 2,32, jika dibuat 4 indeks pada tabel all_fact, maka pembuatan indeks ini akan gagal karena jumlah partisi indeks dan partisi tabel tidak sesuai. Pemeliharaan akan mudah dilakukan jika menggunakan partisi indeks ini karena partisi tunggal dapat diambil secara "offline" dan indeks dapat dibangun tanpa mempengaruhi partisi lain di dalam tabel.
Indeks terpartisi secara global
Penggunaan indeks yang terpartisi secara global digunakan untuk semua indeks, kecuali salah satunya digunakan sebagai kunci pada partisi tabel. Penggunaan indeks yang terpartisi secara global ini sangat berguna implementasinya dalam aplikasi OLTP (Online Transaction Processing) dimana indeks yang lebih sedikit dibutuhkan daripada indeks partisi secara lokal. Kelemahan menggunakan indeks global ini adalah sulit dilakukan pemeliharaan, karena harus mengubah partisi pada tabel yang bersangkutan. Misalnya salah satu partisi tabel dihapus untuk reorganisasi tabel, maka seluruh indeks global yang berlaku akan terpengaruh, sehingga untuk mendefinisikan indeks global ini harus dipastikan DBA memiliki kebebasan untuk menentukan partisi sesuai dengan indeks yang diinginkan.
Gambar 2.40 menunjukkan contoh pembuatan indeks global pada partisi tabel:
CREATE INDEX item_idx on all_fact (item_nbr) GLOBAL
(PARTITION city_idx1 VALUES LESS THAN (100)), (PARTITION city_idx1 VALUES LESS THAN (200)), (PARTITION city_idx1 VALUES LESS THAN (300)), (PARTITION city_idx1 VALUES LESS THAN (400)), (PARTITION city_idx1 VALUES LESS THAN (500));
Partisi dengan penggunaan indeks global akan mengurangi penggunaan I/O secara signifikan dan dengan waktu yang singkat apabila pembuatan indeks global ini mempunyai pembagian dengan jarak yang baik.
Reverse Key Indexes
Teknik ini dirancang untuk mengeliminasi indeks yang digunakan saat memasukkan data pada aplikasi. Teknik ini terbatas saat digunakan untuk pembacaan jarak indeks.
3. Concatenated Index
Menurut Niemiec (2007, p39), sebuah indeks (single index) dapat terkait dengan beberapa kolom yang diberi indeks, disebut concatenated atau composite index. Oracle 9i memperkenalkan proses eksekusi "skip-scan index access " sebagai salah satu pilihan bagi optimizer ketika ada concatenated index. Oleh karena itu, perlu diperhatikan ketika menetapkan urutan kolom dalam indeks tersebut. Secara umum, kolom yang pertama kali dituliskan pada pembuatan indeks haruslah merupakan kolom yang paling sering digunakan sebagai selective column pada klausa WHERE. Sebagai contoh terdapat concatenated index pada kolom Empno, Ename, dan Deptno (Empno adalah bagian pertama, Ename adalah bagian kedua dan Deptno adalah bagian ketiga). Gambar 2.41 menunjukkan contoh query dimana concatenated index tidak dibaca karena leading columnnya adalah empno:
Gambar 2.41 Query dimana kolom pada where clause tidak berupa leading column
SELECT job, empno
FROM emp
WHERE ename = 'RICH';
Dalam hal ini, kolom ename bukanlah kolom pertama yang dinyatakan dalam concatenated index sehingga optimizer tidak akan mengeksekusi indeks yang telah dibuat. Namun pada Oracle 9i, diperkenalkan sebuah proses eksekusi "skip-scan index" yang memungkinkan optimizer menggunakan concatenated index meskipun kolom yang pertama kali dinyatakan dalam indeks tidak terdapat pada klausa WHERE. Optimizer akan tetap akan memilih proses eksekusi yang paling optimal, apakah dengan index skip-scan access, index fast full scan atau dengan full table scan.
Jika ada sebuah concatenated index pada sebuah query yang akan dieksekusi, maka proses eksekusi dengan "skip-scan index" akan lebih cepat dibandingkan dengan proses eksekusi dengan "index fast full scan". Misalnya pada contoh berikut:
Gambar 2.42 berikut menunjukkan contoh pembuatan concatenated index:
CREATE INDEX skip1 ON emp5(job,empno);
Gambar 2.43 menunjukkan query dimana concatenated index tidak digunakan karena kolom pada where clause bukanlah leading column:
SELECT count(*)
FROM emp5
WHERE empno = 7900;
Gambar 2.44 menunjukkan hasil eksekusi yang dihasilkan melalui pada query gambar 2.43 dengan SQL*Plus:
Elapsed: 00:00:03.13 (Result is a single row...not displayed)
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'SKIP1' (NON-UNIQUE)
Statistics
6826 consistent gets
6819 physical reads
Menurut Niemiec (2007, p48), jika urutan kolom yang dibuat dalam concatenated index tidak sesuai dengan query yang akan dieksekusi (leading column tidak ada di dalam where clause) maka query di atas dieksekusi dengan fast full scan yang memerlukan waktu eksekusi dalam waktu 3 menit 13 detik. Namun kita bisa memaksa optimizer mengabaikan hal itu meskipun leading column tidak berada dalam where clause dengan cara menggunakan hints "skip-scan" dimana cara penggunaannya seperti pada query gambar 2.45:
SELECT /*+ index_ss(emp5 skip1) */ count(*)
FROM emp5
WHERE empno = 7900;
Gambar 2.46 menunjukkan hasil eksekusi yang dihasilkan melalui query di atas dengan SQL*Plus:
Elapsed: 00:00:00.56
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 INDEX (SKIP SCAN) OF 'SKIP1' (NON-UNIQUE)
Statistics
21 consistent gets
17 physical reads
Jika query dijalankan dengan proses eksekusi "skip-scan index", maka waktu yang diperlukan untuk melakukan eksekusi query ini adalah 56 detik. Oleh karena itu, urutan kolom-kolom dalam sebuah concatenated index yang ingin dibuat perlulah diperhatikan. Hal ini sangat mempengaruhi optimizer melakukan proses eksekusi pada query yang bersangkutan.
4. Index-hint
Menurut Chan, Immanuel (2008 p16-8), index hint dalam sebuah query berguna ketika pengguna ingin "memaksakan" sebuah indeks dieksekusi oleh optimizer. Penggunaan index hint digunakan agar optimizer menggunakan proses eksekusi sebuah query dengan akses melalui indeks yang dinyatakan dalam sebuah index hint. Gambar 2.47 menunjukkan skema bagaimana index hint bekerja:
Gambar 2.47 Skema Index hint bekerja
Syntax pembuatan index hint adalah:
/*+ index(nama_table nama_indeks) */
Gambar 2.49 menunjukkan bagaimana cara pengunaan index hint
/*+ index(emp5 skip1) */
Ketika sebuah index hint digunakan, maka optimizer akan melakukan eksekusi sesuai dengan nama indeks yang dispesifikasikan pada query tersebut. Jika daftar kolom dan indeks bersesuaian, maka indeks tersebut yang akan dieksekusi. Jika indeks tersebut tidak ada, maka indeks yang berkaitan dengan tabel dan kolom yang dimaksud di awal dalam urutanlah yang akan dieksekusi.
5. Penggunaan Indeks yang Tidak Tepat
Menurut Niemiec (2007, p40) sebagai analogi, terdapat sebuah tabel "produk" yang mempunyai kolom "company_no". Perusahaan tersebut hanya memiliki 1 cabang sehingga nilai kolom tersebut dari semua baris di dalam tabel "produk" adalah 1. Jika ada indeks pada kolom tersebut, maka optimizer tidak akan menggunakan indeks tersebut. Hal ini disebabkan proses eksekusi dengan indeks akan memperlambat proses eksekusi dibandingkan proses eksekusi dengan "Table Access Full".
Gambar 2.50 menunjukkan penggunaan Table Access Full (Full Table Scan) meskipun terdapat index:
SELECT product_id, qty
FROM product
WHERE company_no = 1;
Gambar 2.51 menunjukkan hasil eksekusi yang dihasilkan melalui query gambar 2.50 dengan SQL*Plus:
Elapsed time: 405 seconds (all records are retrieved via a full table scan)
OPERATION OPTIONS OBJECT NAME
------------------ -------------- -----------
SELECT STATEMENT
TABLE ACCESS FULL PRODUCT
49,825 consistent gets (memory reads)
41,562 physical reads (disk reads)
Gambar 2.52 menunjukkan penggunaan hint untuk memaksa penggunaan index oleh optimizer
SELECT /*+ index(product company_idx1) */ product_id, qty
FROM product
WHERE company no = 1;
Gambar 2.53 menunjukkan hasil eksekusi yang dihasilkan melalui query gambar 2.52 dengan SQL*Plus:
Elapsed time: 725 seconds (all records retrieved using the index on company_no)
OPERATION OPTIONS OBJECT NAME
------------------ -------------- ----------- SELECT STATEMENT
TABLE ACCESS BY ROWID PRODUCT
INDEX RANGE SCAN COMPANY_IDX1
4,626,725 consistent gets (memory reads)
80,513 physical reads (disk reads)
Dari kedua perbandingan di atas terlihat bahwa jika sebuah index tidaklah optimal untuk menjalankan sebuah query maka optimizer akan lebih memilih untuk menggunakan "Table Access Full" karena penggunaan index tersebut membuat optimizer harus bekerja lebih dimana selain mencari semua baris dalam tabel, optimizer juga harus mencari baris dalam index yang dispesifikasikan.
Menurut Niemiec (2007, p41-42) Ada beberapa hal yang membuat index tidak dibaca oleh optimizer, yaitu:
Penggunaan operator '<>' dan '!='
Indeks hanya dapat digunakan untuk menemukan data yang terdapat di dalam tabel. Setiap terdapat operator not equal di dalam klausa WHERE, indeks yang terdapat di dalam kolom yang direferensikan tidak akan digunakan. Sebagai contoh pada gambar 2.54, terdapat sebuah tabel CUSTOMER dan terdapat indeks di dalam kolom CUST_RATING dan query yang ingin dijalankan adalah untuk mengambil data dimana cust_ratingnya bukan 'aa'. Proses eksekusi yang dijalankan adalah full table scan walaupun terdapat indeks pada kolom CUST_RATING.
SELECT cust_id, cust_name
FROM customers
WHERE cust_rating <> 'aa';
Penggunaan is null atau is not null
Ketika terdapat penggunaan sintaks is null atau is not null di dalam klausa WHERE maka optimizer tidak akan menggunakan indeks tersebut. Hal ini disebabkan karena nilai dari null tidak terdefinisi sehingga tidak ada nilai di dalam basis data yang sama dengan NULL. Gambar 2.55 menunjukkan contoh query yang menyebabkan full table scan walaupun pada kolom sal terdapat indeks karena pengunaan is null dalam klausa WHERE:
SELECT empno, ename, deptno
FROM emp
WHERE sal is null;
Selamat belajar!
