Klausa Oracle WITH adalah salah satu teknik yang paling umum digunakan untuk menyederhanakan kode sumber SQL dan meningkatkan kinerja. Di Oracle SQL, klausa “WITH”, juga dikenal sebagai Common Table Expression (CTE), adalah alat canggih yang juga digunakan untuk meningkatkan keterbacaan kode.

WITH biasanya digunakan untuk mendefinisikan kumpulan hasil bernama sementara, juga disebut subkueri atau CTE seperti yang didefinisikan sebelumnya. Kumpulan nama sementara ini dapat direferensikan beberapa kali dalam kueri SQL SELECT utama. CTE seperti tabel virtual dan sangat berguna untuk mengatur dan memodulasi kode SQL.

Memahami klausa WITH

Sintaksis

Menggunakan klausa WITH sangat sederhana. Buat namespace dengan operator AS diikuti dengan kueri SELECT dan Anda dapat menambahkan kueri SELECT sebanyak yang Anda inginkan diikuti dengan koma (,). Merupakan praktik yang baik untuk menggunakan istilah yang bermakna untuk namespace guna membedakannya di SELECT utama.

Dalam hal eksekusi internal klausa WITH, Oracle akan mengeksekusi namespace secara internal satu per satu dan menyimpan hasilnya dalam memori yang kemudian akan digunakan oleh SQL SELECT utama. Ini meniru tampilan terwujud dengan hasil antara dan mengurangi perhitungan yang berlebihan. Hal ini menunjukkan bahwa Oracle mengoptimalkan kueri SQL dengan CTE dengan menyimpan sementara hasil subkueri, memungkinkan pengambilan dan pemrosesan lebih cepat di bagian kueri selanjutnya.

WITH 
    cte_name1 as (SELECT * FROM Table1),
    cte_name2 as (SELECT * FROM Table2),
    ...
SELECT ...
FROM cte_name1, cte_name2
WHERE ...;

Kasus penggunaan

Dalam kasus penggunaan ini, saya akan berbicara secara spesifik tentang bagaimana Anda dapat menggunakan gabungan dalam secara efektif, menggunakan klausa WITH, yang dapat sangat membantu meningkatkan kinerja proses. Pertama-tama mari kita lihat kumpulan data dan pernyataan masalah sebelum mendalami solusinya lebih dalam.

Skenarionya adalah rantai ritel e-commerce di mana data harga jual produk massal perlu dimuat untuk lokasi toko online tertentu. Bayangkan suatu produk dapat memiliki beberapa garis harga yang ditujukan untuk harga reguler, harga promosi, dan penawaran BOGO. Dalam hal ini, pengguna mencoba membuat beberapa garis harga promosi dan mengabaikan kemungkinan kesalahan yang mungkin mereka lakukan. Melalui proses ini, kami akan mendeteksi duplikat data yang secara fungsional berlebihan dan mencegah terciptanya data berkualitas buruk dalam sistem penetapan harga. Dengan melakukan hal ini, kami akan menghindari kegagalan program antarmuka di lapisan tengah repositori harga, yang berfungsi sebagai jembatan antara mesin penetapan harga dan repositori harga yang dapat diakses oleh platform e-commerce.

TABEL: e_promosi

PS: Ini contoh datanya, tapi di dunia nyata, ribuan dan jutaan garis harga bisa diperbarui untuk menurunkan atau menaikkan harga setiap minggunya.

Tabel di atas menampilkan masing-masing kode UPC dan item di baris harga 10001. Masalah dengan kumpulan data ini adalah pengguna back office mencoba membuat baris duplikat sebagai bagian dari harga baris yang sama melalui proses pengunggahan dan pengguna tidak menyadarinya dari duplikat data yang mungkin mereka buat.

Tujuannya di sini adalah untuk menangkap catatan duplikat dan membuang entri 1 dan 2 sehingga pengguna dapat memutuskan mana di antara keduanya yang harus ada dalam sistem penetapan harga untuk diterapkan di situs web.

Menggunakan kode di bawah ini akan menyederhanakan deteksi kesalahan dan juga mengoptimalkan solusi prosedur penyimpanan untuk kinerja yang lebih baik.

WITH price_lines as
         (SELECT rowid, price_line, UPC, start_dt, end_dt
            FROM e_promotions
           WHERE price_line = 10001
             AND flag = 0)
        SELECT MIN(a.rowid) as price_line, UPC, start_dt, end_dt
          FROM price_lines a, price_lines b
         WHERE a.price_line = b.price_line
           AND a.flag = b.flag
           AND a.UPC = b.UPC
           AND a.rowid <> b.rowid
           AND (a.start_dt BETWEEN b.start_dt AND b.end_dt OR
               a.end_dt BETWEEN b.start_dt AND b.end_dt OR
               b.start_dt BETWEEN a.start_dt AND a.end_dt OR
               b.end_dt BETWEEN a.start_dt AND a.end_dt)
         GROUP BY a.price_line, a.UPC;

Dengan kode di atas kita melakukan dua hal secara paralel:

1. Saya telah menanyakan tabel satu kali untuk kumpulan data yang perlu kita proses menggunakan klausa WITH.
2. Menambahkan gabungan dalam untuk mendeteksi duplikat tanpa harus menanyakan tabel untuk kedua kalinya, sehingga mengoptimalkan kinerja proses penyimpanan

Ini adalah salah satu dari banyak kasus penggunaan yang pernah saya gunakan sebelumnya yang telah memberi saya peningkatan kinerja yang signifikan dalam pengkodean PLSQL dan SQL saya.

Bersenang-senanglah dan kirimkan komentar Anda jika Anda memiliki pertanyaan!