Memakai Full-Text Search Di MySQL Server

Hari ini adalah hari pertama di tahun 2015. Sama seperti hari-hari sebelumnya, awan gelap dan angin kencang tidak kunjung hilang. Tidak ada yang lebih indah daripada mengawali hari pertama di tahun baru dengan segelas teh hangat sambil menulis blog. Pada artikel Memahami Eksekusi SQL di MySQL Server, saya menunjukkan bahwa query dengan pencarian floating seperti LIKE '%abc%' tidak dapat memanfaatkan index. Untuk mengatasi permasalahan tersebut, MySQL Server sejak versi 5.6.4 telah menambahkan dukungan Full-Text Index pada engine InnoDB. Full-Text Search memiliki kemampuan yang jauh lebih canggih dibandingkan dengan floating LIKE: ia akan mengurutkan record berdasarkan hasil yang paling relevan (misalnya record yang mengandung banyak jumlah kata yang dicari akan muncul di urutan awal).

Sebagai contoh, saya dapat menambahkan Full-Text Index pada kolom nama di tabel barang dengan memberikan SQL seperti berikut ini:

ALTER TABLE barang
ADD FULLTEXT INDEX idx_nama(nama ASC);

Setelah ini, saya dapat memberikan SQL yang menggunakan Full-Text Search pada kolom nama seperti:

SELECT nama FROM barang WHERE MATCH(nama) AGAINST('seal');

Query di atas akan mengembalikan seluruh nama produk yang mengandung kata 'seal'. Untuk menunjukkan bahwa index dipakai, saya dapat menggunakan EXPLAIN yang hasilnya terlihat seperti pada gambar berikut ini:

Full-Text Index Dipakai Pada Full-Text Search

Full-Text Index Dipakai Pada Full-Text Search

Agar lebih jelas, saya juga akan membandingkan perubahan kinerja yang dicapai. Saya akan mulai dengan memantau kinerja versi query yang memakai pencarian floating LIKE:

mysql> SET PROFILING=1;

mysql> SELECT SQL_NO_CACHE * FROM barang WHERE nama LIKE '%seal%';

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000108 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000715 |
| init                 | 0.000056 |
| System lock          | 0.000009 |
| optimizing           | 0.000014 |
| statistics           | 0.000037 |
| preparing            | 0.000022 |
| executing            | 0.000003 |
| Sending data         | 0.154063 |
| end                  | 0.000014 |
| query end            | 0.000010 |
| closing tables       | 0.000017 |
| freeing items        | 0.000131 |
| cleaning up          | 0.000031 |
+----------------------+----------+

mysql> SELECT * FROM barang WHERE nama LIKE '%seal%';

mysql> SHOW PROFILE FOR QUERY 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000069 |
| checking permissions | 0.000008 |
| Opening tables       | 0.000024 |
| init                 | 0.000032 |
| System lock          | 0.000010 |
| optimizing           | 0.000009 |
| statistics           | 0.000018 |
| preparing            | 0.000014 |
| executing            | 0.000004 |
| Sending data         | 0.009502 |
| end                  | 0.000013 |
| query end            | 0.000009 |
| closing tables       | 0.000014 |
| freeing items        | 0.000122 |
| cleaning up          | 0.000017 |
+----------------------+----------+

mysql> SELECT * FROM barang WHERE nama LIKE '%seal%';

mysql> SELECT * FROM barang WHERE nama LIKE '%seal%';

mysql> SELECT * FROM barang WHERE nama LIKE '%seal%';

mysql> SHOW PROFILES;
+----------+------------+------------------------------------------------------------+
| Query_ID | Duration   | Query                                                      |
+----------+------------+------------------------------------------------------------+
|        1 | 0.15523725 | SELECT SQL_NO_CACHE * FROM barang WHERE nama LIKE '%seal%' |
|        2 | 0.00986225 | SELECT SQL_NO_CACHE * FROM barang WHERE nama LIKE '%seal%' |
|        3 | 0.01032150 | SELECT SQL_NO_CACHE * FROM barang WHERE nama LIKE '%seal%' |
|        4 | 0.00977775 | SELECT SQL_NO_CACHE * FROM barang WHERE nama LIKE '%seal%' |
|        5 | 0.00983650 | SELECT SQL_NO_CACHE * FROM barang WHERE nama LIKE '%seal%' |
+----------+------------+------------------------------------------------------------+

Sekarang, saya akan membandingkannya dengan query yang memakai Full-Text Search:

mysql> SET PROFILING=1;

mysql> SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal');

mysql> SHOW PROFILE FOR QUERY 1;
+-------------------------+----------+
| Status                  | Duration |
+-------------------------+----------+
| starting                | 0.000115 |
| checking permissions    | 0.000008 |
| Opening tables          | 0.000708 |
| init                    | 0.000046 |
| System lock             | 0.000010 |
| optimizing              | 0.000013 |
| statistics              | 0.000037 |
| preparing               | 0.000013 |
| FULLTEXT initialization | 0.172336 |
| executing               | 0.000012 |
| Sending data            | 0.093124 |
| end                     | 0.000013 |
| query end               | 0.000013 |
| closing tables          | 0.000017 |
| freeing items           | 0.000150 |
| cleaning up             | 0.000022 |
+-------------------------+----------+

mysql> SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal');

mysql> SHOW PROFILE FOR QUERY 2;
+-------------------------+----------+
| Status                  | Duration |
+-------------------------+----------+
| starting                | 0.000090 |
| checking permissions    | 0.000014 |
| Opening tables          | 0.000027 |
| init                    | 0.000031 |
| System lock             | 0.000012 |
| optimizing              | 0.000009 |
| statistics              | 0.000022 |
| preparing               | 0.000010 |
| FULLTEXT initialization | 0.000320 |
| executing               | 0.000006 |
| Sending data            | 0.001038 |
| end                     | 0.000009 |
| query end               | 0.000009 |
| closing tables          | 0.000013 |
| freeing items           | 0.000141 |
| cleaning up             | 0.000018 |
+-------------------------+----------+

mysql> SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal');

mysql> SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal');

mysql> SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal');

mysql> SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                               |
+----------+------------+---------------------------------------------------------------------+
|        1 | 0.26663350 | SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal') |
|        2 | 0.00176875 | SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal') |
|        3 | 0.00201450 | SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal') |
|        4 | 0.00165800 | SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal') |
|        5 | 0.00160050 | SELECT SQL_NO_CACHE * FROM barang WHERE MATCH(nama) AGAINST('seal') |
+----------+------------+---------------------------------------------------------------------+

Pada hasil percobaan sederhana tersebut, terlihat bahwa bila saya mengabaikan eksekusi pertama yang lambat, maka untuk setiap query berikutnya, versi yang memakai Full-Text Search rata-rata lebih cepat 82% dibanding versi yang memakai float LIKE.

Selain melakukan pencarian pada modus Natural Language, Full-Text Search juga menyediakan modus Boolean. Sebagai contoh, saya bisa mencari kolom nama yang mengandung kata 'Seal' dan 'Set' tetapi tidak mengandung kata 'Shock' dengan query seperti berikut ini:

SELECT 
    nama
FROM
    barang
WHERE
    MATCH (nama) AGAINST ('+Seal +Set -Shock' IN BOOLEAN MODE);

