Ada banyak cara yang dapat dan harus dilakukan agar proses server database dapat berlangsung cepat. Dalam pengembangan aplikasi database, hal ini biasa disebut dengan istilah performance tuning, karena berkenaan dengan kinerja/performance database. Dalam Oracle, dikenal banyak tuning untuk mendapatkan kinerja yang optimal. Beberapa di antaranya adalah instant tuning, memory tuning, sql tuning, dan sebagainya.Keberhasilan pengembangan aplikasi database yang besar acapkali menjadi terkesan kurang dihargai, apabila ternyata database tersebut relatif lambat memberikan data yang diperlukan. Sebenarnya hal ini dapat dimaklumi apabila kita memahami internal database. Misalkan, memilih seratus data tertentu dari satu juta data yang tersimpan di dalam database, tentu saja akan membutuhkan waktu yang lama. Namun demikian, sebagian besar user tidak memahami hal internal database. Yang mereka inginkan database tersebut harus cepat memberikan data yang dicari apapun kriterianya. Ada banyak cara yang dapat dan harus dilakukan agar proses server database dapat berlangsung cepat. Dalam pengembangan aplikasi database biasa disebut dengan istilah performance tuning. Dalam Oracle dikenal banyak tuning untuk mendapatkan kinerja yang optimal. Beberapa di antaranya adalah instant tuning, memory tuning, sql tuning, dan sebagainya. Artikel ini membahas sebagian dari sql tuning.Sebagian besar dari SQL yang digunakan di dalam pengembangan aplikasi basis data adalah perintah SELECT yang ditujukan untuk menarik/mengambil sejumlah baris data dari suatu tabel. Misalkan, SELECT * FROM T1 berarti "tarik semua data - ditandai dengan * - dari tabel T1". Contoh lain: SELECT DATA1, DATA2 FROM T2 berarti "tarik DATA1 dan DATA2 dari tabel T2". Mengingat SELECT merupakan perintah SQL yang paling banyak digunakan, maka artikel ini memfokuskan pada optimalisasi perintah SELECT untuk mendapatkan kinerja yang paling optimal yang antara lain ditandai dengan kecepatan akses/proses.

SELECT memerlukan waktu pemrosesan yang panjang dan sangat tergantung pada klausul SELECT yang digunakan. Untuk mempercepatnya, setiap programmer harus memperhatikan teknik-teknik optimalisasi SQL.

1. Pemilihan Metode Akses
Dalam mengambil/menarik data dari suatu table, SQL melakukan salah satu dari alternatif berikut:
- Pengaksesan secara bertahap (sequential access). Teknik ini juga sering direferensikan sebagai full table scanning, karena SQL akan mengambil data yang diinginkan dengan lebih dulu merunut/melacak data satu demi satu baris, dimulai dari record/baris pertama. Apabila proses ini melibatkan tabel dengan jumlah record yang banyak, maka metode ini tidak akan efisien, karena membutuhkan waktu yang lama. Contoh: SELECT * FROM T_PROFILE_ANUMBER.
- Pengaksesan secara langsung (direct access). Dalam teknik ini, SQL hanya membaca baris/data yang diminta. Metode ini biasa juga disebut access by index, karena untuk dapat melakukan ini diperlukan indeks pada kolom yang digunakan untuk kriteria pencarian. Dengan pembacaan langsung pada baris yang dibutuhkan, maka metode ini membutuhkan waktu yang lebih cepat dari sequential access. Contoh: SELECT * FROM T_PROFILE_ANUMBER WHERE ANUMBER = '0215215522', di mana kolom ANUMBER merupakan kolom yang diindex.

2. Pembuatan Index
Pencarian data menggunakan SELECT biasanya dengan menggunakan kriteria pencarian menggunakan klausa WHERE, atau HAVING. Contoh berikut menunjukkan adanya spesifikasi pencarian dengan batasan K3 > 5:

SELECT * FROM T1 WHERE K3 > 5

Kolom yang akan digunakan dalam spesifikasi pencarian data perlu dibuat index agar dapat menggunakan metode direct access. Dalam contoh di atas, maka proses SELECT akan lebih cepat memberikan hasil apabila dibuatkan index pada kolom K3 dari tabel T1.

