Gunakan klausa WITH dalam pernyataan SELECT - AnalyticDB

Anda dapat menggunakan klausa WITH untuk membuat ekspresi tabel umum (common table expression/CTE). CTE merupakan set hasil sementara yang berlaku selama durasi satu pernyataan SQL. Kueri SELECT kemudian dapat mereferensikan set hasil tersebut. CTE dapat digunakan untuk meratakan kueri bersarang atau menyederhanakan subkueri. Subkueri dalam klausa WITH hanya dieksekusi sekali, sehingga dapat meningkatkan performa kueri. Topik ini menjelaskan cara menggunakan klausa WITH dalam pernyataan SELECT.

Catatan

Satu klausa WITH dapat mendefinisikan satu atau beberapa CTE. Pisahkan beberapa CTE dengan koma (,). Setiap CTE harus diikuti oleh pernyataan SQL atau CTE lainnya.
Paging tidak didukung dalam pernyataan CTE.

Penggunaan

Kedua kueri berikut ini ekuivalen

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

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

Gunakan klausa WITH untuk multiple subkueri

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;

Relasi yang didefinisikan dalam klausa WITH dapat saling mereferensi

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 direferensikan beberapa kali hanya akan dieksekusi sekali, sehingga dapat meningkatkan performa beberapa kueri.

Penting

Mengaktifkan optimasi eksekusi CTE dapat menurunkan performa beberapa kueri. Jika terjadi penurunan performa yang signifikan, sebaiknya nonaktifkan fitur optimasi tersebut.

Aktifkan optimasi eksekusi CTE

Aktifkan optimasi eksekusi CTE untuk kueri tertentu

Untuk mengaktifkan optimasi eksekusi CTE pada kueri tertentu, tambahkan petunjuk sebelum pernyataan kueri. Contoh berikut menunjukkan cara mengoptimalkan kueri pada dataset bawaan:

/*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;

Aktifkan eksekusi CTE teroptimalkan untuk semua kueri
Jalankan pernyataan SET untuk mengaktifkan optimasi eksekusi CTE untuk semua kueri. Contoh berikut menunjukkan cara mengaktifkan optimasi tersebut:
```
SET adb_config cte_execution_mode=shared;
```

Nonaktifkan optimasi eksekusi CTE

Nonaktifkan optimasi CTE untuk kueri tertentu

Untuk menonaktifkan optimasi eksekusi CTE pada kueri tertentu, tambahkan petunjuk sebelum pernyataan kueri. Contoh berikut menunjukkan cara menonaktifkan optimasi tersebut untuk kueri pada dataset bawaan:

/*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;

Nonaktifkan optimasi eksekusi CTE untuk semua kueri
Jalankan pernyataan SET untuk menonaktifkan optimasi eksekusi CTE untuk semua kueri. Contoh berikut menunjukkan cara menonaktifkan optimasi tersebut:
```
SET adb_config cte_execution_mode=inline;
```