Database Management Systems Overview Evaluasi Query ADVANCED DATABASE

Database Management Systems Overview Evaluasi Query ADVANCED DATABASE Sumber: Fundamentals of Database Systems, Third Edition ch. 26, Elmasri DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 1/23

Pokok Bahasan v v v Informasi deskriptif apa yang disimpan oleh DBMS dalam katalognya ? Alternatif apa saja yang perlu dipertimbangkan dalam melakukan retrieving baris-baris dari sebuah tabel ? Mengapa DBMS mengimplementasikan beberapa algoritma untuk setiap operasi aljabar ? Faktor-faktor apa yang memperngaruhi kinerja relatif dari algoritma yang berbeda ? Apa yang dimaksud dengan rencana evaluasi query dan bagaimana rencana tersebut direpresentasikan ? Mengapa upaya untuk memperoleh sebuah rencana evaluasi query yang baik dianggap penting ? Bagaimana hal ini dapat dilakukan dalam relational DBMS ? DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 2/23

Overview Evaluasi Query v Rencana (Plan): Berupa tree dari operator-operator Aljabar Relasional (ops. R. A. ), yang diikuti dengan pilihan algoritma tertentu untuk setiap operator. § v Setiap operator secara tipikal diimplementasikan dengan menggunakan antar muka `tarik’, yaitu bilamana sebuah operator `ditarik’ utk “ouput tuples” berikutnya, maka operator tersebut akan `menarik’ semua input-nya dan melakukan perhitungan terhadap inputs tsb menggunakan operator yang dimaksud. Dua issue utama dalam optimasi query: § Utk sebuah query yang diberikan, rencana apa yang diperlukan ? • Diperlukan algorithm utk melacak ruang rencana guna memperoleh nilai (estimasi) rencana yang termurah. § v v Persoalan: bagaimana mengestimasi biaya suatu rencana? Tujuan ideal: Cari rencana terbaik. Prakteknya: Hindari rencana-rencana terjelek ! Dalam kuliah ini dibahas pendekatan/teknik evaluasi query yang digunakan dalam System R. DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 3/23

Beberapa Teknik Umum v Algoritma-algoritma utk mengevaluasi operator-operator relasional menggunakan beberapa ide sederhana berikut secara intensif: § Indexing: Dpt menggunakan WHERE conditions utk melakukan retrieve sekumpulan tuples yang relatif sedikit (yang memenuhi kondisi selections atau joins) § Iteration: Kadangkala, lebih cepat untuk men-scan semua tuples, walaupun terdapat sebuah index. (Dan kadangkala, kita dapat men-scan data entries dalam sebuah index dari pada harus men-scan tabel itu sendiri) § Partitioning: Dengan menggunakan sorting atau hashing, kita dpt mempartisi input tuples dan menggantikan sebuah operasi yang mahal dengan operasi-operasi serupa yang melibatkan input tuples yang lebih sedikit. Evaluasi query yang dijelaskan dlm bab ini dilakukan dengan memperhatikan teknik-teknik di atas ! DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 4/23

Katalog Sistem v Evaluasi query membutuhkan informasi mengenai relasi dan indexes yang dilibatkan. Untuk ini, Katalog sistem secara tipikal paling tidak harus berisikan: § § § v Katalog sistem di-update secara periodik. § v v # tuples (NTuples) dan # pages (NPages) utk setiap relasi. # nilai-nilai key yang berbeda (NKeys) dan # pages (INPages) untuk setiap index. Tinggi index (IHeight), range nilai-nilai key utk setiap index: maksimum (High) dan minimum (Low) untuk setiap tree index. Proses updating yang dilakukan untu setiap kali terjadi perubahan data mempunyai biaya I/O yang tinggi. Untuk ini dapat digunakan aproksimasi, walaupun akan terdapat sedikit inkonsistensi Informasi yang lebih detil (misalnya, histogram dari nilai-nilai dalam beberapa field) kadangkala juga perlu disimpan. Relasi yang akan dijadikan contoh dalam bab ini: § Sailors (sid: integer, sname: string, rating: integer, age: real) : 500 pages, 80 tuples § Reserves (sid: integer, bid: integer, day: date, rnam: string) : 1000 pages, 100 tuples DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 5/23

Lintasan Akses (Access Paths) v Sebuah lintasan akses merupakan metode utk melakukan retrieving tuples, berupa: § File scan, atau index yang matches (memenuhi) suatu seleksi (dalam suatu query) v v Salah satu bentuk seleksi yang sederhana disebut “Conjunctive Normal Form (CNF)” : konjungsi kondisi dalam bentuk attr op value (op salah satu dari operator perbandingan <, , =, , >, atau . Sebuah tree index matches (kunjungsi dari) terms yang hanya melibatkan attributes dalam prefix dari search key. Contoh: § v Tree index pada <a, b, c> matches seleksi a=5 AND b=3, dan a=5 AND b>6, tetapi bukan b=3 atau b=5 AND c=3 (<a>, <a, b> prefix, tetapi <b>, <a, c> dan <b, c> bukan prefix) Sebuah hash index matches (konjungsi dari) terms yang memiliki sebuah term attribute = value utk setiap attribut dalam search key dari index. Contoh: § Hash index pada <a, b, c> matches a=5 AND b=3 AND c=5; tetapi BUKAN untuk b=3, atau a=5 AND b=3, atau a>5 AND b=3 AND c=5. DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 6/23

Catatan utk Seleksi yang Komplek SELECT …. . FROM …. . WHERE (day<8/9/94 AND rname=‘Paul’) OR bid=5 OR sid=3 Kondisi seleksi harus dikonversi terlebih dahulu ke conjunctive normal form (CNF): (day<8/9/94 OR bid=5 OR sid=3 ) AND (rname=‘Paul’ OR bid=5 OR sid=3) v Dalam kuliah untuk bab ini, seleksi hanya dibatasi untuk kondisi yang TIDAK melibatkan OR. Lihat buku referensi utk kasus-kasus umum. v DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 7/23

Satu Pendekatan utk Seleksi v Cari lintasan akses yang paling selektif, gunakan lintasan tsb untuk melakukan retrieval tuples, kemudian lakukan seleski pada terms tersisa yang tidak match dengan index: § § § Most selective access path: Sebuah index atau file scan yang diperkirakan memerlukan jumlah page I/O terkecil. Terms yang match dengan index akan mereduksi jumlah tuples yang di -retrieved; terms lainnya digunakan utk membuang beberapa tuples yang tidak memenuhi kondisi, tetapi tidak mempengaruhi jumlah dari tuples/pages yang akan diambil (fetched). Perhartikan day<8/9/94 AND bid=5 AND sid=3. B+ tree index pada day dpt digunakan; kemudian, bid=5 and sid=3 harus dicek utk setiap tuple yang di-retrieved. Hal yang sama, hash index pada <bid, sid> dpt digunakan terlebih dahulu; kemudian day<8/9/94 harus dicek untuk setiap tuple yang di-retrieved. DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 8/23

Penggunaan Index utk Seleksi v Biaya seleksi bergantung pada #qualifying tuples, dan clustering. § § Biaya pencarian qualifying data entries (biasanya kecil) ditambah biaya utk retrieving records (bisa besar tanpa clustering). Sebagai ilustrasi perhatikan contoh query di bawah, dengan asumsi nilai attribut rname terdistribusi secara uniform, dan sekitar 10% dari tuples memenuhi kondisi seleksi (1000 pages, 100 tuples). Dengan menggunakan clustered index, biaya dapat sedikit lebih kecil dari 100 I/Os; tetapi jika unclustered, dapat mencapai 10000 I/Os ! SELECT FROM WHERE DBMS – Arif Djunaidy – FTIF ITS * Reserves R R. rname < ‘Abc%’ Bab 8 - 9/23

Proyeksi SELECT DISTINCT FROM v v R. sid, R. bid Reserves R Bagian yang mahal adalah proses utk membuang duplikasi. § Sistem SQL tidak membuang duplikai kecuali jika keyword DISTINCT dinyatakan dalam query. Sorting Approach: Sort pada <sid, bid> dan buang duplikasi. (Dapat dibuat lebih optimal dengan cara membuang informasi yang tidak diiginkan pada saat melakukan proses sorting. ) Hashing Approach: Hash pada <sid, bid> untuk membuat sejumlah partisi. Muat partisi ke dalam memory satu demi satu, kemudian bangun struktur hash dalam memory, dan lakukan eliminasi duplikasi. Jika tersedia index pada R. sid dan R. bid dalam search key, biaya dapat menjadi lebih murah dibandingkan harus melakukan sorting data entries! DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 10/23

Join: Index Nested Loops UNTU setiap tuple r R DO UNTUK setiap tuple s S dimana ri = sj DO tambahkan <r, s> ke dalam hasil v Jika tersedia index pada join column dari salah satu relasi (misalkan S), kita dapat mengeksploitasi index utk FOR-LOOP bagian dalam utk memperoleh matching disebut index nested loops join. § v Biaya: M * ( 1 + p. R * cost untuk mencari matching S tuples) , M = total biaya utk melakukan file scan dari relasi R Utk setiap R tuple, biaya utk menjajaki S index rata-rata sekitar 1. 2 utk hash index, 2 -4 utk B+ tree. Biaya utk mencari S tuples (dengan asumsi menggunakan alternatif 2 atau 3 utk data entries) akan bergantung pada clustering. § Clustered index: 1 I/O (typical), unclustered: dapat mencapai 1 I/O per matching S tuple. DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 11/23

Contoh: Index Nested Loops v. Join Hash-index (Alt. 2) pada sid of Sailors (sbg. inner): § § v Scan Reserves: 1000 pages I/Os, 100*1000 tuples. Utk setiap Reserves tuple: 1. 2 I/Os utk mengambil data entry dlm index, ditambah 1 I/O utk mengambil (tepat satu) matching Sailors tuple. Total: 220, 000 I/Os. Hash-index (Alt. 2) pada sid of Reserves (sbg. inner): § § Scan Sailors: 500 pages I/Os, 80*500 tuples. Utk setiap Sailors tuple: 1. 2 I/Os utk mendapatkan index page bersama dengan data entries, ditambah biaya utk retrieving matching Reserves tuples. Dg asumsi distribusi merata, 2. 5 reservations per sailor (100, 000 / 40, 000). Biaya utk melakukan retrieving menjadi 1 atau 2. 5 I/Os bergantung pada apakah index di-clustered atau tidak. Total 40000 * 2. 5 ( 100000 I/Os) DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 12/23

Join: Sort-Merge (Ri=j v v S) Sort R dan S pada join column, kemudian gunakan scan utk melakukan ``merge’’ (pada join column. ). § Lanjutkan scan dari R hingga current R-tuple >= current S tuple, kemudian lanjutkan scan dari S hingga current S-tuple >= current R tuple; Lakukan langkah tersebut hingga current R tuple = current S tuple. § Pada akhir langkah di atas, semua R tuples dg nilai yang sama dlm Ri (current R group) match dengan semua S tuples dg nilai yang sama dlm Sj (current S group); Return output <r, s> untuk semua pasangan dari tuples yang diperoleh. § Kemudian lakukan kembali (resume) scanning R dan S. Biaya: M Log 2 M + N Log 2 N + (M+N), dengan asumsi bahwa § M dan N adalah # pages dari masing-masing relasi § Proses sort-merge dari masing-masing relasi dilakukan dalam 2 fase (2 passes); operasi read dan write diperlukan pada setiap fase. DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 13/23

Contoh: Sort-Merge Join Sailors v Reserves Baik Reserves maupun Sailors di-sorted dlm 2 passes; dengan biaya total join sebesar 7500 I/Os: § Biaya sorting untuk relasi Reserves: 2*2*1000 = 4000 I/Os § Biaya sorting untuk relasi Sailors: 2*2*500 = 2000 I/Os § Biaya total: 4000 + 2000 + (1000 + 5000) = 7500 I/Os. DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 14/23

Optimasi Query v Rang Rencana (Space of Plans): query optimizer secara tipikal mengenali query dengan memperlakukan query sebagai ekspresi dari kombinasi operator-operator relasional - - Query Parser Parsed Query § Raltional v Query Optimizer Plan Generator Plan Cost Estimator Catalog Manager Estimated Plan Query Plan Evaluator Optimizer Komersial: § Optmizer yang digunakan oleh berbagai DBMS komersial merupakan modul yang sangat komplek, yang secara tipikal memerlukan 40 hingga 50 manyears untuk pengembangannya DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 15/23

Sepintas tentang System R Optimizer v Dampak: § v Cost estimation: Approximate art at best. § § v Paling banyak digunakan pada saat ini; dapat bekerja dengan baik untuk operasi yang melibatkan < 10 joins. Statistik dipelihara dalam system catalogs, dan digunakan untuk mengestimasi biaya operasi dan ukuran hasil operasi. Mempertimbangkan kombinasi biaya CPU dan I/O. Plan Space: Too large, must be pruned. § § § Hanya mempertimbangkan ruang left-deep plans Left-deep plans memungkinkan output setiap operator utk di-pipelined ke dalam operator berikutnya tanpa melakukan penyimpanan dalam relasi sementara. Menghindari penggunaan Cartesian products. DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 16/23

Estimasi Biaya v Utk § setiap rencana (plan), hrs mengestimasi biaya: Harus mengestimasi biaya utk setiap operasi dlm plan tree. • Bergantung pada kardinalitas tuples masukan dan luaran. • Telah dibahas sebelumnya bagaimana cara mengestimasi biaya operasi (sequential scan, index scan, joins, dll. ) § Juga harus mengestimasi ukuran dari hasil untuk setiap operasi dalam tree ! • Menggunakan informasi mengenai relasi-relasi yang digunakan. • Untuk operasi selections dan joins, terms yang dilibatkan diasumsikan independen satu sama lain. DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 17/23

Estimasi Ukuran dan Faktor Reduksi SELECT attribute list FROM relation list WHERE term 1 AND. . . AND termk v Perhatikan query block: v # tuples maksimum dlm hasil adalah perkalian dari kardinalitas relasi-relasi yang terdapat dalam FROM clause. Faktor reduksi (RF) yang di asosiasikan dg setiap term merefleksikan dampak dari term dlm mereduksi ukuran hasil. Kardinalitas hasil = # tuples max * perkalian semua RF’s. § Asumsi implisit bahwa terms independen satu sama lain! § Term col=value RF: 1/NKeys(I), utk index I pada col § Term col 1=col 2 RF: 1/MAX(NKeys(I 1), NKeys(I 2)) § Term col>value RF: (High(I)-value)/(High(I)-Low(I)) v DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 18/23

Contoh Skema Relasi Sailors (sid: integer, sname: string, rating: integer, age: real) Reserves (sid: integer, bid: integer, day: dates, rname: string) Serupa dengan skema pada bab-bab sebelumnya; kecuali ditambahkan attribut rname utk variasi. v Reserves: v § v Setiap tuple mempunyai panjang 40 bytes, 100 tuples per page, 1000 pages. Sailors: § Setiap tuple mempunyai panjang 50 bytes, 80 tuples per page, 500 pages. DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 19/23

RA Tree: Contoh Motivasi SELECT S. sname FROM Reserves R, Sailors S WHERE R. sid=S. sid AND R. bid=100 AND S. rating>5 v v sname bid=100 rating > 5 sid=sid Cost: 500*1000 I/Os = 500000 I/Os Sailors Reserves Tanpa optimasi: worst plan ! (On-the-fly) sname Plan: Kehilangan beberapa peluang: selections di-`pushed’ lebih awal, tidak memanfaatkan index, dlsb. rating > 5 (On-the-fly) bid=100 Sasaran optimasi: Mencari plans yang lebih efisien yang memberikan hasil query yang (Simple Nested Loops) sama. sid=sid Reserves DBMS – Arif Djunaidy – FTIF ITS Sailors Bab 8 - 20/23

