Penggunaan dan skenario hint - Hologres

Petunjuk (hint) adalah sintaks SQL tambahan yang memungkinkan Anda mengubah cara eksekusi suatu pernyataan SQL. Fitur ini mendukung optimasi SQL khusus. Hologres versi V2.2 dan yang lebih baru mendukung sintaks hint. Dengan hint, Anda dapat mengubah metode eksekusi pernyataan SQL, menyetel kueri SQL, serta meningkatkan performa. Topik ini menjelaskan cara menggunakan hint dan menyediakan skenario umum.

Prasyarat

Sebelum menggunakan fitur hint, atur parameter Grand Unified Configuration (GUC) pg_hint_plan_enable_hint ke on pada tingkat session atau database:

Aktifkan parameter pada tingkat session.
```
SET pg_hint_plan_enable_hint=on;
```
Aktifkan parameter pada tingkat database. Pengaturan ini berlaku untuk koneksi baru.
```
ALTER DATABASE <dbname> SET pg_hint_plan_enable_hint=on;
```

Batasan

Fitur hint memerlukan Hologres V2.2 atau versi yang lebih baru. Jika instans Anda menggunakan V2.1 atau versi sebelumnya, Anda harus melakukan upgrade instans tersebut.

Catatan Penggunaan

Anda dapat menentukan hint untuk tabel reguler (termasuk foreign table), subquery, dan Common Table Expressions (CTEs). Hint tidak didukung untuk view.
Seluruh isi hint harus diapit oleh /*+HINT dan */. Komentar tidak diperbolehkan dalam hint.
Kata kunci hint tidak peka terhadap huruf besar/kecil.
Sebuah petunjuk dapat berisi beberapa kata kunci.
Hint berlaku untuk tingkat kueri tertentu. Parameter yang Anda tentukan dalam hint harus terlihat dalam cakupan saat ini. Anda tidak dapat menggunakan parameter dari kueri induk atau subquery lain. Misalnya, dalam pernyataan SQL berikut, hint /*+HINT Leading(tt t2) */ hanya dapat menentukan tt dan t2 sebagai parameter. Anda tidak dapat menggunakan t1, t3, or t. Demikian pula, hint /*+HINT Leading(t t1) */ hanya dapat menggunakan t dan t1. Anda tidak dapat menggunakan t2, t3, or tt.
```
SELECT /*+HINT Leading(t t1) */ * FROM t1 join (
  SELECT /*+HINT Leading(tt t2) */ * FROM t2 join (
    SELECT * FROM t3
  ) tt
) t;
```

Untuk pernyataan INSERT INTO ... SELECT, cakupan kata kunci `INSERT` mencakup tabel tujuan dan tabel sumber terluar dari pernyataan `SELECT`. Cakupan kata kunci `SELECT` tidak mencakup tabel tujuan. Untuk mencegah konflik, jika Anda menentukan petunjuk setelah `INSERT`, Anda tidak dapat menentukan petunjuk lain dalam pernyataan `SELECT`. Contoh berikut menunjukkan penggunaan yang benar dan salah:

Penggunaan yang benar

-- Contoh 1: Hint setelah INSERT mendukung target, t1, dan t2 sebagai parameter.
INSERT /*+HINT Leading(target (t1 t2)) */ INTO target SELECT t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a

-- Contoh 2: Hint setelah SELECT hanya mendukung t1 dan t2 sebagai parameter.
INSERT INTO target SELECT /*+HINT Leading(t2 t1) */ t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a;

Penggunaan yang salah

-- Anda tidak dapat menggunakan hint setelah INSERT dan SELECT sekaligus. Jika dilakukan, akan muncul error.
INSERT /*+HINT Leading(target (t1 t2)) */ INTO target SELECT /*+HINT Leading(t2 t1) */ t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a;

-- Pesan error
ERROR: insert statement with hint should not have sub select with hint at the same time