Pada saat melakukan pencarian pada modus boolean, saya dapat memakai operator seperti + untuk menyertakan sebuah kata, - untuk memastikan sebuah kata tidak muncul, ( dan ) untuk pengelompokkan, dan sebagainya. Saya juga dapat menggunakan operator > dan < untuk memberikan prioritas kata yang dicari sehingga mempengaruhi urutan record yang dikembalikan. Sebagai contoh, query berikut ini:

SELECT 
    nama
FROM
    barang
WHERE
    MATCH (nama) AGAINST ('+Seal >Water' IN BOOLEAN MODE);

akan menghasilkan record yang mengandung kata 'Water' pada urutan yang lebih awal.

Full-Text Search juga memiliki modus Query Expansion yang akan melakukan pencarian dua kali. Pencarian kedua akan menghasilkan record yang kira-kira berhubungan dengan hasil pencarian pertama. Modus pencarian ini biasanya menghasilkan banyak record yang tidak relevan, tetapi bisa berguna untuk pencarian fuzzy dimana pengguna tidak tahu persis apa yang hendak dicari. Sebagai contoh, berikut adalah SQL yang memakai Query Expansion:

SELECT 
    nama
FROM
    barang
WHERE
    MATCH (nama) AGAINST ('seal water' WITH QUERY EXPANSION);

Bila saya mengerjakan query di atas, saya tidak hanya memperoleh nama yang mengandung 'water seal', tetapi juga yang berkaitan dengannya seperti 'water pump'. Pada modus Natural Language dan Boolean, saya hanya memperoleh 1 record yang benar-benar mengandung nama 'water seal'. Akan tetapi pada modus Query Expansion, saya bisa memperoleh hingga 92 record.

Full-Text Search akan mengabaikan stopword (kata yang dianggap tidak penting) pada kolom yang dicari. Secara default, stopword bawaan MySQL Server adalah:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a     |
| about |
| an    |
| are   |
| as    |
| at    |
| be    |
| by    |
| com   |
| de    |
| en    |
| for   |
| from  |
| how   |
| i     |
| in    |
| is    |
| it    |
| la    |
| of    |
| on    |
| or    |
| that  |
| the   |
| this  |
| to    |
| was   |
| what  |
| when  |
| where |
| who   |
| will  |
| with  |
| und   |
| the   |
| www   |
+-------+

Terlihat bahwa stopword terdiri atas kata-kata dalam bahasa Inggris. Bila ingin memakai tabel lain sebagai daftar stopword, saya dapat mengubah nilai variabel innodb_ft_server_stopword_table untuk merujuk pada tabel lain tersebut.

Iklan

Memahami eksekusi SQL di MySQL Server

Tulisan ini akan menjadi tulisan paling akhir di tahun 2014. Sudah tiba saatnya untuk mengucapkan selamat tinggal pada 2014 dan bersiap-siap menyongsong 2015. Happy new year! Sembari menunggu tahun baru tiba, saya akan memeriksa slow query log di MySQL Server Server.

MySQL Server selalu mencatat query lambat yang pernah dikerjakannya pada slow query log yang memiliki nama dengan format nama_komputer-slow.log. Sebagai contoh, bila nama komputer saya adalah PC-Snake, maka saya dapat menemukan slow query log di lokasi C:\ProgramData\MySQL\MySQL Server 5.6\data\PC-Snake-slow.log. Lalu, apa kriteria untuk sebuah query yang dianggap lambat sehingga perlu dicatat di slow query log? Hal ini tergantung pada nilai variabel long_query_time yang default-nya adalah 10 detik dan nilai min_examined_row_limit yang default-nya adalah 0. Dengan demikian, secara default, query yang eksekusinya memakan waktu lebih dari 10 detik akan dicatat di slow query log.

Sebagai contoh, saya menemukan sebuah query lambat yang sering terjadi:

SELECT DISTINCT
    produk0_.id AS id1_39_0_,
    daftarstok1_.id AS id1_52_1_,
    listperiod2_.id AS id1_33_2_,
    produk0_.createdBy AS createdB2_39_0_,
    produk0_.createdDate AS createdD3_39_0_,
    produk0_.deleted AS deleted4_39_0_,
    produk0_.hargaDalamKota AS hargaDal5_39_0_,
    produk0_.hargaLuarKota AS hargaLua6_39_0_,
    produk0_.jumlah AS jumlah7_39_0_,
    produk0_.jumlahAkanDikirim AS jumlahAk8_39_0_,
    produk0_.jumlahRetur AS jumlahRe9_39_0_,
    produk0_.jumlahTukar AS jumlahT10_39_0_,
    produk0_.keterangan AS keteran11_39_0_,
    produk0_.levelMinimum AS levelMi12_39_0_,
    produk0_.modifiedBy AS modifie13_39_0_,
    produk0_.modifiedDate AS modifie14_39_0_,
    produk0_.nama AS nama15_39_0_,
    produk0_.poin AS poin16_39_0_,
    produk0_.satuan_id AS satuan_17_39_0_,
    produk0_.supplier_id AS supplie18_39_0_,
    daftarstok1_.jumlah AS jumlah2_52_1_,
    daftarstok1_.createdBy AS createdB3_52_1_,
    daftarstok1_.createdDate AS createdD4_52_1_,
    daftarstok1_.deleted AS deleted5_52_1_,
    daftarstok1_.gudang_id AS gudang_i8_52_1_,
    daftarstok1_.modifiedBy AS modified6_52_1_,
    daftarstok1_.modifiedDate AS modified7_52_1_,
    daftarstok1_.produk_id AS produk_i9_52_1_,
    daftarstok1_.produk_id AS produk_i9_39_0__,
    daftarstok1_.id AS id1_52_0__,
    daftarstok1_.gudang_id AS formula0_0__,
    listperiod2_.arsip AS arsip2_33_2_,
    listperiod2_.jumlah AS jumlah3_33_2_,
    listperiod2_.saldo AS saldo4_33_2_,
    listperiod2_.tanggalMulai AS tanggalM5_33_2_,
    listperiod2_.tanggalSelesai AS tanggalS6_33_2_,
    listperiod2_.createdBy AS createdB7_33_2_,
    listperiod2_.createdDate AS createdD8_33_2_,
    listperiod2_.deleted AS deleted9_33_2_,
    listperiod2_.modifiedBy AS modifie10_33_2_,
    listperiod2_.modifiedDate AS modifie11_33_2_,
    listperiod2_.riwayat_id AS riwayat12_52_1__,
    listperiod2_.id AS id1_33_1__,
    listperiod2_.listPeriodeRiwayat_ORDER AS listPer13_1__
FROM
    Produk produk0_
        LEFT OUTER JOIN
    StokProduk daftarstok1_ ON produk0_.id = daftarstok1_.produk_id
        LEFT OUTER JOIN
    PeriodeItemStok listperiod2_ ON daftarstok1_.id = listperiod2_.riwayat_id
WHERE
    1 = 1
ORDER BY produk0_.nama ASC;

