Gunakan klausa WITH dalam pernyataan SELECT - AnalyticDB

Klausa WITH mendefinisikan common table expression (CTE), yaitu set hasil sementara bernama yang cakupannya terbatas pada satu pernyataan SQL. CTE tersebut dapat dirujuk seperti tabel dalam pernyataan SELECT yang mengikutinya. CTE menyederhanakan kueri kompleks dengan menggantikan subkueri bersarang yang rumit menggunakan blok penyusun yang mudah dibaca dan dapat digunakan kembali.

Cara kerja

Semua CTE dalam klausa WITH didefinisikan sebelum kueri utama dijalankan. Subkueri dalam klausa WITH hanya dieksekusi sekali, yang dapat meningkatkan performa kueri. Dengan optimasi eksekusi CTE diaktifkan, CTE yang dirujuk beberapa kali akan dieksekusi tepat satu kali, dan semua referensi membaca dari hasil bersama tersebut.

Sintaks

WITH
  cte_name AS (subquery)
  [, cte_name2 AS (subquery2) ...]
SELECT ...
FROM cte_name [, cte_name2 ...];

Pisahkan beberapa CTE dengan koma.
Setiap CTE harus diikuti oleh CTE lain atau pernyataan SQL utama.
CTE yang didefinisikan lebih awal dalam daftar dapat dirujuk oleh CTE yang didefinisikan kemudian.

Catatan penggunaan

Paging tidak didukung dalam pernyataan CTE.
Klausa WITH didukung dalam pernyataan SELECT.

Contoh

Ganti subkueri bersarang

Kedua kueri berikut setara. Versi CTE lebih mudah dibaca dan dipelihara.

-- Nested subquery
SELECT a, b
FROM (SELECT a, MAX(b) AS b FROM t GROUP BY a) AS x;

-- Equivalent CTE
WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a)
SELECT a, b FROM x;

Definisikan beberapa CTE

Gunakan satu klausa WITH untuk mendefinisikan beberapa CTE dan lakukan JOIN di kueri utama.

WITH
t1 AS (SELECT a, MAX(b) AS b FROM x GROUP BY a),
t2 AS (SELECT a, AVG(d) AS d FROM y GROUP BY a)
SELECT t1.*, t2.*
FROM t1 JOIN t2 ON t1.a = t2.a;

Rangkai CTE

CTE dapat merujuk CTE sebelumnya dalam klausa WITH yang sama.

WITH
x AS (SELECT a FROM t),
y AS (SELECT a AS b FROM x),
z AS (SELECT b AS c FROM y)
SELECT c FROM z;

Optimasi eksekusi CTE

Kluster AnalyticDB for MySQL yang menjalankan kernel versi 3.1.9.3 atau lebih baru mendukung optimasi eksekusi CTE. Fitur ini dinonaktifkan secara default. Untuk mengaktifkannya, atur item konfigurasi CTE_EXECUTION_MODE. Saat diaktifkan, subkueri CTE yang dirujuk beberapa kali hanya dieksekusi sekali, dan semua referensi membaca dari hasil bersama tersebut—sehingga menghindari komputasi berulang.

Penting

Mengaktifkan optimasi eksekusi CTE dapat menurunkan performa untuk beberapa kueri. Jika Anda mengamati penurunan performa yang signifikan, nonaktifkan optimasi tersebut.

Aktifkan optimasi eksekusi CTE

Untuk kueri tertentu, tambahkan petunjuk /*cte_execution_mode=shared*/ sebelum pernyataan:

/*cte_execution_mode=shared*/
WITH shared AS (SELECT L_ORDERKEY, L_SUPPKEY FROM ADB_SampleData_TPCH_10GB.lineitem JOIN ADB_SampleData_TPCH_10GB.orders WHERE L_ORDERKEY = O_ORDERKEY)
SELECT * FROM shared s1, shared s2 WHERE s1.L_ORDERKEY = s2.L_SUPPKEY;

Untuk semua kueri, jalankan:

SET adb_config cte_execution_mode=shared;

Nonaktifkan optimasi eksekusi CTE

Untuk kueri tertentu, tambahkan petunjuk /*cte_execution_mode=inline*/ sebelum pernyataan:

/*cte_execution_mode=inline*/
WITH shared AS (SELECT L_ORDERKEY, L_SUPPKEY FROM ADB_SampleData_TPCH_10GB.lineitem JOIN ADB_SampleData_TPCH_10GB.orders WHERE L_ORDERKEY = O_ORDERKEY)
SELECT * FROM shared s1, shared s2 WHERE s1.L_ORDERKEY = s2.L_SUPPKEY;

Untuk semua kueri, jalankan:

SET adb_config cte_execution_mode=inline;