Hint GUC hanya berlaku untuk kueri saat ini. Hint GUC yang ditetapkan pada tingkat apa pun dalam kueri akan memengaruhi seluruh kueri tersebut. Setelah kueri dieksekusi, kueri berikutnya tidak terpengaruh oleh hint GUC tersebut.
Contoh: Jika Anda menetapkan hint GUC dalam subquery untuk menonaktifkan count distinct reuse, pengaturan tersebut akan berlaku untuk seluruh kueri.
```
SELECT
    count(DISTINCT a),
    count(DISTINCT b)
FROM (
    SELECT
        /*+HINT set(hg_experimental_enable_reuse_cte_of_count_distinct off) */
        t1.a t2.b
    FROM
        t1
        JOIN t2 ON t1.a = t2.a)
```
Anda dapat menggunakan tanda kurung `()` dalam hint untuk menentukan prioritas parameter. Tanda kurung bersarang diperbolehkan. Misalnya, hint Leading(t1 t2 t3) menentukan bahwa `t1` dan `t2` digabungkan terlebih dahulu, lalu hasilnya digabungkan dengan `t3`. Hint Leading(t1 (t2 t3)) menentukan bahwa t2` dan `t3 digabungkan terlebih dahulu, lalu hasilnya digabungkan dengan `t1`.
Hint Join Method dan Join Order memerlukan minimal dua parameter valid agar berlaku. Jika jumlah parameter valid yang diberikan tidak mencukupi, hint tersebut tidak berlaku.
Catatan
Parameter valid: Parameter valid adalah tabel, subquery, dan CTE yang termasuk dalam cakupan tingkat kueri saat ini. Misalnya, hint Leading(t1 t1) dan Leading(t1) hanya berisi satu parameter valid, yaitu `t1`.
Hint Runtime Filter hanya berlaku pada operasi HashJoin.
Jika rencana kandidat yang dihasilkan tidak berisi penggabungan tabel yang ditentukan oleh hint metode join, hint tersebut tidak berlaku. Misalnya, jika Anda menentukan HashJoin(t1 t2) tetapi rencana yang dihasilkan melakukan operasi t1 Join t3 lalu menggabungkan `t2`, hint tersebut tidak berlaku. Dalam kasus ini, Anda dapat menambahkan Leading(t1 t2) untuk memaksa urutan penggabungan.
Jika kata kunci SELECT diikuti oleh beberapa hint berturut-turut yang diapit oleh /*+HINT dan */, hanya hint pertama yang berlaku. Misalnya, dalam pernyataan SELECT /*+HINT HashJoin(t1 t2) */ /*+HINT Leading(t1 t2) */ ..., hanya hint `HashJoin` yang berlaku, sedangkan hint `Leading` diabaikan.
Jika tabel yang didefinisikan dalam hint jenis yang sama saling bertentangan, hint yang didefinisikan pertama kali memiliki prioritas lebih tinggi.
Catatan
Konflik terjadi dalam skenario berikut:
- Dua hint berisi tabel yang sama.
- Kumpulan tabel identik.
- Untuk hint Join Order, parameter dari satu hint merupakan subset dari parameter hint lainnya.
- Parameter untuk hint Join Method, Runtime Filter, atau skew join tidak boleh merupakan subset dari parameter hint lainnya.
- Contoh 1: Hint HashJoin(t1 t2) dan NestLoop(t2 t1) menentukan kumpulan tabel yang sama. Dalam hal ini, terjadi konflik dan hanya hint HashJoin(t1 t2) yang berlaku.
```
SELECT /*+HINT HashJoin(t1 t2) NestLoop(t2 t1) */ ...
```
- Contoh 2: Tabel dalam hint Leading(t1 t2) merupakan subset dari tabel dalam hint Leading(t1 t2 t3). Dalam hal ini, terjadi konflik dan hanya hint Leading(t1 t2) yang berlaku.
```
SELECT /*+HINT Leading(t1 t2) Leading(t1 t2 t3) */ ...
```
Jika rencana yang ditentukan oleh hint tidak memenuhi kondisi pembuatan rencana, rencana eksekusi tidak dapat dihasilkan. Misalnya, jika Anda menentukan `NestLoop` dan `Right Join` untuk dua tabel, rencana yang ditentukan tidak didukung. Akibatnya, pesan error dikembalikan, seperti ERROR: ORCA failed to produce a plan : No plan has been computed for required properties. Hal ini menunjukkan bahwa rencana tidak dapat dihasilkan karena properti yang diperlukan tidak terpenuhi.

Sintaks

Gunakan sintaks berikut untuk menulis hint dalam pernyataan SQL:

SELECT|UPDATE|INSERT|DELETE /*+HINT <HintName(params)>  */ ...

HintName(params) merepresentasikan kata kunci hint dan parameter yang sesuai. Untuk informasi lebih lanjut, lihat Kata kunci hint.

Catatan

Kata kunci hint tidak peka terhadap huruf besar/kecil.
Anda hanya dapat menentukan hint tepat setelah kata kunci `INSERT`, `UPDATE`, `DELETE`, atau `SELECT`.
Isi hint harus diapit oleh /*+HINT dan */.