Terlihat kompleks? Tenang saja, saya tidak menulis SQL tersebut secara manual melainkan memakai Hibernate JPA untuk menghasilkan query secara otomatis. Pada domain class, saya memiliki hierarki composition (whole-part relationship) berupa Produk memiliki StokProduk yang selanjutnya memiliki PeriodeItemStok. Karena MySQL Server adalah database relasional yang tidak mendukung composition, maka ia akan diterjemahkan menjadi LEFT JOIN. Ini adalah apa yang disebut sebagai memakai OOP sebagai paradigma tetapi menerapkan murni dalam bentuk relasional (Taniar, Pardede & Rahayu (2005), Composition in Object-Relational Database, http://www.irma-international.org/viewtitle/14285/).

Untuk mendapatkan bayangan bagaimana MySQL Server akan mengerjakan query diatas, saya dapat menggunakan EXPLAIN untuk melihat bagaimana ‘pola pikir’ MySQL dalam menjalankan query. Untuk itu, saya perlu menambahkan EXPLAIN sebelum SELECT seperti pada query berikut ini:

mysql> EXPLAIN SELECT ... /G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: produk0_
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 443
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: daftarstok1_
         type: ref
possible_keys: FK_qdv4fq1uprbpkool88p9y6h44
          key: FK_qdv4fq1uprbpkool88p9y6h44
      key_len: 8
          ref: inventory.produk0_.id
         rows: 1
        Extra: NULL
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: listperiod2_
         type: ref
possible_keys: FK_ls70le3nhwwxfw0gudwcpv4l3
          key: FK_ls70le3nhwwxfw0gudwcpv4l3
      key_len: 9
          ref: inventory.daftarstok1_.id
         rows: 1
        Extra: NULL
3 rows in set (0.00 sec)

Bagi yang tidak suka membaca informasi dalam bentuk tabel, MySQL Workbench dapat menyajikan hasil EXPLAIN dalam bentuk visual (secara default) seperti pada gambar berikut ini:

Hasil EXPLAIN dalam bentuk visual

Hasil EXPLAIN dalam bentuk visual

Pada tampilan visual di atas, saya perlu menghindari kotak merah yang disebut Full Table Scan. Ini adalah operasi yang paling berat karena harus mencari isi tabel satu per satu tanpa memakai index sama sekali.

Bila saya membiarkan pointer mouse agak lama di kotak tersebut, saya akan memperoleh informasi lebih lanjut seperti pada gambar berikut ini:

Tooltip yang berisi informasi

Tooltip yang berisi informasi

Full table scan akan semakin sia-sia bila saya melakukan pencarian pada tabel yang memiliki banyak record tetapi tidak membutuhkan seluruh baris yang ada. Pada contoh yang saya peroleh, seluruh 443 record yang dicari di tabel produk akan dikembalikan sehingga kerja keras full table scan tidak akan sia-sia.

Operasi LEFT JOIN sudah memakai index. Hal ini terlihat pada dua kotak hijau bertuliskan Non-Unique Key Lookup. Ini adalah foreign key index yang dihasilkan secara otomatis oleh Hibernate (melalui klausa FOREIGN KEY pada saat CREATE TABLE).

Berikutnya, saya menemukan bahwa SELECT DISTINCT pada dasarnya adalah sebuah operasi yang tidak jauh berbeda dengan GROUP BY. Karena SQL ini melibatkan operasi LEFT JOIN, maka MySQL Server tidak bisa begitu saja memakai index. Untuk itu, ia terpaksa harus membuat internal temporary table di memori. Hal ini terlihat dari tulisan tmp_table di bawah kotak DISTINCT.

Internal temporary table adalah tabel di memori yang berisi hasil proses sementara sebelum hasil akhir diperoleh. Saya bisa melihat berapa banyak jumlah internal temporary table yang sudah dibuat oleh MySQL Server (sejak ia dinyalakan) dengan memberikan perintah SQL berikut ini:

SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

Bila seandainya ukuran internal temporary table mencapai batas tertentu, maka ia akan disimpan ke dalam harddisk dalam bentuk tabel MyISAM. Tentu saja akibatnya adalah query akan menjadi lebih lambat. Untuk melihat jumlah temporay table di memori yang akhirnya disimpan ke harddisk, saya dapat memberikan perintah SQL berikut ini:

SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

Kapan MySQL Server menyimpan temporary table ke disk? Hal ini tergantung pada nilai tmp_table_size dan max_heap_table_size. Pada sistem dengan jumlah memori yang berlimpah, meningkatkan kedua variabel tersebut akan mencegah MySQL Server untuk menyimpan temporary table di disk sehingga bisa meningkatkan kinerja bila terdapat banyak query yang mengandung DISTINCT dan GROUP BY.

Pada bagian ORDER, saya menemukan tulisan filesort. Ini adalah algoritma yang akan dipakai oleh MySQL Server bila pengurutan tidak dapat dilakukan melalui index. Seperti yang bisa ditebak, filesort akan lebih lambat dibandingkan dengan pengurutan yang dilakukan dengan menggunakan index.

Lalu apa yang harus saya lakukan untuk mengoptimalkan query ini? Cara yang paling realistis adalah dengan melakukan perubahan pada sisi aplikasi, bukan pada sisi database. Apakah pengguna perlu melihat seluruh produk yang ada setiap kali menampilkan screen produk? Pada banyak kasus, jawabannya adalah ‘tidak’.

Seandainya saya menambahkan kriteria pencarian berdasarkan nama pada query di atas, maka saya akan memperoleh hasil seperti berikut ini:

EXPLAIN SELECT ... 
WHERE produk0_.nama = 'namaprodukdicari' 
ORDER BY produk0_.nama ASC;
Hasil EXPLAIN setelah menambahkan kondisi WHERE

Hasil EXPLAIN setelah menambahkan kondisi WHERE

Terlihat bahwa full table scan masih dilakukan! Hanya saja kini tidak dibutuhkan lagi filesort pada ORDER. Mengapa demikian? Hal ini terjadi karena saya tidak memiliki index untuk kolom nama di tabel produk. Memberikan index pada kolom nama untuk produk adalah sesuatu yang lumrah karena biasanya tabel produk lebih sering dibaca daripada ditulis. Oleh sebab itu, saya segera menambahkan index dengan memberikan perintah berikut ini:

ALTER TABLE produk ADD INDEX idx_nama(nama ASC);

Sekarang, hasil visualisasi EXPLAIN untuk query sebelumnya akan terlihat seperti:

Hasil visualisasi EXPLAIN setelah penambahan index pada kolom nama

Hasil visualisasi EXPLAIN setelah penambahan index pada kolom nama

Ini adalah hasil yang paling optimal karena semuanya berwarna ‘hijau’ 🙂

Walaupun sempurna dari sisi kinerja, mucul permasalahan baru yang berkaitan dengan produktifitas. Kondisi pencarian yang saya gunakan pada WHERE adalah pencarian sama dengan seperti WHERE produk0_.nama = 'namaprodukdicari'. Ini berarti pengguna harus memasukkan nama produk secara lengkap dan sama persis seperti yang tersimpan di database! Program yang lebih user-friendly seharusnya memungkinkan pencarian berdasarkan bagian dari nama. Oleh sebab itu, saya perlu mengubah kriteria pada query menjadi WHERE produk0_.nama LIKE '%bagian_nama_produk%' seperti:

EXPLAIN SELECT ... 
WHERE produk0_.nama LIKE '%bagian_nama_produk%' 
ORDER BY produk0_.nama ASC;

Hasil EXPLAIN kini tiba-tiba menjadi tidak se-‘hijau’ sebelumnya! Full table scan kembali terjadi lagi. Mengapa demikian? Kriteria seperti LIKE '%abc%' tidak dapat memanfaatkan index biasa dengan baik karena abc bisa ada dimana saja di nama produk.

Sebagai gantinya, saya bisa melakukan pengorbanan dengan menghilangkan tanda persen (%) di awal. Dengan demikian, query akan mencari nama produk yang diawali dengan apa yang diketik oleh pengguna:

EXPLAIN SELECT ... 
WHERE produk0_.nama LIKE 'namadepan_produk%' 
ORDER BY produk0_.nama ASC;

Hasil visualisasi akan terlihat seperti pada gambar berikut ini:

Index yang membantu kondisi LIKE

Index yang membantu kondisi LIKE

Index Range Scan menunjukkan bahwa index dipakai untuk membantu mencari nama produk yang diawali oleh kriteria pencarian. Ini jauh lebih baik daripada full table scan. Bila seandainya saya tetap ingin mengoptimalkan kriteria pencarian seperti nama LIKE '%abc%', maka saya perlu menggunakan index khusus yang disebut sebagai Full-Text Index. Saya juga perlu mengganti kondisi LIKE menjadi seperti MATCH(nama) AGAINST('abc'). Fasilitas yang disebut sebagai Full-Text Searching (FTS) ini tersedia di InnoDB sejak versi 5.6.4.

Memakai Materialized View Di Oracle TimesTen

Satu kekurangan dari MySQL yang terasa saat saya beralih ke database tersebut adalah tidak adanya fasilitas materialized view.   Fasilitas materialized view pertama kali diperkenalkan oleh Oracle Database, baru kemudian diikuti oleh database lainnya.   Hingga saat ini, Oracle MySQL masih belum mendukung materialized view (beberapa berusaha mensimulasikannya melalui trigger!).

Lalu, apa itu materialized view?   Apa bedanya dengan view biasa?

Untuk menunjukkan perbedaannya, saya akan membuat tabel seperti berikut ini:

Command> CREATE TABLE produk (
       >   kode CHAR(3) PRIMARY KEY,
       >   nama VARCHAR2(50) NOT NULL
       > );

Command> CREATE TABLE stok (
       >   tanggal DATE NOT NULL DEFAULT SYSDATE PRIMARY KEY,
       >   nomorFaktur CHAR(3) NOT NULL,
       >   kodeProduk CHAR(3) NOT NULL,
       >   jumlah NUMBER NOT NULL,
       >   FOREIGN KEY (kodeProduk) REFERENCES produk(kode)
       > );

Command> INSERT INTO produk VALUES ('AAA', 'Produk A');
1 row inserted.

Command> INSERT INTO produk VALUES ('BBB', 'Produk B');
1 row inserted.

Command> INSERT INTO produk VALUES ('CCC', 'Produk C');
1 row inserted.

Command> INSERT INTO produk VALUES ('DDD', 'Produk D');
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '001', 'AAA', 10);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '001', 'BBB', 20);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '001', 'CCC', 30);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '002', 'AAA', -5);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '003', 'BBB', -10);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '003', 'CCC', -20);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '004', 'AAA', 5);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '004', 'BBB', 4);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '004', 'CCC', 3);
1 row inserted.

