OPTIMASI QUERY - Optimasi Perintah SQL

Cara mengoptimalkan perintah SQL tidak cukup dengan mendesain aplikasinya saja. Dalam mendesain database, seringkali lokasi fisik di nomor duakan. Terlebih hanya fokus terhadap desain logik saja. Padahal untuk menampilkan hasil query dibutuhkan pencarian yang melibatkan struktur fisik penyimpanan data. Intinya dalam mengoptimalkan query adalah dengan meminimalkan “jalur” pencarian untuk menemukan data yang disimpan dalam lokasi fisik.

Penggunaan index yang tidak tepat untuk kerja, tidak akan meningkatkan kecepatan akses data. Hal tersebut dikarenakan index pada database digunakan untuk meningkatkan kecepatan akses data. Cara kerjanya, pada saat query dijalankan, index mencari data dan menentukan nilai ROWID yang membantu menemukan lokasi data secara fisik di disk.

Misal digunakan index yang melibatkan tiga buah kolom yang mengurutkan kolom menurut kota, provinsi, dan kode pos dari tabel karyawan, sebagai berikut :

CREATE INDEX idx_kota_prov_kodepos

ON karyawan(kota,provinsi,kode_pos)

TABLESPACE INDX;

Kemudian user melakukan query sebagai berikut :

SELECT* FROM karyawan WHERE provinsi=’Jawa Barat’;

Pada saat melakukan query tersebut, index tidak akan digunakan karena kolom pertama (kota) tidak digunakan dalam klausa WHERE. Jika user sering melakukan query ini, maka kolom index harus diurutkan menurut provinsi. Selain itu, proses pencarian data akan lebih cepat jika data terletak pada block tabel yang berdekatan daripada harus mencari dibeberapa data file yang terletak pada block yang berbeda.

Misal pada perintah SQL berikut ini :

SELECT* FROM karyawan

WHERE id BETWEEN 1010 and 2010;

Query di atas akan melakukan “scan” terhadap sedikit data block jika tabel karyawan diatas diurutkan berdasarkan kolom id. Untuk mengurutkan berdasarkan kolom yang berbeda-beda maka tabel disimpan dalam flat file, kemudian tabel diekspor dan diurutkan sesuai kebutuhan.

Alternatif yang lain, bisa digunakan perintah untuk membuat tabel lain yang memiliki urutan yang berbeda dari tabel asal, seperti perintah SQL berikut :

CREATE TABLE karyawan_urut

AS SELECT* FROM karyawan

ORDER BY id;

Pada SQl diatas, tabel karyawan_urut berisi data yang sama dengan tabel karyawan hanya datanya terurut berdasarkan kolom id.

OPTIMASI QUERY - Informasi Jalur Akses Query

Pada Database Oracle, informasi untuk melihat jalur akses yang akan digunakan untuk eksekusi database saat melakukan query dapat dilihat dengan menggunakan perintahexplain plain. Informasi ini disimpan dalam tabel PLAN_TABLE yang terdapat di schema user yang mengeksekusi perintah tersebut.

Sebelum melakukan perintah explain plan, terlebih dahulu buat table PLAN_TABLE dengan menggunakan script utlxplan.sql yang diambil dari \%ORACLE_HOME%\RDBMS\ADMIN.

Setelah itu table PLAN_TABLE dapatdigunakan seperti contoh berikut :

SQL>explain plan

Set statement_id=’test1’

Into plan_table for

Select * from karyawan where gaji=2000000;

Dalam PLAN_TABLE rencana eksekusi diatas dikenal dengan nama test1 yang terdefinisikan pada kolom statement_id.

Untuk melihat rencana eksekusi dari test1, digunakan perintah SELECT berikut :

SELECT LPAD(‘’,2*Level)| |Operation| |’ ‘| |Options| |’ ‘| |Object_Name Q_Plan

FROM plan_table

WHERE statement_id=’test1’

CONNECT BY PRIOR id=parent_id AND statement_id=’test1’

START WITH id=0 AND statement_id=’test1’;

Contoh hasil dari eksekusi query tersebut :

Q_PLAN

SELECT STATEMENT

            TABLE ACCESS FULL KARYAWAN

Output tersebut dibaca mulai dari  yang  menjorok paling dalam yaitu : TABLE ACCESS FULL KARYAWAN. Dikarenakan klausa WHERE melibatkan kolom gaji namun kolom gaji tidak ada index-nya, maka Oracle melakukan full table scan. Setelah seluruh table karyawan selesai dibaca, selanjutnya adalah SELECT STATEMENT yang berfungsi untuk menampilkan hasil query.