Kata Kunci Petunjuk

Tabel berikut menjelaskan kata kunci petunjuk yang didukung dan format parameternya.

Tipe	Format Parameter	Deskripsi	Contoh	Catatan
Metode Join	`NestLoop(table1 table2[ table...])`	Memaksa nested loop join.	`SELECT /+HINT NestLoop(table1 table2) / * FROM table1 JOIN table2 ON table1.a = table2.a;`	Hint hanya berlaku jika mencakup minimal dua parameter valid. Catatan Parameter valid mencakup tabel, subquery, dan Common Table Expressions (CTEs) dalam cakupan tingkat kueri saat ini. Fitur ini didukung di Hologres V2.2 atau versi yang lebih baru.
Metode Join	`HashJoin(table1 table2[ table...])`	Memaksa hash join.	`SELECT /+HINT HashJoin(table1 table2 table3) / table1.a FROM table1 JOIN table2 ON table1.a = table2.a JOIN table3 ON table1.a = table3.a;`
Urutan Join	`Leading(table1 table2[ table...])`	Memaksa join mengikuti urutan yang ditentukan.	`SELECT /+HINT Leading(table2 table1) / table1.a from table1 Join table2 on table1.a = table2.a;`
Urutan Join	`Leading(<Join pair>)`	Memaksa urutan dan arah join. Catatan Join pair adalah pasangan tabel atau pasangan join lain yang diapit tanda kurung. Pasangan ini dapat membentuk struktur bersarang.	`SELECT /+HINT Leading((table2 table1) (table3 table4)) / * FROM table1 LEFT JOIN table2 ON table1.a = table2.a RIGHT JOIN table3 ON table1.a = table3.a LEFT JOIN table4 ON table3.a = table4.a ORDER BY table1.a;`
Filter Runtime	`RuntimeFilter(table1 table2[ table...])`	Memaksa hash join pada tabel yang ditentukan untuk menggunakan runtime filter.	`SELECT /+HINT RuntimeFilter(table1 table2) / * FROM table1 JOIN table2 ON table1.a = table2.a;`	Hanya berlaku untuk HashJoin. Didukung di Hologres V2.2 atau versi yang lebih baru.
GUC	`Set(GUC-parameter value)`	Menentukan nilai parameter GUC selama pembuatan rencana. Catatan GUC-parameter menunjukkan nama parameter GUC. value menunjukkan nilai parameter GUC. Untuk informasi lebih lanjut tentang parameter GUC yang didukung oleh Hologres, lihat Parameter GUC.	`EXPLAIN SELECT /+HINT set(hg_experimental_enable_reuse_cte_of_count_distinct off) / count(DISTINCT a), count(DISTINCT b) FROM table1;`	Petunjuk hanya berlaku untuk query saat ini. Setelah query saat ini selesai, query berikutnya tidak terpengaruh oleh petunjuk GUC. Didukung di Hologres V2.2 dan versi yang lebih baru.
Motion Hint	`Broadcast(table table[ table...])`	Memaksa broadcast koleksi pada salah satu sisi JOIN.	`SELECT /+HINT Broadcast(t2) Leading(t1 t2) / * FROM t1 JOIN t2 ON t1.a=t2.a;`	Umumnya digunakan bersama Leading. Didukung di Hologres V3.0 atau versi yang lebih baru.
	`NoBroadcast(table table[ table...])`	Mencegah tabel dari koleksi untuk di-broadcast dalam JOIN.	`SELECT /+HINT NoBroadcast(t2) Leading(t1 t2) / * FROM t1 JOIN t2 ON t1.a=t2.a;`
	`Gather(table table[ table...])`	Memaksa operasi Gather pada salah satu sisi koleksi tabel dalam JOIN.	`SELECT /+HINT Gather(t2) Leading(t1 t2) / * FROM t1 JOIN t2 ON t1.a=t2.a;`
	`NoGather(table table[ table...])`	Memaksa operasi JOIN melewati langkah Gather pada salah satu sisi koleksi tabel.	`SELECT /+HINT NoGather(t2) Leading(t1 t2) / * FROM t1 JOIN t2 ON t1.a=t2.a;`

Skenario

Contoh berikut menunjukkan skenario penggunaan hint. Tabel dalam contoh dibuat dengan menjalankan pernyataan Data Definition Language (DDL) berikut:

CREATE TABLE target (a int primary key, b int);
CREATE TABLE t1 (a int, b int);
CREATE TABLE t2 (a int, b int);
CREATE TABLE t3 (a int);
CREATE TABLE t4 (a int);