Contoh lain, misalkan suatu tabel T1 mempunyai kolom (K1, K2, K3, K4). Apabila dalam proses SELECT nantinya akan lebih banyak menggunakan kolom K4 sebagai batasan/kriteria pencarian, maka disarankan untuk membuat index dari kolom K4.

Cara pembuatan index dapat menggunakan perintah sebagai berikut:

CREATE {UNIQUE} INDEX nama_indeks
ON identifikator_tabel ((nama_kolom {ASC|DESC}), ...);

CREATE INDEX T1_K4_NDX
ON T1(K4);

Index dapat dibuat untuk satu kolom atau gabungan dari beberapa kolom.

Berikut pedoman untuk pemilihan kolom yang perlu dibuatkan index:
- Index unik pada kolom kunci utama (primary key)
- Index pada kolom kunci asing (foreign key)
- Index pada kolom yang digunakan sebagai kriteria pemilihan
- Index pada kolom yang digunakan untuk pengurutan (order by)
- Index pada kolom yang digunakan untuk pengelompokan (group by)

3. Perbaikan Pernyataan SELECT

3.1. Hindari Penggunaan SELECT *
Fokuslah hanya pada kolom yang memang benar-benar dibutuhkan. Misalkan diasumsikan bahwa tabel T_CALLS memiliki kolom (ANUMBER, BNUMBER, TGLJAM, DURASI, TRIN, TROUT), maka jika diinginkan informasi panggilan dari A ke B dan durasinya maka dapat dilakukan dengan perintah sebagai berikut:

SELECT * FROM T_CALLS;

Perintah SQL di atas akan lebih efisien jika dirubah menjadi:

SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS

3.2. Gunakan Kriteria
Hindari pengaksesan seluruh data, tetapi fokuslah pada kriteria data yang diperlukan sesuai kebutuhan. Contoh apabila dibutuhkan informasi ke Jakarta maka:

SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS;

Perintah SQL di atas akan lebih efisien, jika dirubah menjadi:

SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE SUBSTR(BNUMBER, 1, 3) = '021';

3.3. Hindari OR, gunakan UNION
Operator OR membutuhkan waktu yang banyak. Bila memungkinkan, gunakan operator UNION yang setara.

SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE SUBSTR(BNUMBER, 1, 3) = '021' OR
SUBSTR(BNUMBER, 1, 3) = '022';

Perintah SQL di atas akan lebih efisien jika dirubah menjadi:

SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE SUBSTR(BNUMBER, 1, 3) = '021'
UNION
SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE SUBSTR(BNUMBER, 1, 3) = '022';

3.4. Hindari NOT
Hindari operator NOT dan ganti dengan operator perbandingan bila memungkinkan:

SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE NOT (DURASI > 100);

Perintah SQL di atas akan lebih efisien jika dirubah menjadi:

SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE (DURASI <= 100);

3.5. Hindari Ekspresi Matematika di Sisi Kiri Operand
Bila kriteria pencarian memerlukan ekspresi matematika, maka posisikan ekspresi tersebut pada sebelah kanan operator jika memungkinkan.

SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE (DURASI + 40 > 100);

Perintah SQL di atas akan lebih efisien jika dirubah menjadi:

SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE (DURASI > 60);

3.6. Hindari Operator IN
Penggunaan operator IN dalam kriteria pencarian merupakan pernyataan SELECT yang mahal, sehingga perlu dihindari. Berikut cara menghindarinya untuk dua bentuk penggunaan IN.

Penggunaan operator IN bentuk pertama:

SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE SUBSTR(ANUMBER, 1, 3) IN ('021', '022');

Perintah SQL di atas akan lebih efisien jika dirubah menjadi:

SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE SUBSTR(ANUMBER, 1, 3) = '021'
OR SUBSTR(ANUMBER, 1, 3) = '022';

Untuk selanjutnya, perhatikan paragraf 3.3 untuk mengindari penggunaan operator OR.

Penggunaan operator IN bentuk kedua:

SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE SUBSTR(ANUMBER, 1, 3)
IN (SELECT KODEAREA FROM T_AREACODES);

Perintah SQL di atas akan lebih efisien jika dirubah menjadi:

SELECT T1.ANUMBER, T1.BNUMBER, T1.DURASI
FROM T_CALLS T1, T_AREACODES T2
WHERE SUBSTR(T1.ANUMBER, 1, 3) = T2.KODEAREA;

3.7. Hindari Operator EXIST
Sama halnya dengan EXIST, bila memungkinkan hindari penggunaan operator ini.

SELECT A.ANUMBER, A.BNUMBER, A.DURASI FROM T_CALLS A
WHERE EXIST
(SELECT B.KODEAREA FROM T_AREACODES B
WHERE B.KODEAREA = SUBSTR(A.ANUMBER, 1, 3));

Perintah SQL di atas akan lebih efisien jika dirubah menjadi:

SELECT A.ANUMBER, A.BNUMBER, A.DURASI
FROM T_CALLS A, T_AREACODES B
WHERE SUBSTR(A.ANUMBER, 1, 3) = B.KODEAREA;

3.8. Hindari Operator LIKE
Penggunaan operator LIKE akan menyebabkan full table scanning. Oleh karena itu, perlu dirubah dengan ekspresi yang sama bila memungkinkan.

SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE ANUMBER LIKE '021%';

Perintah SQL di atas akan lebih efisien jika dirubah menjadi:

SELECT ANUMBER, BNUMBER, DURASI FROM T_CALLS
WHERE ANUMBER >= '021' AND ANUMBER < '022';

3.9. Urutan Nama Kolom
Penulisan nama kolom disarankan menggunakan urutan sebagaimana kolom tersebut didefinisikan di dalam tabel. Misalkan tabel berikut mempunyai kolom dengan urutan sebagaimana tertulis: T1(K1, K2, K3, K4, K5) di mana terdapat composite index untuk kolom K4 dan K5;

SELECT K2, K5, K3 FROM T1;

Perintah SQL di atas akan lebih efisien jika dirubah menjadi:

SELECT K2, K3, K5 FROM T1;

Pada penggunaan index dari beberapa kolom (composite index) sebutkan nama kolomnya dalam urutan yang benar sesuai dengan urutan kolom tersebut di dalam tabelnya.

SELECT K2, K3, K5 FROM T1
WHERE K5 > 50 AND K4 = '0215215522';

Perintah SQL di atas akan lebih efisien jika dirubah menjadi:

SELECT K2, K3, K5 FROM T1
WHERE K4 = '0215215522' AND K5 > 50;

3.10. Gunakan Konsensus Penulisan SQL
SQL merupakan bahasa pemrograman tingkat tinggi generasi keempat. Secara internal, Oracle akan mengolah lebih dulu setiap pernyataan SQL untuk mendapatkan hasil yang diinginkan. Jika Oracle menemukan suatu pernyataan SQL yang dimasukkan pertama kali, maka Oracle akan mengolah pernyataan tersebut menjadi pernyataan perintah low level yang dimengerti oleh server database untuk mendapatkan hasilnya. Perintah low level ini akan didaftarkan oleh Oracle pada suatu area memory khusus yang disebut LIBRARY CACHE untuk keperluan lebih lanjut. Jika suatu saat pernyataan SQL yang sama muncul lagi, maka Oracle tidak akan merubah SQL menjadi low level, namun menggunakan perintah low level yang sudah ada terdaftar pada LIBRARY CACHE. Dengan demikian, mengurangi proses translasi dari bahasa tingkat tinggi ke bahasa low level.

Untuk itu, disarankan paling tidak dua hal:
- Penggunaan pernyataan SQL yang sama. Di sinilah perlunya konsensus cara penulisan SQL termasuk penggunaan huruf besar dan kecil.
- Penggunaan bind variable. [Sumber : gematel.com/Penulis : M.Sovan Hadibowo - Lab RelSec/QRS - TELKOM RisTI]

-----Original Message-----
From: Daryatmo
Sent: 09 September 2003 10:15
To: ilmukomputer@yahoogroups.com
Subject: [ilmukomputer] FW: optimalisasi sql untuk kecepatan akses basis data

Dear rekans,

Ada article bagus untuk para pemakai oracle or SQL.
Mudah2an bermanfaat.

Regards,
Amo