Menciptakan Index
Index adalah sebuah objek sistem basis data yang dapat mempercepat proses pencarian (query) data. Bayangkan sebuah buku, apabila kita hendak mencari suatu topik di dalam buku tersebut tanpa bantuan indeks yang biasanya terdapat di bagian belakang buku, berapa lama waktu yang kita butuhkan untuk menemukan topik tersebut? Oleh karena itu setiap buku biasanya selalu dilengkapi dengan indeks di halaman belakang, yang tujuannya tidak lain adalah untuk memudahkan pembaca untuk mencari suatu topik yang dikehendaki. Sama halnya apabila suatu table tidak memiliki index, maka pencarian data akan dilakukan satu persatu mulai record pertama sampai record terakhir. Hal ini yang dikenal dengan nama “full table scan” dan sedapat mungkin harus kita hindari. Kita dapat menciptakan index secara eksplisit menggunakan perintah CREATE INDEX atau secara otomatis.
Index merupakan objek database yang menyediakan akses secara langsung dan cepat terhadap data didalam table. Fungsinya adalah menekan kebutuhan disk I/O dengan menggunakan jalur terindex B*Tree untuk menemukan data secara cepat. Index secara otomatis digunakan dan dipelihara oleh Oracle. Sekali index diciptakan, tidak diperlukan interaksi user secara langsung terhadapnya.
Index secara logik dan fisik independen terhadap table yang diindex-nya. Ini berarti bahwa index dapat diciptakan atau dihapus setiap saat dan tidak memiliki efek terhadap table dasarnya atau index yang lain.
15.1. Bagaimana Index Diciptakan?
Terdapat dua tipe index yaitu uniqe dan non-unique. Unique index diciptakan secara otomatis ketika kita mendefinisikan kolom yang memiliki constraint PRIMARY KEY atau UNIQUE. Nama index sesuai dengan nama constraint yang diberikan.
Non-unique index adalah tipe index yang tidak mengharuskan nilai-nilai suatu kolom yang memiliki index ini bernilai unik. Sebagai contoh, kita dapat menciptakan index untuk kolom FOREIGN KEY untuk kepentingan join dalam query dalam rangka meningkatkan kecepatan pencarian data.
15.2. Kapan Index Digunakan?
Database Oracle akan sedapat mungkin akan menggunakan index yang telah diciptakan jika memungkinkan untuk meningkatkan kecepatan akses terhadap data. Hal ini berlaku otomatis dan biasanya tidak memerlukan banyak campur tangan user.
15.3. Teknik Optimasi
Sistem basis data Oracle menyediakan dua jenis teknik optimasi untuk meningkatkan kecepatan akses data.
- Rule-based optimization
Metode yang digunakan ketika Oracle memutuskan apakah tepat menggunakan index berdasarkan aturan internalnya. Server mengidentifikasi kolom-kolom yang diindex serta tipe-tipe indexnya. - Cost-based optimization
Metode ini menggunakan informasi statistik tabel bersama dengan index yang tersedia untuk mencari execution plan bagi perintah-perintah SQL.
15.4. Struktur Index
Index merupakan struktur data tersendiri yang tidak bergantung kepada struktur tabel. Setiap index terdiri dari nilai kolom dan penunjuk (atau ROWID) ke baris yang berisi nilai tersebut. Penunjuk tersebut secara langsung menunjuk ke baris yang tepat pada tabel, sehingga menghindari terjadinya full table scan.
15.5. B*Tree
Oracle menggunakan struktur index berimbang B*Tree. Struktur ini binary, dan merupakan struktur pencarian yang menyeimbangkan dirinya sendiri untuk menyamakan waktu akses ke setiap baris data. Struktur ini merupakan metode yang efisien untuk memastikan bahwa akses ke nilai tertentu akan memakan waktu yang kira-kira sama apakah baris data tersebut terletak pada awal, tengah, atau akhir tabel.
Setiap index yang dibangun oleh Oracle terdiri dari sejumlah halaman (atau cabang) penyimpanan yang tersusun dalam sebuah tree (pohon). Setiap halaman (atau cabang) menyimpan sekumpulan nilai kunci dan penunjuk ke halaman (atau cabang) yang lebih rendah dalam struktur tersebut sampai pada akhirnya nilai kunci tersebut mengindikasikan lokasi dari data itu sendiri. Pada level basis data, lokasi penentu dari suatu baris data disebut ROWID.
15.6. Tipe-tipe Index
Oracle memiliki beberapa tipe index berdasarkan unik tidaknya kolom dan jumlah kolom dalam sebuah index.
- Unique
- Memastikan bahwa nilai suatu kolom harus unik.
- Non-unique
- Memastikan cara tercepat untuk menampilkan data.
- Single column
- Hanya terdapat satu kolom dalam suatu index.
- Concatenated atau composite
- Dapat terdiri hingga 16 kolom dalam suatu index.
15.7. Menciptakan Index
Seperti telah kita pelajari diatas bahwa index dapat diciptakan secara otomatis atau secara eksplisit menggunakan perintah CREATE INDEX.
Sintaks
CREATE [UNIQUE] INDEX index
ON table ( column [ , column . . . );
dimana:
- UNIQUE: pilihan untuk menciptakan unique index
- index: nama index yang akan diciptakan
- table: nama tabel dari kolom yang akan diindex
- column: nama kolom dari tabel yang akan diindex
Contoh:
Menciptakan index untuk mempercepat akses terhadap kolom LAST_NAME pada tabel EMPLOYEE.
CREATE INDEX employee_last_name_idx
ON employee(last_name);
Index created.
Menampilkan data karyawan yang memiliki nama belakang Velasquez.
SELECT id, last_name
FROM employee
WHERE last_name = ‘Velasquez’;
Index tidak akan digunakan bila kita menggunakan fungsi pada kondisi pencarian (Oracle versi 7 kebawah).
SELECT id, last_name
FROM employee
WHERE UPPER(last_name) = ‘VELASQUEZ’;
Tips
Oracle 7 belum mendukung functional index sehingga kita perlu berhati-hati dalam menggunakan fungsi pada kolom terindex karena index yang telah kita ciptakan tidak akan digunakan.
Oracle Magazine contains technology strategy articles, sample code, tips, Oracle and partner news, how to articles for developers and DBAs, and more. Oracle (NASDAQ: ORCL) is the world’s largest enterprise software company.
For more information about Oracle, please visit their Web site at http://www.oracle.com. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
15.8. Banyak Tidak Selalu Lebih Baik
Lebih banyak index pada tabel tidak berarti akan mempercepat query. Semakin banyak index pada suatu tabel menyebabkan kelambatan pemrosesan perintah-perintah DML (Data Manipulation Language), karena setiap terjadi perubahan data maka index juga harus disesuaikan.
15.8.1. Kapan Diperlukan Index?
Index perlu diciptakan jika:
- Kolom sering digunakan dalam klausa WHERE atau dalam kondisi join.
- Kolom berisi nilai dengan jangkauan yang luas.
- Kolom berisi banyak nilai null.
- Beberapa kolom sering digunakan dalam klausa WHERE atau dalam kondisi join.
- Tabel berukuran besar dan sebagian besar query menampilkan data kurang dari 2-4%.
Catatan:
Perlu diingat bahwa jika kita ingin menerapkan keunikan, kita harus mendefinisikan unique contraint didalam definisi table. Kemudian, unique index otomatis diciptakan.
15.8.2. Kapan Tidak Diperlukan Index?
Perlu kita perhatikan bahwa terdapat beberapa kondisi dimana tidak diperlukan kehadiran index, yaitu ketika:
- Table kecil
- Kolom tidak sering digunakan sebagai kondisi dalam query
- Kebanyakan query menampilkan data lebih dari 2-4% dari seluruh data
- Table sering di-upadate
Tips
Jika anda menciptakan composite index (index yang terdiri dari beberapa kolom), index hanya akan digunakan apabila kita menyebutkan leading column dari index tersebut dalam query.
15.9. Mengkonfirmasi Index
Kita dapat menggunakan view USER_INDEXES dari data dictionary untuk mengkonfirmasi index yang telah kita ciptakan. Kita juga dapat mengecek kolom-kolom yang membangun suatu index dengan melakukan query terhadap view USER_IND_COLUMNS.
Contoh:
Menampilkan seluruh index yang telah dibuat untuk tabel EMPLOYEE.
SELECT ic.index_name, ic.column_name,
ic.column_position col_pos, ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = ‘EMPLOYEE’;
15.10. Menghapus Index
Index tidak dapat dimodifikasi. Kita harus menghapusnya terlebih dahulu dan menciptakannya kembali. Kita dapat menghapus definisi index dari data dictionary dengan perintah DROP INDEX.
Sintaks
DROP INDEX index
Contoh:
Menghapus index EMPLOYEE_LAST_NAME_IDX.
DROP INDEX employee_last_name_idx;
15.11. Tips
Beberapa tips berikut ini mungkin berguna agar query yang kita gunakan benar-benar menggunakan index yang telah kita ciptakan pada suatu table.
- Mengganti penggunaan simbol “!=” atau “<>” dengan “<” dan “>”
- Query berikut menyebabkan index diabaikan:
SELECT *
FROM employee
WHERE id <> 1;- Query berikut menyebabkan index digunakan:
SELECT *
FROM employee
WHERE ((id < 1) OR (id > 1)); - Meminimalkan atau menghilangkan sama sekali fungsi pada sisi kolom
- Query berikut menyebabkan index diabaikan:
CREATE INDEX employee_start_date_idx ON employee(start_date);
SELECT *
FROM employee
WHERE TO_DATE(start_date) = TO_DATE(sysdate);SELECT *
FROM employee
WHERE TRUNC(start_date) = TRUNC(sysdate);DROP INDEX employee_start_date_idx;
- Query berikut menyebabkan index digunakan:
CREATE INDEX employee_start_date_idx ON employee(start_date);
SELECT *
FROM employee
WHERE start_date BETWEEN TO_DATE(sysdate,’DD/MM/YYYY’) AND TO_DATE(sysdate,’DD/MM/YYYY’) + .99999;SELECT *
FROM employee
WHERE start_date BETWEEN TO_DATE(’09/05/1991′,’DD/MM/YYYY’) AND TO_DATE(’09/05/1991′,’DD/MM/YYYY’) + .99999;DROP INDEX employee_start_date_idx;
- Menghindari penggunaan fungsi penggabungan baik CONCAT() maupun tanda “||” pada sisi kolom
- Query berikut menyebabkan index diabaikan:
CREATE INDEX employee_first_name_idx ON employee(first_name);
CREATE INDEX employee_last_name_idx ON employee(last_name);
SELECT *
FROM employee
WHERE first_name||last_name = ‘CarmenVelasquez’;SELECT *
FROM employee
WHERE CONCAT(first_name, last_name) = ‘CarmenVelasquez’;DROP INDEX employee_first_name_idx;
DROP INDEX employee_last_name_idx;
- Query berikut menyebabkan index digunakan:
CREATE INDEX employee_first_name_idx ON employee(first_name);
CREATE INDEX employee_last_name_idx ON employee(last_name);
SELECT *
FROM employee
WHERE first_name = ‘Carmen’ AND last_name = ‘Velasquez’;DROP INDEX employee_first_name_idx;
DROP INDEX employee_last_name_idx;
- Mengganti penggunaan fungsi SUBSTR() dengan LIKE
- Query berikut menyebabkan index diabaikan:
SELECT *
FROM region
WHERE SUBSTR(name,1,3) = ‘Eur’;- Query berikut menyebabkan index digunakan:
SELECT *
FROM region
WHERE name LIKE ‘Eur%’; - Menghindari penggunaan IS NULL dan IS NOT NULL pada query. Sebaiknya selalu memberikan nilai default pada kolom untuk menghindari pencarian dengan IS NULL atau IS NOT NULL.
- Query berikut menyebabkan index diabaikan:
- Query berikut menyebabkan index digunakan:
15.12. Latihan
1. Buatlah index dengan nama S_EMP_LAST_NAME_IDX untuk meningkatkan kecepatan akses terhadap nama belakang pegawai.
CREATE INDEX employee_last_name_idx
ON employee(last_name);
2. Tampilkan index-index yang telah dibuat untuk table S_EMP dengan perintah berikut:
SELECT ic.index_name, ic.column_name, ic.column_position col_pos, ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = ‘S_EMP’;
3. Hapuslah index EMPLOYEE_LAST_NAME_IDX.
DROP INDEX employee_last_name_idx;
4. Buatlah sebuah non-unique index untuk meningkatkan kecepatan akses terhadap kode atasan pegawai dan kode departemen tempat pegawai bekerja.
CREATE INDEX employee_manager_id_idx
ON employee(manager_id);
CREATE INDEX employee_dept_id_idx
ON employee(dept_id);
5. Buatlah index dengan nama EMPLOYEE_FIRST_NAME_IDX untuk meningkatkan kecepatan akses terhadap nama depan pegawai.
CREATE INDEX employee_first_name_idx
ON employee(first_name);
6. Tampilkan index-index yang telah dibuat untuk table EMPLOYEE dan DEPARTMENT.
SELECT ic.index_name, ic.column_name, ic.column_position col_pos, ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND (ic.table_name IN (’EMPLOYEE’,'DEPARTMENT’));
Simpan query diatas menjadi script dengan nama INDEX_SCRIPT.SQL.
7. Hapuslah constraint PRIMARY KEY pada table EMPLOYEE.
ALTER TABLE employee
DROP PRIMARY KEY CASCADE;
8. Tampilkan script INDEX_SCRIPT.SQL dan amatilah apa yang terjadi pada kode pegawai.
@INDEX_SCRIPT;
9. Berikanlah kembali constraint PRIMARY KEY terhadap kode pegawai dan jalankan sekali lagi script INDEX_SCRIPT.SQL.
ALTER TABLE employee
ADD CONSTRAINT employee_id_pk PRIMARY KEY(id);
@INDEX_SCRIPT;
10. Hapuslah index EMPLOYEE_LAST_NAME_IDX.
DROP INDEX employee_last_name_idx;
Silahkan melanjutkan membaca pembahasan Mengontrol Akses User
Catatan:
Untuk mempraktekkan artikel-artikel mengenai Oracle SQL & PL/SQL Anda dapat mendownload script table disini, lalu jalankan sesuai live demo (browser Anda harus mendukung Flash Player).
 |
Buku ini membahas berbagai macam tip dan trik yang sangat berguna bagi Anda pembaca pengguna database Oracle. Pemula, mahasiswa, programmer, maupun database administrator yang mengharapkan solusi cepat dalam menangani permasalahan-permasalahan dalam penggunaan database Oracle dapat membaca buku ini. Anda tidak harus membaca buku ini secara urut karena buku ini di susun berdasarkan topik permasalahan, dan Anda dapat membaca topik-topik yang sesuai dengan permasalahan yang Anda hadapi. Dapat di beli di toko buku Gramedia, Gunung Agung, Uranus, Karisma dsb. Harga Rp. 24.800,- (157 halaman) |
[…] Silahkan melanjutkan membaca pembahasan Menciptakan Index […]