Gunakan hint untuk menyesuaikan urutan join

Tujuan utama hint urutan join adalah menyesuaikan urutan penggabungan tabel. Urutan join yang tidak efisien dapat berdampak signifikan terhadap performa kueri SQL. Urutan join yang tidak efisien biasanya disebabkan oleh statistik yang hilang atau tidak akurat.

Statistik hilang: Biasanya terjadi karena operasi `ANALYZE` tidak dijalankan secara tepat waktu. Untuk informasi lebih lanjut tentang operasi `ANALYZE`, lihat ANALYZE dan AUTO ANALYZE.
Statistik tidak akurat: Dapat terjadi setelah operasi filter atau join. Statistik menjadi usang, sehingga menyebabkan deviasi besar antara set hasil aktual dan perkiraan jumlah baris.

Ketika urutan join tidak efisien, Anda dapat menyesuaikannya secara manual menggunakan parameter GUC atau hint. Menyesuaikan urutan join dengan hint lebih sederhana dan nyaman dibandingkan menggunakan parameter GUC.

Dalam contoh SQL berikut, tabel digabungkan sebagai t1 Join t2. Dalam Hash Join, database menggunakan tabel yang lebih kecil untuk membangun tabel hash. Operasi ini ditampilkan di bawah operator Hash dalam rencana eksekusi. Jika tabel t2 memiliki jumlah baris jauh lebih banyak daripada tabel t1, performa kueri akan menurun. Untuk mengatasi masalah ini, Anda dapat menjalankan operasi ANALYZE untuk memperbarui statistik database atau menggunakan hint untuk mengubah urutan join. Misalnya, menggunakan hint Leading(t2 t1) mengubah urutan menjadi t2 Join t1. Hal ini menghasilkan rencana eksekusi yang lebih efisien dan peningkatan performa.

Contoh SQL

SELECT /*+HINT Leading(t2 t1) */ t1.a FROM t1 JOIN t2 ON t1.a = t2.a;

Perbandingan rencana eksekusi

Rencana eksekusi tanpa hint

QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Gather  (cost=0.00..10.07 rows=1000 width=4)
   ->  Hash Join  (cost=0.00..10.05 rows=1000 width=4)
         Hash Cond: (t1.a = t2.a)
         ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
               Hash Key: t1.a
               ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                     ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=4)
         ->  Hash  (cost=5.01..5.01 rows=1000 width=4)
               ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
                     Hash Key: t2.a
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                           ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=4)

Rencana eksekusi dengan hint

QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Gather  (cost=0.00..10.07 rows=1000 width=4)
   ->  Hash Join  (cost=0.00..10.05 rows=1000 width=4)
         Hash Cond: (t2.a = t1.a)
         ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
               Hash Key: t2.a
               ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                     ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=4)
         ->  Hash  (cost=5.01..5.01 rows=1000 width=4)
               ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
                     Hash Key: t1.a
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                           ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=4)

Gunakan hint GUC

Dalam beberapa skenario, Anda mungkin perlu menggunakan parameter GUC untuk mencapai performa kueri yang lebih baik. Hint GUC digunakan untuk mengonfigurasi parameter GUC pada tingkat kueri. Meskipun Anda dapat mengonfigurasi parameter GUC sebelum mengeksekusi kueri untuk mencapai efek yang sama, hint GUC menyediakan cara efisien untuk mengonfigurasinya hanya untuk satu kueri. Setelah kueri selesai, pengaturan parameter GUC menjadi tidak berlaku dan tidak memengaruhi kueri berikutnya.

Contoh SQL

SELECT /*+HINT set(hg_experimental_query_batch_size 512) */t1.a FROM t1 JOIN t2 ON t1.a = t2.a;

Rencana eksekusi

QUERY PLAN
Hash Join  (cost=0.00..10.00 rows=1 width=4)
  Hash Cond: (t1.a = t2.a)
  ->  Gather  (cost=0.00..5.00 rows=1 width=4)
        ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
              ->  Seq Scan on t1  (cost=0.00..5.00 rows=1 width=4)
  ->  Hash  (cost=5.00..5.00 rows=1 width=4)
        ->  Gather  (cost=0.00..5.00 rows=1 width=4)
              ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                    ->  Seq Scan on t2  (cost=0.00..5.00 rows=1 width=4)

Menggunakan Petunjuk untuk CTE dan Subquery

Dalam skenario yang melibatkan CTE dan subquery, Anda dapat menggunakan hint untuk memengaruhi rencana eksekusinya.

