Optimasi Query Materi 4

1. Optimasi QueryMateri 4 Eko Prasetyo TeknikInformatika UniversitasBhayangkara Surabaya 2012 SistemManajemen Basis Data

2. Tabel-tabel yang digunakan lecturer enrolled students faculty grade_scale course

3. Optimasi Query • Data yang tersimpandalam database semakin lama akansemakinbesarukuranatauvolumenya. • Kalautidakdidukungdengankecepatanakses yang memadaimakaakansemakinmenurununjukkerjanya. • Ukuranunjukkerjadalamhalinikecepatanakses data dipengaruhiolehbanyakfaktor, seperti: perintah SQL, index, denormalisasi, cluster.

4. OptimasiPadaPerintah SQL • Desainaplikasisajatidakcukupuntukmeningkatkanunjukkerjaharusdidukungdenganoptimasidariperintah SQL yang digunakanpadaaplikasitersebut. • Dalammendesain database, seringkalilokasifisik data tidakmenjadiperhatianpenting. • Karenahanyadesainlogiksaja yang diperhatikan. • Padahaluntukmenampilkanhasil query dibutuhkanpencarian yang melibatkanstrukturfisikpenyimpanan data. • Intidarioptimasi query adalahmeminimalkan “jalur” pencarianuntukmenemukan data yang disimpandalamlokasifisik. • Indexpada database digunakanuntukmeningkatkankecepatanakses data. • Padasaat query dijalankan, index mencari data danmenentukannilaiROWID yang membantumenemukanlokasi datasecarafisikdi disk. • Akantetapipenggunaanindex yang tidaktepat, tidakakanmeningkatkanunjukkerjadalamhalinikecepatanakses data. • Indekbertindakseperti ‘daftarisi’ dalamsebuahbuku.

5. OptimasiPadaPerintah SQL • Operasi-operasi query: seleksi, proyeksi, join • Seleksi: Operasipemilahanbaris-baris data yang memenuhikriteriadalamklausa WHERE • Jikatidakterdapatindekpadakolom yang digunakansebagai operand seleksi, maka DBMS akan men-scan seluruhisitabeltersebut • Proyeksi: Operasipemilihankolomtertentudaritabel yang akanditampilkan • Select sid, name from students  Memilihkolomsiddan name daritabel students • Operasi yang mahaladalahjikamenggunakan operator DISTINCT • Select DISTINCT age from students  Akanmendapatkanusiaberapasajapadamahasiswadalamtabel students • Join: Operasipenggabungan 2 ataulebihtabel, inimerupakanoperasi yang umumdanmahal. • Ada 3 jenis: left join, right join, dan inner join (kadangjugamenggunakan operator WHERE untukmenggabungkan)

6. Index • Misal: Padatabel students akandibuatkan index yang melibatkan 1 kolom yang mengurutkan data menurutnama. • create index idx_students_name on students (name) • Kemudianmelakukan query sebagaiberikut: • Select a.sid, a.name from students as a where a.name like ‘R%’ • Select a.sid, a.name from students as a where a.name in (‘Sandi’,’Wati’) • Select a.sid, a.name from students as a where a.name not in (‘Sandi’,’Wati’) and a.age > 20 • Kedua query akanmenggunakanindeksecara optimal. • Query 1 dan 2: optimal • Query 3: optimal untuk name, tidakuntuk age (karenatidakadaindekuntuk age) • Untukmenghapus index, gunakanperintah alter dan drop • Misal: alter table students drop index idx_students_name

7. Index • Misal: Padatabel students akandibuatkan index yang melibatkan 2 kolom yang mengurutkan data menurut fid (kodefakultas) dan lid (kodedosenwali) • create index idx_students_fid_lid on students (fid, lid) • Kemudianmelakukan query sebagaiberikut: • SELECT a.name, a.birthday from students as a where a.lid in (‘9001’,’9003’)  Indektidakdigunakan, karenaseleksitidakmelibatkankolompertamaindek • SELECT a.name, a.birthday from students as a where a.fid = ‘1’  Indektidakakandigunakansecara optimal karenapembuangan resource pelibatankolom lid dalamindek • SELECT a.name, a.birthday from students as a where a.fid = ‘1’ and a.lid in (‘9001’,’9003’)  Indekdigunakansecarabaikdan optimal.

8. OptimasiAplikasi • Dalampembuatanaplikasi, yang perlumendapatperhatianadalahapakahaksesterhadap data sudahefisien. • Efisiendalamhalpenggunaanobyek yang mendukungkecepatanakses, seperti index atau cluster. • Kemudianjugabagaimanacara database didesain. Apakahdesain database sudahmelakukannormalisasi data secaratepat. • Kadangkalanormalisasisampai level yang kesekian, tidakmenjaminsuatudesain yang efisien. • Untukmembuatdesain yang lebihtepat, kadangsetelahmelakukannormalisasiperludilakukandenormalisasi. • Misalnyatabel yang hubungannya one-to-one danseringdiaksesbersamalebihbaikdisatukandalamsatutabel.

9. Denormalisasi • Misal, padatabel students seringadapengaksesanantaratabel students dan lecturer untukmenampikannamadosenwalipadaKRS/KHSmahasiswa, untukkasussepertiinisebaiknyadilakukandenormalisasidenganmenambahkankolom ‘lecturer_name’padatabel student • Manfaatkankorelasi sub-query update • Lakukandenormalisasitabel students denganmenambahkolomlecturer_name • Isikolomlecturer_namedidapatkandaritabel lecturer alter table students add column lecturer_namevarchar(30) update students as a set lecturer_name = (select b.name from lecturer as b where b.lid=a.lid)

10. Cluster • Cluster adalahsuatu segment yang menyimpan data daritabel yang berbedadalamsuatustrukturfisik disk yang berdekatan. • Konfigurasiinibermanfaatuntukaksesdata daribeberapatabel yang seringdi-query. • Penggunaancluster secaratepatdilaksanakansetelahmenganalisatabel-tabelmanasaja yang seringdi-query secarabersamaanmenggunaanperintah SQL join. • Jikaaplikasiseringmelakukan query denganmenggunakansuatukolomyang beradapadaklausa WHERE, makaharusdigunakan index yang melibatkankolomtersebut. • Penggunaanindex yang tepatbergantungpadajenisnilai yang terdapatdalamkolomyang akandiindex. • DalamRDBMS Oracle, index B-Tree digunakanuntukkolomyang mengandungnilai yang cukupbervariasi, sedangkanuntuknilai yang tidakmemilikivariasicukupbanyak, lebihbaikmenggunakan index bitmap.

11. Any Question ? To Be Continued … Materi 5