Posts tagged ‘SQL’
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 Home-Original 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 Home-Original 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 Home-Original 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 Home-Original 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 Home-Original 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
);
Day 29: Materialized View
Learning In Home-Original Date: 10 Februari 2009
Oracle Database selain menawarkan fitur OLTP (Online Transaction Processing), juga mendukung data Warehousing. Secara singkatnya, aku bisa bilang kalau OLTP adalah proses database sehari-hari dimana data di-input oleh user dan disimpan di tabel relational, sedangkan data warehousing adalah proses penyimpanan data untuk keperluan query dan analisa. Ada beberapa karakteristik data warehousing yang berbeda dari OLTP, misalnya, memiliki banyak index, sedikit join, dan tabel yang tidak ter-normalisasi.
Data yang dipakai dalam data warehousing adalah data historical berbulan-bulan atau bertahun-tahun yang lalu. Oleh sebab itu, kebanyakan fact table dalam data warehousing memiliki ukuran yang sangat besar. Salah satu cara untuk meningkatkan performance dalam data warehousing adalah dengan membuat summary. Implementasi tabel summary ini pada database Oracle dapat dicapai dengan menggunakan materialized view. Berikut ini adalah contoh SQL untuk membuat materialized view:
CREATE MATERIALIZED VIEW
LOG ON products
WITH SEQUENCE, ROWID
(prod_id, prod_name)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW
LOG ON sales
WITH SEQUENCE, ROWID
(prod_id, quantity_sold, amount_sold)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW
products_sales
BUILD IMMEDIATE
REFRESH FAST
ENABLE QUERY REWRITE
AS
SELECT
p.prod_id,
sum(quantity_sold)
as quantity_sold,
sum(amount_sold)
as amount_sold
FROM sales s, products p
WHERE
s.prod_id = p.prod_id
GROUP BY
p.prod_id;
Tabel sales di schema SH (Sales History, demo schema bawaan Oracle Database) adalah tabel yang isinya besar yang mencapai 1.016.271 record. Setiap query yang mengandung SUM, AVG, dan sebagainya yang umum dipergunakan dalam reporting pasti akan menggunakan banyak resource jika harus di-proses ulang setiap kali request. Pada contoh di atas, aku menyertakan klausa ENABLE QUERY REWRITE untuk mengaktifkan query rewrite. Aku juga menyertakan klausa REFRESH FAST. Ini berarti, aku ingin materialized view tersebut nilainya diperbaharui secara otomatis jika ada perubahan pada tabel yang membentuknya, yaitu tabel products dan tabel sales. Perubahan ini akan di-apply secara incremental berdasarkan data dari log yang sudah buat untuk tabel sales dan tabel products sehingga proses refresh bisa lebih singkat.
Materialized view akan dipakai secara otomatis melalui teknik yang disebut query rewrite tanpa perlu referensi ke materialized view bersangkutan di statement SQL. Dengan demikian, operasi materialized view berlangsung secara transparan bagi user yang memberikan statement SQL. Sebuah query SQL akan melalui beberapa proses untuk menentukan apakah ia akan mengalami query rewrite atau tidak. Oracle akan melalui perbandingan full text untuk menentukan apakah query SQL sesuai dengan query di materialized view (perbandingan ini tidak menyertakan whitespace). Jika tidak ketemu, ia akan membandingkan bagian FROM dari query SQL dan query di materialized view. Jika masih belum ketemu juga, maka ia akan menggunakan general query rewrite.
Oracle SQL Basic: Function
Salah satu hal yang sering yang saya lakukan dalam membuat query SQL adalah mengambil nilai tanggal, bulan, atau tahun dari sebuah tipe data DATE. Fungsi EXTRACT dapat membantu disini, contohnya seperti berikut:
SELECT * FROM tblPemesanan WHERE
EXTRACT (MONTH FROM TANGGAL) = 8
Untuk menghitung selisih bulan, fungsi MONTHS_BETWEEN dapat dipergunakan, seperti pada contoh berikut:
SELECT MONTHS_BETWEEN(CURRENT_DATE,
DATE '2009-01-26') FROM DUAL;
Untuk mendapatkan hari Sabtu berikutnya dari hari ini, fungsi NEXT_DAY dapat dipergunakan, seperti pada contoh:
SELECT NEXT_DAY (CURRENT_DATE, 'SATURDAY') FROM DUAL;
Ok, mungkin aku sudah menulis terlalu banyak fungsi yang berhubungan dengan DATE. Ada banyak sekali fungsi lainnya yang jarang dipergunakan, misalnya fungsi USER untuk mengetahui user yang sedang login:
SELECT USER FROM DUAL;
Oracle SQL Basic: ROWNUM
ROWNUM adalah salah satu pseudocolumn di Oracle yang mengembalikan sebuah angka yang menunjukkan posisi baris di tabel. Nilai ROWNUM pada baris pertama akan berupa 1, pada baris kedua berupa 2, dan seterusnya.
Salah satu hal yang bisa menjebak dalam penggunaan ROWNUM, misalnya dalam top-N reporting. Saat kita diminta untuk menampilkan 10 employee pertama setelah di-urut-kan berdasarkan nama, mungkin sekali yang terbayang dalam benak kita adalah berikut:
SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;
Sayangnya, query di atas dapat saja menghasilkan nilai yang berbeda, karena setiap baris akan diurutkan ulang berdasarkan ORDER BY. Cara yang lebih aman adalah dengan dengan query berikut:
SELECT * FROM (SELECT * FROM employees ORDER BY last_name)
WHERE ROWNUM < 11;
Salah satu misteri yang berhubungan dengan ROWNUM adalah mengapa query berikut selalu tidak menghasilkan apa-apa?
SELECT * FROM employees WHERE ROWNUM > 1;
Pada awalnya aku berpikir query ini seharusnya menghasilkan baris ke-dua sampai baris terakhir. Tapi ternyata tidak demikian. Hal ini karena, pada saat baris pertama di-fetch, ia akan memiliki ROWNUM = 1, dan ini membuat kondisi (ROWNUM > 1) bernilai false. Lalu saat baris kedua di-fetch, ia juga memiliki ROWNUM = 1 mengingat baris pertama tidak memenuhi kondisi. Akibatnya kondisi (ROWNUM > 1) juga bernilai false. Dan begitu selanjutnya, sehingga tidak ada baris yang dikembalikan.
Oracle SQL Basic: Tipe Data
Bagian yang paling dasar dalam mempelajari SQL adalah mengenal tipe data. Oracle 10g selain mendukung tipe data SQL99 juga memiliki beberapa tipe data-nya sendiri, seperti VARCHAR2, NVARCHAR2, BINARY_FLOAT, dan sebagainya.
Untuk tipe data tanggal dan waktu, aku bisa menggunakan tipe data DATE. Data untuk tipe DATE ini bisa dalam bentuk ANSI date literal, yang harus dalam format ‘YYYY-MM-DD’ seperti:
DATE '2008-12-25'
atau dalam bentuk Oracle date value, seperti:
TO_DATE('25-12-2008 10:00:00', 'DD-MM-YYYY HH24:MI:SS')
O ya, kedua data di atas sebenarnya berbeda, misalnya jika di-compare melalui WHERE. Hal ini karena saat memakai ANSI date literal, nilai DATE tersebut adalah 25 Desember 2008 pada tepat tengah malam, atau jam 00:00. Sedangkan yang satunya lagi adalah 25 Desember 2008 pada jam 10 pagi. Kalau kita tidak mempedulikan bagian jam (time) dan hanya ingin memakai tanggal (date), maka data DATE tersebut dapat di-truncate terlebih dahulu, seperti pada contoh ini:
SELECT * FROM LATIHAN WHERE TRUNC(TANGGAL) = DATE '2008-12-25';
Jika aku membutuhkan informasi DATE yang lebih spesifik (lebih precise), maka aku bisa menggunakan tipe data TIMESTAMP. Tipe data ini memungkinkan nilai pecahan untuk detik hingga 9 digit, seperti:
TIMESTAMP '2008-12-25 10:00:00.898999'
Untuk menyimpan interval, aku bisa menggunakan tipe data INTERVAL YEAR TO MONTH dan INTERVAL DAY TO SECOND, seperti pada contoh berikut:
CREATE TABLE Latihan ( MasaBerlaku INTERVAL YEAR TO MONTH ); INSERT INTO Latihan VALUES ( INTERVAL '5' YEAR(1));
Nilai 1 setelah YEAR, menunjukkan presisi YEAR, dimana dalam contoh di atas berupa 1 digit, yaitu lima (5) tahun. O ya, data bertipe INTERVAL juga dapat di-operasi-kan dengan data bertipe DATE untuk menghasilkan DATE. Misalnya, aku ingin melihat tanggal kadaluarsa berdasarkan tanggal hari ini dari masing-masing record di tabel percobaan di atas, aku bisa menggunakan SQL berikut ini:
SELECT TRUNC(SYSDATE) + MasaBerlaku FROM Latihan;