Contoh SQL

WITH c1 AS (
        SELECT /*+HINT Leading(t2 t1) */ t1.a FROM (
            (
                SELECT /*+HINT leading(t2 t1) */ t1.a FROM t1 JOIN t2 ON t1.a = t2.a
            ) AS t1
            JOIN
            (
                SELECT /*+HINT NestLoop(t4 t3) */ t4.a FROM t3 JOIN t4 ON t3.a = t4.a
            ) AS t2
            ON t1.a = t2.a
        )
    ),
    c2 AS (
        SELECT /*+HINT leading(t1 t2) */ t2.a FROM (
            (
                SELECT /*+HINT Leading(t1 t2) */ t1.a FROM t1 JOIN t2 ON t1.a = t2.a
            ) AS t1
            JOIN
            (
                SELECT /*+HINT Leading(t4 t3) */ t4.a FROM t3 JOIN t4 ON t3.a = t4.a
            ) AS t2
            ON t1.a = t2.a
        )
    )
    SELECT /*+HINT NestLoop(v2 v1) */  * FROM (
        (
            SELECT /*+HINT Leading (c1 t2) */ c1.a FROM c1 JOIN t2 ON c1.a = t2.a
        ) AS v1
        JOIN
        (
            SELECT /*+HINT Leading (t1 c2) */ c2.a FROM t1 JOIN c2 ON t1.a = c2.a
        ) AS v2
        ON v1.a = v2.a
    )
    ORDER BY v2.a;

Rencana eksekusi

QUERY PLAN
Sort  (cost=0.00..10660048.36 rows=1 width=8)
  Sort Key: t4_1.a
  ->  Gather  (cost=0.00..10660048.36 rows=1 width=8)
        ->  Nested Loop  (cost=0.00..10660048.36 rows=1 width=8)
              Join Filter: ((t1.a = t4_1.a) AND (t1.a = t1_1.a) AND (t2_1.a = t1_1.a) AND (t2_1.a = t4_1.a))
              ->  Hash Join  (cost=0.00..25.01 rows=1 width=8)
                    Hash Cond: (t1_1.a = t4_1.a)
                    ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                          Hash Key: t1_1.a
                          ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                ->  Seq Scan on t1 t1_1  (cost=0.00..5.00 rows=1 width=4)
                    ->  Hash  (cost=20.00..20.00 rows=1 width=4)
                          ->  Hash Join  (cost=0.00..20.00 rows=1 width=4)
                                Hash Cond: ((t1_2.a = t4_1.a) AND (t1_2.a = t3_1.a) AND (t2_2.a = t3_1.a) AND (t2_2.a = t4_1.a))
                                ->  Hash Join  (cost=0.00..10.00 rows=1 width=8)
                                      Hash Cond: (t1_2.a = t2_2.a)
                                      ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                            Hash Key: t1_2.a
                                            ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                  ->  Seq Scan on t1 t1_2  (cost=0.00..5.00 rows=1 width=4)
                                      ->  Hash  (cost=5.00..5.00 rows=1 width=4)
                                            ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                                  Hash Key: t2_2.a
                                                  ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                        ->  Seq Scan on t2 t2_2  (cost=0.00..5.00 rows=1 width=4)
                                ->  Hash  (cost=10.00..10.00 rows=1 width=8)
                                      ->  Hash Join  (cost=0.00..10.00 rows=1 width=8)
                                            Hash Cond: (t4_1.a = t3_1.a)
                                            ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                                  Hash Key: t4_1.a
                                                  ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                        ->  Seq Scan on t4 t4_1  (cost=0.00..5.00 rows=1 width=4)
                                            ->  Hash  (cost=5.00..5.00 rows=1 width=4)
                                                  ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                                        Hash Key: t3_1.a
                                                        ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                              ->  Seq Scan on t3 t3_1  (cost=0.00..5.00 rows=1 width=4)
              ->  Materialize  (cost=0.00..10385.07 rows=40 width=8)
                    ->  Broadcast  (cost=0.00..10385.07 rows=40 width=8)
                          ->  Hash Join  (cost=0.00..10385.07 rows=1 width=8)
                                Hash Cond: (t1.a = t2_1.a)
                                ->  Hash Join  (cost=0.00..10380.07 rows=1 width=4)
                                      Hash Cond: ((t4.a = t1.a) AND (t3.a = t1.a) AND (t3.a = t2.a) AND (t4.a = t2.a))
                                      ->  Redistribution  (cost=0.00..10370.07 rows=1 width=8)
                                            Hash Key: t4.a
                                            ->  Nested Loop  (cost=0.00..10370.07 rows=1 width=8)
                                                  Join Filter: (t3.a = t4.a)
                                                  ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                        ->  Seq Scan on t3  (cost=0.00..5.00 rows=1 width=4)
                                                  ->  Materialize  (cost=0.00..5.00 rows=40 width=4)
                                                        ->  Broadcast  (cost=0.00..5.00 rows=40 width=4)
                                                              ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                                    ->  Seq Scan on t4  (cost=0.00..5.00 rows=1 width=4)
                                      ->  Hash  (cost=10.00..10.00 rows=1 width=8)
                                            ->  Hash Join  (cost=0.00..10.00 rows=1 width=8)
                                                  Hash Cond: (t2.a = t1.a)
                                                  ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                                        Hash Key: t2.a
                                                        ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                              ->  Seq Scan on t2  (cost=0.00..5.00 rows=1 width=4)
                                                  ->  Hash  (cost=5.00..5.00 rows=1 width=4)
                                                        ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                                              Hash Key: t1.a
                                                              ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                                    ->  Seq Scan on t1  (cost=0.00..5.00 rows=1 width=4)
                                ->  Hash  (cost=5.00..5.00 rows=1 width=4)
                                      ->  Redistribution  (cost=0.00..5.00 rows=1 width=4)
                                            Hash Key: t2_1.a
                                            ->  Local Gather  (cost=0.00..5.00 rows=1 width=4)
                                                  ->  Seq Scan on t2 t2_1  (cost=0.00..5.00 rows=1 width=4)