Pada contoh tabel di atas, saya memiliki daftar perubahan produk (misalnya, bertambah akibat pembelian dan berkurang akibat penjualan) pada tabel stok.   Biasanya, saya sering kali menampilkan nama produk beserta jumlah yang tersedia berdasarkan perubahan stok tersebut.   Untuk itu, saya dapat menggunakan query SQL berikut ini:

Command> SELECT p.kode, p.nama, COALESCE(SUM(s.jumlah),0) FROM 
       > produk p LEFT JOIN stok s ON p.kode = s.kodeProduk GROUP BY p.kode, p.nama;
< AAA, Produk A, 10 >
< BBB, Produk B, 14 >
< CCC, Produk C, 13 >
< DDD, Produk D, 0 >
4 rows found.

Banyak database sudah mendukung apa yang disebut sebagai view (atau disebut juga non-materialized view).   Biasanya view dipakai untuk menyederhanakan SQL yang menggabungkan beberapa tabel sehingga dapat diakses melalui view tersebut.   Sebagai contoh, untuk membuat sebuah view berdasarkan query di atas, maka saya dapat memberikan perintah seperti berikut ini:

Command> CREATE VIEW persediaan AS
       > SELECT p.kode, p.nama, COALESCE(SUM(s.jumlah),0) jumlah FROM
       > produk p LEFT JOIN stok s ON p.kode = s.kodeProduk
       > GROUP BY p.kode, p.nama;

Command> SELECT * FROM persediaan;
< AAA, Produk A, 10 >
< BBB, Produk B, 14 >
< CCC, Produk C, 13 >
< DDD, Produk D, 0 >
4 rows found.

Pada contoh di atas, saya sudah membuat sebuah view baru yang diberi nama persediaan.   Sifat view adalah non-materialized sehingga tidak ada data yang ‘disimpan’ pada view tersebut.   Setiap kali query dilakukan pada view persediaan, maka SQL asalnya yang melakukan agregasi SUM akan selalu dikerjakan kembali!   Sebagai buktinya, lihat hasil explain yang men-select dari view persediaan:

Command> EXPLAIN SELECT * FROM persediaan;

Query Optimizer Plan:

  STEP:                1
  LEVEL:               3
  OPERATION:           TblLkRangeScan
  TBLNAME:             SOLID.PRODUK
  IXNAME:              PRODUK
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

  STEP:                2
  LEVEL:               3
  OPERATION:           TblLkRangeScan
  TBLNAME:             SOLID.STOK
  IXNAME:              TTFOREIGN_2
  INDEXED CONDITION:   S.KODEPRODUK >= P.KODE
  NOT INDEXED:         <NULL>

  STEP:                3
  LEVEL:               2
  OPERATION:           MergeJoin(Left OuterJoin)
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   P.KODE = S.KODEPRODUK
  NOT INDEXED:         <NULL>

  STEP:                4
  LEVEL:               1
  OPERATION:           GroupBy
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

Berbeda dengan view, sebuah materialized view akan ‘menyimpan’ nilai dari tabel sumber (disebut jadi detail table).   Sebagai contoh, kode SQL berikut ini akan membuat sebuah synchronous materialized view:

Command> CREATE MATERIALIZED VIEW sync_persediaan AS
       > SELECT kodeProduk, SUM(jumlah) jumlah, COUNT(*) entri
       > FROM stok
       > GROUP BY kodeProduk;
3 rows materialized.

Command> SELECT * FROM sync_persediaan;
< AAA, 10, 3 >
< BBB, 14, 3 >
< CCC, 13, 3 >
3 rows found.

Karena beberapa keterbatasan, saya tidak menghasilkan materialized view yang sama seperti non-materialized view di atas.   Setiap kali saya men-select dari sync_persediaan, saya akan memperoleh hasil yang sama tanpa adanya query ulang dari tabel sumber.   Dengan demikian, proses membaca dari materialized view bukanlah sesuatu yang berat, seperti yang ditunjukkan oleh hasil explain berikut ini:

Command> EXPLAIN SELECT * FROM sync_persediaan;

Query Optimizer Plan:

  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkSerialScan
  TBLNAME:             SYNC_PERSEDIAAN
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

Lalu, kapan sebuah materialized view diperbaharui?   Setiap kali saya melakukan perubahan pada tabel sumber (disebut juga detail table). Sebagai contoh, bila saya menambahkan sebuah item baru untuk stok, maka sync_persediaan akan diperbaharui, seperti yang ditunjukkan pada hasil explain berikut ini:

Command> EXPLAIN INSERT INTO stok VALUES (SYSDATE, '005', 'DDD', 10);