Alternative Plans 1 (No Indexes) SELECT S. sname FROM Reserves R, Sailors S WHERE R. sid=S. sid AND R. bid=100 AND S. rating>5 v v Perbedaan utama: push selects. Biaya dari plan: § § v v (On-the-fly) sname sid=sid (Scan; write to bid=100 temp T 1) Reserves (Sort-Merge Join) rating > 5 (Scan; write to temp T 2) Sailors Scan Reserves (1000) + write temp T 1 (10 pages, jika terdapat 100 boats dan diasumsikan terdistribusi secara merata). Scan Sailors (500) + write temp T 2 (250 pages, jika terdapat 10 ratings dan diasumsikan terdistribusi secara merata). Sort T 1 (2*2*10), sort T 2 (2*4*250), merge (10+250) Total: (1000 +10) + (500+250) + (40+2000+260) = 4060 page I/Os. Jika digunakan Nested-Loops join, biaya join = 10+4*250, biaya total = 2770. Jika dilakukan `push’ projections, T 1 hanya memp sid, T 2 hanya memp sid dan sname: § T 1 dapat ditempatkan dlm 3 pages, biaya Nested-Loops join turun menjadi lebih kecil dari 250 pages, total < 2000. DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 21/23

sname Alternative Plans 2 With Indexes v v Dengan clustered index pada bid dari Reserves, diperoleh 100, 000/100 = 1000 tuples on 1000/100 = 10 pages. INL dengan pipelining (outer tidak direalisasikan). (On-the-fly) rating > 5 (On-the-fly) sid=sid (Use hash index; do not write result to temp) bid=100 (Index Nested Loops, with pipelining ) Sailors Reserves – Upaya utk melakukan “projecting out” terhadap fields yang tidak diperlukan dari bagian luar (outer) tidak membantu menurunkan biaya. v Join column sid adalah key untuk Sailors. –Paling banyak satu matching tuple, unclustered index pada sid OK. v Keputusan utk tidak mem-push rating>5 sebelum join didasarkan pada ketersediaan sid index pada Sailors. v Biaya: Selection pada Reserves tuples (10 I/Os); untuk setiap tuple, harus memperoleh matching Sailors tuple (1000*1. 2); shg total 1210 I/Os. DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 22/23

Rangkuman v v Terdapat beberapa alternatif algoritma evaluasi query untuk setiap operator relasional Sebuah query dievaluasi dengan mengkonversikannya menjadi sebuah tree of operators dan mengevaluasi operators tersebut dalam tree Harus memahami optimasi query yang digunakan dalam DBMS guna memahami secara pernuh dampak kinerja dari suatu desain database (relasi dan indexes) yang dilakukan pads suatu beban kerja dari transaksi (sekumpulan queries) Dua hal utama yang perlu diperhatikan dalam optimasi query: § Pertimbangkan satu set alternative plans. • Harus memangkas (prune) ruang pencarian; secara tipikal melalui left-deep plans only. § Harus mengestimasi biaya dari setiap “plan”. • Harus mengestimasi ukuran dari hasil dan biaya dari setiap “plan node”. • Issue kunci: Implementasi statistik, indexes, dan operator. DBMS – Arif Djunaidy – FTIF ITS Bab 8 - 23/23