Gunakan hint INSERT

Dalam kebanyakan kasus, Anda dapat menggunakan hint dalam skenario INSERT INTO ... SELECT ketika tabel tujuan terkait dengan tabel sumber dan Anda ingin menentukan urutan join atau melakukan penyesuaian lain. Logika SQL dalam skenario INSERT INTO ... SELECT bisa kompleks, dan Anda mungkin perlu menambahkan hint berdasarkan rencana eksekusi.

Contoh 1: Hint berlaku untuk tabel tujuan pernyataan INSERT dan tabel sumber terluar dari kueri SELECT.

Saat eksekusi, jika volume data yang dihasilkan oleh operasi t1 Join t2 kecil, volume data di tabel tujuan `target` besar, dan statistik tidak diperbarui, rencana eksekusi yang dihasilkan mungkin tidak optimal. Dalam kasus ini, Anda dapat menggunakan hint untuk menyesuaikan urutan guna mencapai performa yang lebih baik.

Contoh SQL

--Hint berlaku pada tabel tujuan pernyataan INSERT dan tabel sumber terluar dari kueri SELECT.
INSERT /*+HINT Leading(target (t1 t2)) */ INTO target SELECT t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a;

Rencana eksekusi

QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Gather  (cost=0.00..26.57 rows=1000 width=8)
   ->  Insert  (cost=0.00..26.54 rows=1000 width=8)
         ->  Project  (cost=0.00..16.12 rows=1000 width=8)
               ->  Hash Right Join  (cost=0.00..15.12 rows=1000 width=12)
                     Hash Cond: (target.a = t1.a)
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                           ->  Seq Scan on target  (cost=0.00..5.00 rows=1000 width=4)
                     ->  Hash  (cost=10.07..10.07 rows=1000 width=8)
                           ->  Redistribution  (cost=0.00..10.07 rows=1000 width=8)
                                 Hash Key: t1.a
                                 ->  Hash Join  (cost=0.00..10.06 rows=1000 width=8)
                                       Hash Cond: (t1.a = t2.a)
                                       ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
                                             Hash Key: t1.a
                                             ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                                                   ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=4)
                                       ->  Hash  (cost=5.01..5.01 rows=1000 width=8)
                                             ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                                                   Hash Key: t2.a
                                                   ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                                                         ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=8)

Contoh 2: Hint berlaku untuk subquery SELECT.

Contoh SQL

Catatan

Kedua pernyataan INSERT berikut yang menggunakan hint adalah ekuivalen.

INSERT INTO target SELECT /*+HINT Leading(t2 t1) */ t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a;

INSERT /*+HINT Leading(t2 t1) */ INTO target SELECT t1.a,t2.b FROM t1 JOIN t2 ON t1.a=t2.a;

Rencana eksekusi

QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Gather  (cost=0.00..26.57 rows=1000 width=8)
   ->  Insert  (cost=0.00..26.54 rows=1000 width=8)
         ->  Project  (cost=0.00..16.12 rows=1000 width=8)
               ->  Hash Left Join  (cost=0.00..15.12 rows=1000 width=12)
                     Hash Cond: (t1.a = target.a)
                     ->  Redistribution  (cost=0.00..10.07 rows=1000 width=8)
                           Hash Key: t1.a
                           ->  Hash Join  (cost=0.00..10.06 rows=1000 width=8)
                                 Hash Cond: (t2.a = t1.a)
                                 ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                                       Hash Key: t2.a
                                       ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                                             ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=8)
                                 ->  Hash  (cost=5.01..5.01 rows=1000 width=4)
                                       ->  Redistribution  (cost=0.00..5.01 rows=1000 width=4)
                                             Hash Key: t1.a
                                             ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                                                   ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=4)
                     ->  Hash  (cost=5.00..5.00 rows=1000 width=4)
                           ->  Local Gather  (cost=0.00..5.00 rows=1000 width=4)
                                 ->  Seq Scan on target  (cost=0.00..5.00 rows=1000 width=4)

Gunakan hint UPDATE

Dalam pernyataan `UPDATE`, hint juga biasanya digunakan ketika tabel tujuan terkait dengan tabel sumber dan diperlukan penyesuaian manual.

Contoh SQL
Volume data tabel `t1` lebih besar daripada tabel `target`. Anda dapat menetapkan hint untuk menggunakan tabel `target` dalam membangun tabel hash guna menyesuaikan urutan join.
```
UPDATE /*+HINT Leading(t1 target) */ target SET b=t1.b+1 FROM t1 WHERE t1.a=target.a;
```

Perbandingan rencana eksekusi

Rencana eksekusi tanpa hint

QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Gather  (cost=0.00..52.77 rows=1000 width=1)
   ->  Update  (cost=0.00..52.76 rows=1000 width=1)
         ->  Project  (cost=0.00..11.09 rows=1000 width=32)
               ->  Hash Join  (cost=0.00..10.08 rows=1000 width=32)
                     Hash Cond: (target.a = t1.a)
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=28)
                           ->  Seq Scan on target  (cost=0.00..5.00 rows=1000 width=28)
                     ->  Hash  (cost=5.01..5.01 rows=1000 width=8)
                           ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                                 Hash Key: t1.a
                                 ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                                       ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=8)

Rencana eksekusi dengan hint

QUERY PLAN                                          
----------------------------------------------------------------------------------------------
 Gather  (cost=0.00..52.77 rows=1000 width=1)
   ->  Update  (cost=0.00..52.76 rows=1000 width=1)
         ->  Project  (cost=0.00..11.09 rows=1000 width=32)
               ->  Hash Join  (cost=0.00..10.08 rows=1000 width=32)
                     Hash Cond: (t1.a = target.a)
                     ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                           Hash Key: t1.a
                           ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                                 ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=8)
                     ->  Hash  (cost=5.00..5.00 rows=1000 width=28)
                           ->  Local Gather  (cost=0.00..5.00 rows=1000 width=28)
                                 ->  Seq Scan on target  (cost=0.00..5.00 rows=1000 width=28)

Gunakan hint runtime filter

Hologres mendukung Runtime Filter. Jika pernyataan SQL tidak memenuhi kondisi untuk memicu runtime filter, Anda dapat menggunakan hint untuk memaksa pemicuan guna meningkatkan performa kueri.

Penting

Anda hanya dapat menggunakan hint untuk memaksa pembuatan runtime filter saat melakukan operasi HashJoin.
Memaksa pembuatan runtime filter tidak selalu meningkatkan performa kueri. Evaluasi penggunaannya berdasarkan skenario spesifik Anda.

Contoh SQL

SELECT /*+HINT runtimefilter(t1 t2) */ * FROM t1 JOIN t2 ON t1.a = t2.a;

Rencana eksekusi

-- Jika runtime filter muncul dalam rencana eksekusi, berarti runtime filter telah dipicu. 
QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Gather  (cost=0.00..10.13 rows=1000 width=16)
   ->  Hash Join  (cost=0.00..10.07 rows=1000 width=16)
         Hash Cond: (t1.a = t2.a)
         Runtime Filter Cond: (t1.a = t2.a)
         ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
               Hash Key: t1.a
               ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                     ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=8)
                           Runtime Filter Target Expr: t1.a
         ->  Hash  (cost=5.01..5.01 rows=1000 width=8)
               ->  Redistribution  (cost=0.00..5.01 rows=1000 width=8)
                     Hash Key: t2.a
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                           ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=8)