Query Optimizer Plan:

  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkInsert
  TBLNAME:             STOK
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

  STEP:                2
  LEVEL:               2
  OPERATION:           RowLkSerialScan
  TBLNAME:             SOLID.SYNC_PERSEDIAAN
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         SOLID.SYNC_PERSEDIAAN.KODEPRODUK = 'DDD'

  STEP:                3
  LEVEL:               1
  OPERATION:           OneGroupGroupBy
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

  STEP:                4
  LEVEL:               1
  OPERATION:           RowLkUpdView
  TBLNAME:             SOLID.SYNC_PERSEDIAAN
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

Kesimpulannya: Gunakan materialized view bila query untuk pada materialized view tersebut sering dilakukan.   Bila perubahan pada tabel sumber (detail table) terlalu sering, lebih baik menggunakan view (disebut juga non-materialized view).

Untuk mengatasi masalah kinerja yang timbul akibat perubahan tabel sumber (materialized table),  TimesTen memungkinkan apa yang disebut sebagai asynchronous materialized view.   Sebagai contoh, saya membuat sebuah asynchronous materialized view dengan memberikan perintah berikut ini:

Membuat asynchronous materialized view

Membuat asynchronous materialized view

Materialized view bernama async_persediaan di atas akan diperbaharui setiap 10 menit.   Bila tidak ada perubahan tabel sumber pada saat materialized view diperbaharui, maka tidak akan ada yang dikerjakan.   Dengan demikian, perubahan pada tabel sumber tidak akan langsung mempengaruhi asynchronous materialized view.

Pada contoh ini, bila pengguna melakukan perubahan jumlah stok (akibat menambah atau menghapus faktur), maka isi dari async_persediaan tidak akan berubah selama interval 10 menit belum tercapai.   Hal ini biasanya tidak menjadi masalah, kecuali bila pengguna tiba-tiba membuka halaman untuk melihat persediaan (atau mencetak laporan)!   Untuk mengatasi hal tersebut, saya dapat memaksa agar asynchronous materialized view segera diperbaharui saat halaman persediaan akan ditampilkan, dengan menggunakan perintah SQL REFRESH MATERIALIZED VIEW seperti berikut ini:

Command> SELECT * FROM async_persediaan;
< AAA, 30, 4 >
< BBB, 14, 3 >
< CCC, 13, 3 >
3 rows found.

Command> REFRESH MATERIALIZED VIEW async_persediaan;

Command> SELECT * FROM async_persediaan;
< AAA, 50, 5 >
< BBB, 14, 3 >
< CCC, 13, 3 >
3 rows found.

Menampilkan Log SQL Yang Dikerjakan MySQL

Kode program yang memakai ORM (object relational mapper) seperti JPA umumnya tidak memberikan query SQL secara langsung karena query akan dihasilkan oleh sang mapper.   Tapi terkadang developer perlu mengetahui apa saja query SQL yang dihasilkan oleh ORM, misalnya untuk keperluan troubleshooting atau mencari bottleneck kinerja.   Bila memakai database MySQL, si developer memiliki beberapa pilihan untuk melihat SQL yang dibuat oleh aplikasi tanpa perlu memakai tool tambahan.

JDBC bawaan untuk MySQL sudah menyediakan fasilitas logging.   Hasil log-nya lebih detail karena dilengkapi dengan nilai parameter dibandingkan dengan yang terlihat di log Hibernate yang hanya menuliskan isi prepared statement dimana nilai masih berupa tanda tanya (?).  Untuk mengaktifkan logging di JDBC, saya perlu menambahkan sesuatu di JDBC URL, yaitu nilai logger dan profileSQL.  Sebagai contoh, bila saya memakai JPA, maka saya mengubah isi berikut ini di persistence.xml:

...
<properties>
   ...
   <property name="javax.persistence.jdbc.url"
      value="jdbc:mysql://localhost/namadb?logger=com.mysql.jdbc.log.Slf4JLogger&amp;profileSQL=true" />
   ...
</properties>
...

Nilai profileSQL menunjukkan agar driver JDBC tersebut akan mencatat setiap SQL yang ada.   Sementara itu, nilai logger menunjukkan tujuan yang menyimpan catatan SQL nantinya.    Saya memilih menggunakan SlfjLogger yang akan mencatat sesuai dengan konfigurasi Log4j saya.  Saya memisahkan kedua nilai tersebut dengan &amp; karena ini adalah XML;  bila tidak memakai JPA, misalnya saya membuat koneksi di kode program, maka saya boleh langsung memakai tanda ampersand (&).

Langkah berikutnya adalah melakukan konfigurasi Log4J.  Bila memakai Griffon, maka perubahan ini dapat dilakukan di file Config.groovy.  Sebagai contoh, saya akan mencatat log SQL yang dikerjakan aplikasi ke sebuah file:

log4j = {
  ...
  appenders {
     rollingFile name: 'sqlLog', file: "${System.getProperty('user.home')/sql.log",
        layout: pattern(conversionPattern: '%d [%t] %-5p %c - %m%n'), maxFileSize: 10485760)
  }
  ...
  debug additivity: false, sqlLog: 'com.mysql.jdbc.log'
  ...

Setelah ini, bila aplikasi dijalankan, saya dapat menemukan seluruh SQL yang dikerjakan di file sql.log yang terletak di folder home (seperti C:\Users\nama\).

Alternatif lainnya, saya dapat melakukan pencatatan SQL ini disisi server MySQL itu sendiri.  Hal ini berguna bila database diakses oleh banyak user secara bersamaan, dan saya ingin melihat seluruh SQL yang dikerjakan oleh user-user yang terkoneksi.  Perlu diperhatikan bahwa pencatatan melalui general query log ini tidak harus berurut sesuai dengan eksekusinya, melainkan berdasarkan urutan saat diterima dari client.

Untuk melakukan pencatatan di sisi server, saya harus melakukan konfigurasi MySQL, misalnya dengan mengubah file my.ini yang ada di folder instalasi MySQL (seperti C:\Program Files\MySQL\MySQL Server 5.6\my.ini).  Saya harus mengubah file tersebut sehingga terdapat baris berikut ini:

log-output=FILE
general-log=1
general-log-file="sql.log"

Setelah me-restart server MySQL, maka seluruh SQL yang dikerjakan oleh server tersebut akan tercatat di file sql.log.  File ini terletak di lokasi yang sesuai dengan nilai datadir (nilainya tertera di file konfigurasi yang sama, atau gunakan perintah SHOW VARIABLES LIKE 'datadir'; untuk melihat nilainya)Secara default, lokasi ini adalah di C:\ProgramData\MySQL\MySQL Server 5.6\data.

Berikutnya, jalankan aplikasi, tunggu hingga log terkumpul (gunakan tail -f untuk memantau bila di Linux), lalu cari query yang menjadi biang kesalahannya 🙂

Oracle SQL: Memilih Record Secara Acak Dari Tabel

Ada saatnya kita diharuskan untuk memilih satu baris dari sekian banyak baris yang ada di tabel. Untuk itu, kita dapat menggunakan SQL seperti berikut di database Oracle:

SELECT nama_kolom FROM
( SELECT nama_kolom FROM nama_tabel
ORDER BY DBMS_RANDOM.VALUE()) WHERE ROWNUM = 1;

Jika menginginkan lebih dari 1 baris yang acak, kita dapat mengganti ROWNUM = 1, menjadi ROWNUM <= n, dimana n adalah jumlah baris record yang kita inginkan.

Bagaimana Cara Kerja Query Di Atas?

Pada umumnya kita menggunakan nama field pada bagian ORDER BY untuk menentukan pengurutan berdasarkan field apa. Selain itu, kita juga bisa menggunakan angka (bilangan bulat) untuk menunjukkan posisi kolom yang akan dijadikan basis pengurutan.