Gunakan petunjuk gerakan

Contoh 1: Paksa tabel `t1` untuk di-broadcast selama operasi `JOIN`. Metode ini biasanya digunakan ketika statistik tidak akurat, yang dapat menyebabkan banyak data shuffle. Karena Hash Join hanya dapat melakukan broadcast pada tabel di sisi build, Anda harus menggunakan `Leading` untuk menentukan urutan join. Hal ini mencegah pengoptimal memilih urutan t1 JOIN t2, yang dapat terjadi jika penalti biaya broadcast terlalu tinggi akibat statistik yang hilang.

Contoh SQL

SELECT /*+HINT Leading(t2 t1) Broadcast(t1) */ * FROM t1 JOIN t2 ON t1.a = t2.a;

Rencana eksekusi

QUERY PLAN                                          
----------------------------------------------------------------------------------------------
  Gather  (cost=0.00..100000000000000005366162204393472.00 rows=1000 width=16)
   ->  Hash Join  (cost=0.00..100000000000000005366162204393472.00 rows=1000 width=16)
         Hash Cond: (t2.a = t1.a)
         ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
               ->  Seq Scan on t2  (cost=0.00..5.00 rows=1000 width=8)
         ->  Hash  (cost=100000000000000005366162204393472.00..100000000000000005366162204393472.00 rows=3000 width=8)
               ->  Broadcast  (cost=0.00..100000000000000005366162204393472.00 rows=3000 width=8)
                     ->  Local Gather  (cost=0.00..5.00 rows=1000 width=8)
                           ->  Seq Scan on t1  (cost=0.00..5.00 rows=1000 width=8)

Contoh 2: Paksa tabel `t1` untuk tidak di-broadcast. Ini berguna dalam skenario di mana statistik tabel tidak akurat. Hal ini dapat menyebabkan tabel besar diperkirakan kecil, sehingga menurunkan performa saat broadcast dilakukan pada tabel tersebut.

Data sampel

CREATE TABLE test1(a int, b int);
CREATE TABLE test2(a int, b int);

INSERT INTO test1 SELECT 1, i FROM generate_series(1, 10) AS i;
INSERT INTO test2 SELECT 1,i FROM generate_series(1, 1000000) AS i;

analyze test1,test2;

Contoh SQL

Broadcast tanpa hint

explain SELECT * FROM test1 JOIN test2 ON test1.b = test2.b;

Nonaktifkan broadcast dengan hint

explain SELECT /*+HINT NoBroadcast(test1)  */ * FROM test1 JOIN test2 ON test1.b = test2.b;

Rencana eksekusi

Broadcast tanpa hint

QUERY PLAN
---------------------------------------------------------------------------------
Gather  (cost=0.00..51.98 rows=1000000 width=16)
  ->  Hash Join  (cost=0.00..13.12 rows=1000000 width=16)
        Hash Cond: (test2.b = test1.b)
        ->  Local Gather  (cost=0.00..5.23 rows=1000000 width=8)
              ->  Seq Scan on test2  (cost=0.00..5.20 rows=1000000 width=8)
        ->  Hash  (cost=5.00..5.00 rows=200 width=8)
              ->  Broadcast  (cost=0.00..5.00 rows=200 width=8)
                    ->  Local Gather  (cost=0.00..5.00 rows=10 width=8)
                          ->  Seq Scan on test1  (cost=0.00..5.00 rows=10 width=8)

Nonaktifkan broadcast dengan hint

RENCANA QUERY
---------------------------------------------------------------------------------
Kumpulkan  (biaya=0.00..53.23 baris=1000000 lebar=16)
  ->  Gabungan Hash  (biaya=0.00..14.37 baris=1000000 lebar=16)
        Kondisi Hash: (test2.b = test1.b)
        ->  Redistribusi  (biaya=0.00..6.48 baris=1000000 lebar=8)
              Kunci Hash: test2.b
              ->  Pengumpulan Lokal  (biaya=0.00..5.23 baris=1000000 lebar=8)
                    ->  Pemindaian Seq pada test2  (biaya=0.00..5.20 baris=1000000 lebar=8)
        ->  Hash  (biaya=5.00..5.00 baris=10 lebar=8)
              ->  Redistribusi  (biaya=0.00..5.00 baris=10 lebar=8)
                    Kunci Hash: test1.b
                    ->  Pengumpulan Lokal  (biaya=0.00..5.00 baris=10 lebar=8)
                          ->  Pemindaian Seq pada test1  (biaya=0.00..5.00 baris=10 lebar=8)