Sebagai contoh, SQL ini akan mengurutkan berdasarkan kolom kedua:

SELECT kolom1, kolom2 FROM nama_tabel ORDER BY 2;

Lalu, bagaimana dengan ORDER BY DBMS_RANDOM.VALUE()? Fungsi tersebut akan mengembalikan angka pecahan di antara angka 0 dan 1. Tentu saja ini tidak berarti akan mengurutkan di-“antara” kolom pertama?

Selain mengharapkan nama kolom dan angka bilangan bulat, klausa ORDER BY juga menerima ekspresi, seperti hasil kembalian sebuah function. Pengurutan akan dilakukan berdasarkan nilai ekspresi ini untuk setiap baris yang ada. Oleh sebab itu, query pengurutan yang kita berikan pada dasarnya adalah penyederhanaan dari query berikut ini:

SELECT nama_kolom FROM
( SELECT DBMS_RANDOM.VALUE() AS RANDOM, nama_kolom FROM nama_tabel
ORDER BY RANDOM) WHERE ROWNUM = 1;

Day 36: Hierarchical Queries

Learning In HomeOriginal Date: 18 Februari 2009

Sebagai latihan terakhir di bulan ini yang berkaitan dengan database Oracle, aku akan mencoba memakai hierarchical queries. Sebagai contoh, aku memiliki tabel tbl_kategori dengan struktur sebagai berikut:

CREATE TABLE TBL_KATEGORI ("ID" NUMBER(5,0), "NAMA" VARCHAR2(100 BYTE), "SUPER_KATEGORI" NUMBER(5,0));

Pada tabel ini, setiap kategori dapat memiliki sub-kategori dengan tingkatan yang tidak dibatasi. Untuk menghasilkan query yang secara otomatis mengurutkan berdasarkan hierarki, aku menggunakan perintah:


SELECT * FROM tbl_kategori START WITH super_kategori IS NULL CONNECT BY PRIOR id = super_kategori;

Pada bagian START WITH, aku memberikan kondisi super_kategori IS NULL, yang menunjukkan bahwa yang menjadi root (kategori paling atas) adalah kategori dengan nilai field super_kategori berupa NULL.

Pada bagian CONNECT BY, aku memberikan kondisi PRIOR id = super_kategori yang menunjukkan bagaimana setiap kategori saling berhubungan membentuk hierarki.

Salah satu pseudocolumn yang tersedia saat memakai hierarcial query adalah LEVEL. Sebagai contoh, aku bisa hanya men-select kategori yang berada pada level 2 saja (tepat dibawah root):


SELECT
*
FROM
tbl_kategori
WHERE
LEVEL = 2
START WITH
super_kategori IS NULL
CONNECT BY
PRIOR id = super_kategori;

Pseudocolumn lain adalah CONNECT_BY_ISLEAF yang akan bernilai 1 jika baris tersebut tidak memiliki hierarki di bawahnya lagi. Sebagai contoh, aku dapat men-select semua kategori yang tidak memiliki sub-kategori lagi, dengan perintah:


SELECT
*
FROM
tbl_kategori
WHERE
connect_by_isleaf = 1
START WITH
super_kategori IS NULL
CONNECT BY
PRIOR id = super_kategori;

Day 33: SQL Untuk Modeling

Learning In HomeOriginal Date: 14 Februari 2009

Hari Valentine akhirnya tiba juga. Nuansa cinta terlihat dimana-mana, gadis manis tetangga yang sering menatapku (?) juga terlihat tambah menarik. Komunikasi tanpa kata-kata (dan tanpa protocol tentunya) diantara kami memang sulit dipahami, tapi itu bukanlah topik tulisan hari ini. Pagi ini, aku akan kembali mempelajari menggunakan SQL. Kali ini dalam kaitannya dengan MODEL yang memungkinkan manipulasi isi tabel sampai ke tahap cell. Sebagai contoh, misalnya, aku membuat sebuah tabel sederhana yang mirip lembaran kerja di Excel:


CREATE TABLE tbl_latihan_model (
  A VARCHAR2(10),
  B VARCHAR2(10),
  C VARCHAR2(10),
  D NUMBER(10)
);

INSERT INTO tbl_latihan_model 
  VALUES ('A1', 'B1', 'C1', 10);
INSERT INTO tbl_latihan_model 
  VALUES ('A2', 'B2', 'C2', 20);
INSERT INTO tbl_latihan_model 
  VALUES ('A3', 'B3', 'C3', 30);
INSERT INTO tbl_latihan_model 
  VALUES ('A4', 'B4', 'C4', 40);
INSERT INTO tbl_latihan_model 
  VALUES ('A5', 'B5', 'C5', 50);
INSERT INTO tbl_latihan_model 
  VALUES ('A6', 'B6', 'C6', 60);

Lalu, aku ingin mengubah nilai kolom D sesuai keinginan dengan MODEL:


SELECT
*
FROM
tbl_latihan_model
MODEL
DIMENSION BY (A, B, C)
MEASURES (D)
RULES UPDATE (
D[A='A1', B='B1', C='C1'] = 999,
D[A='A4', B='B4', C='C4'] = D[A='A1', B='B1', C='C1'] + 1
)
ORDER BY A
;

Hasil akhirnya akan mirip seperti ini:


-------------------
 A   B   C   D
-------------------
 A1  B1  C1   999
 A2  B2  C2    20
 A3  B3  C3    30
 A4  B4  C4  1000
 A5  B5  C5    50
 A6  B6  C6    60
-------------------

Aku telah mengubah nilai kolom D di baris 1 menjadi 999. Bukan hanya itu, aku juga mengubah nilai kolom D di baris 4 menjadi nilai kolom D di baris pertama ditambah dengan 1. Semuanya itu dalam sekali query. Dan bahkan bisa lebih lagi, dengan menambah statement baru di bagian klausa RULES.

Contoh yang lebih berguna, misalnya, aku akan menghitung dan menampilkan diskon untuk produk berdasarkan rule yang telah aku tentukan:


SELECT 
  tanggal,
  kategori_id,
  sum_jumlah,
  diskon
FROM 
  (SELECT
      tanggal, 
      brg.id AS brg_id, 
      SUM(pb.jumlah) AS sum_jumlah
   FROM
      tbl_pembelian pb, tbl_barang brg
   WHERE
      pb.barang_id = brg.id
   GROUP BY
      tanggal, brg.id
  ) pb,
  tbl_kategori kat, 
  tbl_barang brg
WHERE
  pb.brg_id = brg.id AND
  brg.kategori = kat.id 
MODEL  
  PARTITION BY (tanggal)
  DIMENSION BY (
    brg_id, 
    kat.id as kategori_id, 
    sum_jumlah)
  MEASURES (0 as diskon)
  RULES UPDATE (
     diskon[any, 
            any, 
            sum_jumlah > 10] = 0.01,
     diskon[any, 
            kategori_id = 3, 
            any] = 
      diskon[cv(brg_id), 
             cv(kategori_id), 
             cv(sum_jumlah)] +
        cv(sum_jumlah) * 0.1
  )
ORDER BY
  tanggal
;  

Pada contoh tersebut, aku memberikan dua rule, yaitu diskon sebesar 0.01 untuk jika atribut sum_jumlah lebih dari 10. Selain itu, jika kategori_id adalah 3, maka aku akan menambahkan diskon sebesar 0.01 dari sum_jumlah saat ini. Aku menggunakan fungsi cv() untuk mendapatkan nilai saat cell saat ini (cell yang sedang diproses). Contoh hasil query-nya, misalnya:


---------------------------------
 TANGGAL KATEGORI SUM     DISKON
         ID       JUMLAH 
---------------------------------
 28       5         2        0
 28       3        13     1,31
 30      10        15     0,01
 30       4         4        0
 31       3         2      0,2
---------------------------------

Sekarang sudah saatnya untuk beristirahat sejenak, sebab tadi aku melihat the girl next door sudah pulang. Rambutnya yang panjang dibiarkan terurai rapi.. Ini pasti pertanda yang baik di hari Valentine. Aku jadi semakin betah disini.

Day 32: SQL Untuk Analisa Data

Learning In HomeOriginal Date: 13 Februari 2009

Sebagai lanjutan dari pelajaran kemarin, hari ini aku akan mempelajari beberapa perluasan SQL milik Oracle Database yang berfungsi untuk analisa data. Aku akan mulai dengan RANK() dan DENSE_RANK(). Kedua fungsi tersebut akan mengembalikan nilai peringkat mulai dari 1, 2, dan seterusnya. Perbedaannya terletak pada penanganan dua atau lebih record yang memiliki peringkat yang sama. Misalnya, ada 3 record yang memiliki peringkat yang sama, yaitu peringkat 1. Jika menggunakan RANK(), record berikut-nya yang bernilai berbeda akan memiliki peringkat 4. Jika menggunakan DENSE_RANK(), record berikut-nya yang bernilai berbeda akan memiliki peringkat 2. Contoh penggunakan DENSE_RANK(), misalnya, aku ingin mengetahui produk apa yang nilai penjualannya terbesar dan memberikan peringkat terhadap masing-masing produk:


SELECT
s.prod_id, p.prod_name,
SUM(s.amount_sold),
DENSE_RANK() OVER (ORDER BY SUM(s.amount_sold) DESC)
FROM
sales s, products p
WHERE
s.prod_id = p.prod_id
GROUP BY
s.prod_id, p.prod_name;

Fungsi CUME_DIST() dapat dipakai untuk memberikan nilai yang relatif terhadap kumpulan nilai (bisa berupa seluruh kolom, atau berdasarkan partisi yang ditentukan). Nilai yang dikembalikan antara nilai lebih besar dari 0 (nol) hingga mencapai 1 (satu). Aku dapat menggunakan fungsi ini untuk mendapatkan nilai distribusi kumulatif, seperti pada contoh:


SELECT
c.cust_state_province,
SUM(s.quantity_sold),
CUME_DIST() OVER (ORDER BY SUM(s.quantity_sold)) as "Distribusi"
FROM
sales s, customers c
WHERE
s.cust_id = c.cust_id
GROUP BY
c.cust_state_province;

Fungsi NTILE() dapat dipakai untuk membagi tabel ke dalam sejumlah bagian yang ditentukan dan menghasilkan nilai mulai dari 1 hingga N (jumlah yang ditentukan) untuk masing-masing baris. Misalnya, aku ingin membagi tbl_pembelian ke dalam 5 kelompok, aku dapat menggunakan query berikut:


SELECT TANGGAL, JUMLAH, NTILE(5) OVER (ORDER BY TANGGAL ASC) FROM tbl_pembelian;

Dan database Oracle akan mengembalikan nilai seperti berikut untuk tabel dengan 15 baris:


---------------------------------
  TANGGAL  JUMLAH     NTILE(5)
---------------------------------
    x         x        1
    x         x        1
    ...       ...      1
                       2
                       2
                       2
                       3 
                       3
                       3
                       4
                       4
                       4
   ...        ...      5
   x          x        5
---------------------------------

Aku dapat menggunakan windowing function untuk menghitung nilai aggregate secara kumulatif. Misalnya, aku ingin menghitung nilai SUM untuk penjualan secara kumulatif mulai dari bulan Januari hingga bulan Desember dengan menggunakan SQL berikut:


SELECT
t.calendar_month_number,
SUM(s.amount_sold),
SUM(SUM(s.amount_sold)) OVER
(ORDER BY t.calendar_month_number ROWS UNBOUNDED PRECEDING) AS "Total Kumulatif"
FROM
sales s, times t
WHERE
s.time_id = t.time_id
GROUP BY
t.calendar_month_number;

Beberapa variasi untuk windowing function, misalnya moving berdasarkan selang 2 record sebelumnya:


AVG(x) OVER (ORDER BY x ROWS 2 PRECEDING)

atau centered pada nilai saat ini (row aktif), misalnya menghitung jumlah dari 2 baris sebelum hingga 2 baris sesudah untuk masing-masing baris:


SUM(x) OVER (ORDER BY x RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING)

Query SQL analisa lain yang cukup berguna adalah perluasan terhadap fungsi aggregate seperti SUM, AVG, COUNT(*), dsb sehingga mendukung OVER (PARTITION BY). Sebagai contoh, aku memiliki tabel TBL_PEMBELIAN, dan TBL_BARANG. Aku ingin mendapatkan laporan total pembelian per tanggal, dimana total tersebut didapatkan dari SUM field jumlah yang ada di TBL_PEMBELIAN dikali dengan field harga yang ada di TBL_BARANG. Perluasan OVER (PARTITION BY) untuk fungsi agregate SUM() mempermudah aku dalam membuat query-nya:


SELECT DISTINCT "Tanggal", "Total Pembelian" FROM
(SELECT
pb.tanggal AS "Tanggal",
brg.nama AS "Nama Barang",
brg.harga,
SUM(pb.jumlah),
SUM(SUM(pb.jumlah) * brg.harga) OVER (PARTITION BY pb.tanggal) AS "Total Pembelian"
FROM
tbl_pembelian pb, tbl_barang brg
WHERE
pb.barang_id = brg.id
GROUP BY
tanggal, brg.nama, brg.harga);

Untuk mendapatkan nilai ratio untuk suatu ekspresi, aku dapat menggunakan fungsi RATIO_TO_REPORT. Sebagai contoh, aku ingin mendapatkan nilai persentase jumlah penjualan berdasarkan bulan:


SELECT
t.calendar_month_number,
sum(s.quantity_sold),
TO_CHAR(RATIO_TO_REPORT(SUM(s.quantity_sold)) OVER() * 100, '99.99') || ' %' AS "Persentase"
FROM
sales s, times t
WHERE
s.time_id = t.time_id
GROUP BY
t.calendar_month_number;

Fungsi LAG dan LEAD dapat dipakai untuk menggeser baris dari sebuah kolom berdasarkan nilai offset yang ditentukan. LAG akan menggeser sejumlah N offset ke bawah, sementara LEAD akan menggser sejumlah N offset ke atas.

Day 31: SQL Untuk Data Warehousing

Learning In HomeOriginal Date: 12 Februari 2009

Tidak terasa sudah sebulan berada di Pontianak, dan sebentar lagi hari Valentine akan tiba. Dalam suasana penuh cinta ini, aku akan menikmatinya dengan mempelajari SQL yang kerap dipakai dalam query yang berkaitan dengan data warehousing. Aku akan mulai dengan klausa GROUP BY CUBE dan fungsi GROUPING. Anggap saja aku punya tabel TBL_PEMBELIAN dan tabel TBL_KATEGORI. Lalu, aku ingin mendapatkan laporan pembelian per-hari untuk setiap kategori yang ada. Untuk itu, aku dapat menggunakan query berikut:


SELECT pb.tanggal, kat.nama, COUNT(*)
FROM TBL_PEMBELIAN pb, TBL_KATEGORI kat
WHERE pb.barang_id = kat.id GROUP BY CUBE (pb.tanggal, kat.nama);

GROUP BY CUBE pada query di atas selain mengembalikan kombinasi tanggal dan kategori, juga mengembalikan total untuk masing-masing kombinasi dalam bentuk nilai NULL seperti:

----------------------------------
BA
RIS TANGGAL    NAMA      COUNT(*)
----------------------------------
1  NULL       NULL       16
2  NULL       KATEGORI1  8
3  NULL       KATEGORI2  8
4  01/01/2009 NULL       5
5  01/01/2009 KATEGORI1  3
6  01/01/2009 KATEGORI2  2
7  02/01/2009 NULL       11
8  02/01/2009 KATEGORI1  5
9  02/01/2009 KATEGORI2  6
----------------------------------

Nilai ‘NULL’ disini memiliki arti khusus, yaitu subtotal dari sebuah baris. Sebagai contoh, baris 4 adalah baris yang menunjukkan jumlah transaksi pada tanggal 01/01/2009 yaitu 5 (3+2). Begitu juga dengan baris 7. Baris 2 menunjukkan total COUNT untuk seluruh kategori 1, yaitu 8 (3+5). Baris 3 menunjukkan total COUNT untuk seluruh kategori 2, yaitu 8 (2+6).

Agar hasil-nya lebih rapi, aku dapat menggunakan fungsi GROUPING dan DECODE untuk memberi tulisan tertentu pada label untuk nilai subtotal, seperti:


SELECT decode(grouping(pb.tanggal),1, 'Total', pb.tanggal),
decode(grouping(kat.nama), 1, 'Seluruh Kategori', kat.nama),
COUNT(*) FROM TBL_PEMBELIAN pb, TBL_KATEGORI kat WHERE pb.barang_id = kat.id GROUP BY CUBE (pb.tanggal, kat.nama);

Jika aku tidak menginginkan hasil pada baris 2, dan 3, dimana terdapat subtotal untuk masing-masing tanggal, maka aku dapat menggunakan GROUP BY ROLLUP yang tidak berbeda jauh dengan GROUP BY CUBE, sehingga query-nya menjadi:


SELECT decode(grouping(pb.tanggal),1, 'Total', pb.tanggal),
decode(grouping(kat.nama), 1, 'Seluruh Kategori', kat.nama),
COUNT(*) FROM TBL_PEMBELIAN pb, TBL_KATEGORI kat WHERE pb.barang_id = kat.id GROUP BY ROLLUP
(pb.tanggal, kat.nama);

Query yang mengandung fungsi GROUPING() dapat menjadi sulit dibaca jika banyak atribut yang harus diperiksa dengan fungsi tersebut. Oleh sebab itu, fungsi GROUPING_ID() hadir untuk mempermudah. GROUPING_ID() akan mengambil nilai 0 dan 1 dari GROUPING(), lalu merangkainya sesuai urutan atribut menjadi deretan angka 0 dan 1 sebagai bilangan biner. GROUPING() akan menghasilkan konversi bilangan biner ke bilangan desimal dari deretan angka 0 dan 1 ini. Hal ini terlihat dari hasil query berikut:


SELECT
GROUPING(pb.tanggal) as "TANGGAL",
GROUPING(kat.nama) as "KATEGORI",
GROUPING_ID(pb.tanggal, kat.nama)
FROM
tbl_pembelian pb, tbl_kategori kat
WHERE
pb.barang_id = kat.id
GROUP BY
ROLLUP (pb.tanggal, kat.nama);

-------------------------
  TANG KATE   GROUPING
  GAL  GORI   _ID
-------------------------
  0    0         0
  0    0         0
  0    1         1
  0    0         0
  0    0         0
  0    0         0
  0    1         1
  ...  ...       ...
  1    1         3
--------------------------

Dengan demikian, aku dapat memilih level detail subtotal yang ingin ditampilkan berdasarkan GROUPING_ID, dengan menambahkan klausa seperti berikut ini di akhir query:


HAVING
GROUPING_ID(pb.tanggal, brg.nama, brg.harga) >= 1;

Day 30: Dimension

Learning In HomeOriginal Date: 11 Februari 2009

Data warehousing membutuhkan struktur data khusus yang tidak ditemui di OLTP, salah satunya adalah dimension/dimensi. Contoh dimension antara lain time, product dan customer. Dimensi dapat memiliki hierarki, misalnya, dimensi time dapat di-roll up menjadi quarters, dan quarters dapat di-roll up menjadi years, dan years di-roll up menjadi all years. Analisa data umumnya dimulai dari dimensi dengan hierarki tertinggi dan perlahan-lahan di-drill down sesuai kebutuhan.

Sebagai contoh, terdapat sebuah denormalized table dengan nama times yang memiliki atribut seperti calendar_year, calendar_quarter_number, calendar_month_number, calendar_week_number, dan day_number_in_week, dan lainnya. Aku dapat merepresentasikan hubungan hierarki antar-kolom di tabel tersebut dengan membuat dimensi sebagai berikut:


CREATE DIMENSION LATIHAN_TIMES
  LEVEL year IS 
    (times.calendar_year)
  LEVEL quarter IS 
    (times.calendar_quarter_number)
  LEVEL month IS 
    (times.calendar_month_number)
  LEVEL week IS 
    (times.calendar_week_number)
  LEVEL day IS 
    (times.day_number_in_week)
  HIERARCHY times_rollup (
    day  CHILD OF
    week CHILD OF
    month CHILD OF
    quarter CHILD OF
    year
  );

Untuk melihat keterangan mengenai dimensi yang baru saja aku buat tadi, aku dapat menggunakan perintah:


EXECUTE dbms_dimension.describe_dimension('LATIHAN_TIMES');

Untuk memeriksa apakah dimensi yang baru aku buat valid, aku akan menggunakan API dbms_dimension.validate_dimension(). Tapi sebelum itu, aku akan membuat sebuah tabel DIMENSION_EXCEPTIONS terlebih dahulu dengan menjalankan script %ORA_HOME%\rdbms\admin\utldim.sql. Setelah itu, aku menjalankan kode berikut:


EXECUTE dbms_dimension.validate_dimension('LATIHAN_TIMES', FALSE, TRUE, 'check1');

Aku akan mendapatkan daftar kesalahan di tabel DIMENSION_EXCEPTION. Disini terdapat rowid baris mana yang invalid. Kesalahan yang aku buat adalah hubungan relasi 1:n antara sebuah parent dan anak-anaknya. Sebuah ‘year’ dapat memiliki banyak ‘quarter number’, tetapi sebuah ‘quarter number’ tidak dapat mengandung lebih dari satu parent ‘year’. Untuk itu, aku harus menggunakan id agar mendapatkan quarter number yang unik di setiap year yang ada. Begitu juga dengan hierarki di bawahnya sehingga dimensi-nya kira-kira seperti berikut ini:


DROP DIMENSION LATIHAN_TIMES;

CREATE DIMENSION LATIHAN_TIMES
  LEVEL year IS 
    (times.calendar_year)
  LEVEL quarter IS 
    (times.calendar_quarter_id)
  LEVEL month IS 
    (times.calendar_month_id)  
  LEVEL day IS 
    (times.time_id)
  HIERARCHY times_rollup (
    day  CHILD OF    
    month CHILD OF
    quarter CHILD OF
    year
  );