All Products
Search

This Product

    AnalyticDB:CREATE MATERIALIZED VIEW

    Document Center

    AnalyticDB:CREATE MATERIALIZED VIEW

    Last Updated:Apr 04, 2026

    Topik ini menjelaskan pernyataan CREATE MATERIALIZED VIEW, yang digunakan untuk membuat Tampilan yang di-materialisasi dengan kebijakan refresh lengkap atau cepat serta menentukan jadwal penyegarannya.

    Sintaks

    CREATE [OR REPLACE] MATERIALIZED VIEW mv_name
[mv_definition]
[mv_properties]
[COMMENT 'view_comment']
[REFRESH {COMPLETE|FAST}]
[ON {DEMAND|OVERWRITE}]
[START WITH date] [NEXT date]
[{DISABLE|ENABLE} QUERY REWRITE]
AS 
query_body

mv_definition:
  ({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'column_comment']
  | table_constraints}
  [, ... ])
  [table_attribute]
  [partition_options]
  [index_all]
  [storage_policy]
  [block_size]
  [engine]
  [table_properties]

    Parameter

    OR REPLACE

    Opsional

    Dapat diubah setelah pembuatan: Tidak

    Parameter ini hanya didukung oleh kluster dengan versi kernel 3.1.4.7 atau lebih baru.

    • Jika tidak ada Tampilan yang di-materialisasi dengan nama yang sama, Tampilan yang di-materialisasi baru akan dibuat.

    • Jika Tampilan yang di-materialisasi dengan nama yang sama sudah ada, sistem membuat tampilan sementara, mengisi tampilan tersebut dengan data baru, lalu menggantikan Tampilan yang di-materialisasi asli.

    mv_definition

    Opsional

    Dapat diubah setelah pembuatan: Tidak

    Menentukan skema Tampilan yang di-materialisasi.

    Jika klausa ini dihilangkan, sistem menyimpulkan skema dari query_body. Secara default, sistem menentukan kunci primer, membuat indeks pada semua kolom, mengatur kebijakan penyimpanan ke hot storage, dan menggunakan engine XUANWU.

    Untuk menentukan skema Tampilan yang di-materialisasi secara manual—termasuk kunci distribusi, kunci partisi, kunci primer, indeks, dan kebijakan penyimpanan data hot/cold—gunakan sintaks yang sama seperti pernyataan CREATE TABLE. Misalnya, jika Anda tidak ingin mengindeks semua kolom, Anda dapat menggunakan kata kunci INDEX untuk menentukan kolom mana yang akan diindeks. Untuk mengurangi biaya penyimpanan, Anda juga dapat menentukan kebijakan penyimpanan campuran hot/cold atau bahkan hanya menyimpan data dari tahun terakhir.

    Aturan kunci primer

    • Refresh lengkap: Jika kunci primer tidak didefinisikan secara eksplisit, sistem secara otomatis menghasilkan kolom __adb_auto_id__ sebagai kunci primer untuk Tampilan yang di-materialisasi. Jika Anda ingin menentukan kunci primer secara eksplisit, Anda dapat menggunakan kolom apa pun dari output query_body sebagai kunci primer Tampilan yang di-materialisasi.

    • fast refresh: Kunci primer, baik yang didefinisikan secara eksplisit maupun yang dihasilkan secara otomatis, harus mengikuti aturan berikut:

      • Untuk kueri agregat bergrup (kueri agregat dengan klausa GROUP BY), kunci primer harus berupa kolom GROUP BY. Misalnya, jika Anda menggunakan GROUP BY a,b, kunci primernya harus a dan b.

      • Untuk kueri agregat tanpa grup (tanpa klausa GROUP BY), kunci primer harus berupa konstanta.

      • Untuk kueri non-agregat, kunci primer harus sama dengan kunci primer tabel dasar. Misalnya, jika kunci primer tabel dasar adalah PRIMARY KEY(sale_id,sale_date), kunci primer Tampilan yang di-materialisasi juga harus PRIMARY KEY(sale_id,sale_date).

    Rekomendasi

    Untuk kinerja kueri optimal, kami merekomendasikan menentukan kunci primer, kunci distribusi, dan kunci partisi saat membuat Tampilan yang di-materialisasi.

    mv_properties

    Opsional

    Dapat diubah setelah pembuatan: Ya (dengan menggunakan ALTER MATERIALIZED VIEW)

    Parameter ini hanya didukung oleh kluster Edisi Perusahaan, Edisi Dasar, dan Edisi Data Lakehouse dengan versi kernel 3.1.9.3 atau lebih baru.

    Menentukan kebijakan resource untuk Tampilan yang di-materialisasi. Ini mencakup mv_resource_group untuk alokasi sumber daya dan mv_refresh_hints untuk konfigurasi tugas refresh. Nilainya harus dalam format JSON. Contoh:

    MV_PROPERTIES='{
  "mv_resource_group":"<resource_group_name>",
  "mv_refresh_hints":{"<hint_name>":"<hint_value>"}
}'
    mv_resource_group

    Menentukan kelompok sumber daya yang digunakan untuk membuat dan merefresh Tampilan yang di-materialisasi. Jika tidak ditentukan, kelompok sumber daya default user_default akan digunakan.

    Nilai parameter ini dapat berupa kelompok sumber daya Interactive atau Job dari engine XIHE. Perbedaannya adalah kelompok sumber daya Job menyediakan sumber daya sesuai permintaan, yang biasanya menimbulkan latensi dalam hitungan detik atau menit. Jika Anda memiliki toleransi tinggi terhadap latensi refresh, Anda dapat menentukan kelompok sumber daya Job. Tampilan yang di-materialisasi yang menggunakan kelompok sumber daya Job juga dikenal sebagai Tampilan yang di-materialisasi elastis. Untuk meningkatkan kecepatan refresh Tampilan yang di-materialisasi elastis, Anda dapat mengonfigurasi parameter elastic_job_max_acu dalam mv_refresh_hints untuk mengubah jumlah maksimum sumber daya yang dapat digunakan oleh Tampilan yang di-materialisasi. Untuk detail penggunaan, lihat bagian Contoh Tampilan yang di-materialisasi Elastis di bawah.

    Anda dapat melihat kelompok sumber daya yang tersedia untuk kluster Anda di halaman Resource Groups di Konsol atau dengan memanggil operasi DescribeDBResourceGroup.

    Operasi gagal jika kelompok sumber daya yang ditentukan tidak ada.

    mv_refresh_hints

    Menentukan parameter konfigurasi untuk Tampilan yang di-materialisasi. Untuk daftar parameter yang didukung beserta penggunaannya, lihat Common hints.

    REFRESH [COMPLETE | FAST]

    Opsional

    Nilai default: COMPLETE

    Dapat diubah setelah pembuatan: Tidak

    Menentukan kebijakan refresh Tampilan yang di-materialisasi. Untuk informasi tentang perbedaan antara kebijakan refresh dan kasus penggunaannya, lihat Pilih kebijakan refresh.

    COMPLETE

    Refresh lengkap menjalankan kueri SQL asli untuk memindai data semua partisi target di tabel dasar dan sepenuhnya menimpa data lama dengan data hasil perhitungan baru.

    Refresh lengkap mendukung mekanisme pemicu refresh ON DEMAND [START WITH date] [NEXT date] dan ON OVERWRITE, yang memungkinkan Anda melakukan refresh manual sesuai kebutuhan, menjadwalkan refresh otomatis, atau merefresh secara otomatis ketika tabel dasar ditimpa.

    FAST
    Parameter ini didukung mulai versi 3.1.9.0. Versi 3.1.9.0 hanya mendukung fast refresh untuk Tampilan yang di-materialisasi satu tabel. Versi 3.2.0.0 dan lebih baru mendukung fast refresh untuk Tampilan yang di-materialisasi satu tabel maupun multi-tabel.

    Menjalankan fast refresh. Sistem menulis ulang kueri tampilan (query_body) untuk hanya memindai data yang berubah (dari operasi INSERT, DELETE, dan UPDATE) di tabel dasar dan menerapkan perubahan tersebut ke Tampilan yang di-materialisasi. Hal ini menghindari pemindaian seluruh tabel dasar pada setiap siklus dan mengurangi biaya komputasi setiap refresh.

    Sebelum membuat Tampilan yang di-materialisasi yang menggunakan fast refresh, Anda harus mengaktifkan fitur binary logging untuk kluster dan tabel dasar. Jika tidak, pembuatan akan gagal. Untuk petunjuknya, lihat Aktifkan fitur binary logging.

    Untuk Tampilan yang di-materialisasi yang menggunakan fast refresh, mekanisme pemicu refresh harus berupa refresh otomatis terjadwal. Anda harus menentukan waktu refresh berikutnya dengan menggunakan ON DEMAND {NEXT date}.

    Fast refresh memiliki beberapa batasan. Jika query_body tidak mendukung fast refresh, terjadi error saat Anda mencoba membuat Tampilan yang di-materialisasi.

    ON [DEMAND | OVERWRITE]

    Opsional

    Nilai default: DEMAND

    Dapat diubah setelah pembuatan: Tidak

    Menentukan mekanisme pemicu refresh Tampilan yang di-materialisasi. Untuk informasi tentang perbedaan antara mekanisme pemicu refresh dan kasus penggunaannya, lihat Pilih mekanisme pemicu refresh.

    DEMAND

    Refresh sesuai permintaan. Artinya, Anda dapat merefresh Tampilan yang di-materialisasi secara manual saat diperlukan, atau menggunakan NEXT untuk menentukan refresh otomatis terjadwal.

    Tampilan yang di-materialisasi fast refresh hanya mendukung ON DEMAND.

    OVERWRITE

    Tampilan yang di-materialisasi secara otomatis direfresh setelah data di tabel dasarnya ditimpa oleh pernyataan INSERT OVERWRITE.

    Ketika mekanisme pemicu refresh adalah ON OVERWRITE, Anda tidak dapat menentukan START WITH atau NEXT.

    [START WITH date] [NEXT date]

    Opsional

    Dapat diubah setelah pembuatan: Tidak

    Ketika mekanisme pemicu refresh Tampilan yang di-materialisasi adalah ON DEMAND, Anda dapat menentukan jadwal refresh-nya. Jika tidak ada jadwal yang ditentukan, tampilan tidak direfresh secara berkala.

    START WITH

    Waktu refresh pertama. Jika dihilangkan, tampilan direfresh segera setelah pembuatan.

    NEXT

    Waktu refresh terjadwal berikutnya.

    • Untuk Tampilan yang di-materialisasi yang menggunakan fast refresh, Anda harus menentukan NEXT. Interval refresh otomatis harus antara 5 detik (s) hingga 5 menit (min).

    • Untuk Tampilan yang di-materialisasi yang menggunakan complete refresh, NEXT bersifat opsional. Jika ditentukan, interval refresh otomatis minimum adalah 60 detik (s).

    date

    Fungsi waktu didukung. Waktu akurat hingga detik, dan milidetik dipotong.

    [DISABLE | ENABLE] QUERY REWRITE

    Opsional

    Nilai default: DISABLE

    Dapat diubah setelah pembuatan: Ya (dengan menggunakan ALTER MATERIALIZED VIEW)

    Parameter ini hanya didukung mulai versi 3.1.4.

    Mengaktifkan atau menonaktifkan penulisan ulang kueri otomatis untuk Tampilan yang di-materialisasi ini. Untuk informasi lebih lanjut, lihat Penulisan ulang kueri untuk Tampilan yang di-materialisasi.

    DISABLE

    Menonaktifkan fitur penulisan ulang kueri untuk Tampilan yang di-materialisasi saat ini.

    ENABLE

    Mengaktifkan fitur penulisan ulang kueri untuk Tampilan yang di-materialisasi saat ini. Setelah Anda mengaktifkan fitur ini, pengoptimal dapat menulis ulang seluruh atau sebagian kueri berdasarkan pola SQL-nya dan mengarahkannya ke Tampilan yang di-materialisasi. Hal ini menghindari eksekusi perhitungan asli pada tabel dasar dan meningkatkan kinerja kueri.

    query_body

    Wajib

    Dapat diubah setelah pembuatan: Tidak

    Menentukan kueri pada tabel dasar untuk Tampilan yang di-materialisasi.

    Aturan kolom SELECT

    Kolom dalam daftar SELECT harus mengikuti aturan berikut:

    • Dengan GROUP BY dan fungsi agregat: Sertakan semua kolom GROUP BY dalam daftar SELECT.

      Lihat contoh

      Contoh benar (semua kolom GROUP BY disertakan):

      CREATE MATERIALIZED VIEW demo_mv1
REFRESH FAST ON DEMAND
 NEXT now() + INTERVAL 3 minute
AS
SELECT
  sale_id,    -- kolom GROUP BY
  sale_date,  -- kolom GROUP BY
  max(quantity) AS max,  -- kolom ekspresi memerlukan alias
  sum(price) AS sum
FROM sales
GROUP BY sale_id, sale_date;

      Salah (kolom GROUP BY sale_date tidak disertakan):

      CREATE MATERIALIZED VIEW false_mv1
REFRESH FAST ON DEMAND
 NEXT now() + INTERVAL 3 minute
AS
SELECT
  sale_id,
  max(quantity) AS max,
  sum(price) AS sum
FROM sales
GROUP BY sale_id, sale_date;  -- sale_date ada di GROUP BY tetapi tidak di SELECT

    • Dengan fungsi agregat dan tanpa GROUP BY: Daftar SELECT hanya boleh berisi kolom agregat, atau hanya konstanta dan kolom agregat.

      Lihat contoh

      -- Hanya kolom agregat
CREATE MATERIALIZED VIEW demo_mv2
REFRESH FAST ON DEMAND
 NEXT now() + INTERVAL 3 minute
AS
SELECT
  max(quantity) AS max,
  sum(price) AS sum
FROM sales;

-- Konstanta ditambah kolom agregat (konstanta menjadi kunci primer)
CREATE MATERIALIZED VIEW demo_mv3
REFRESH FAST ON DEMAND
 NEXT now() + INTERVAL 3 minute
AS
SELECT
  1 AS pk,
  max(quantity) AS max,
  sum(price) AS sum
FROM sales;

    • Tanpa agregasi: Sertakan semua kolom kunci primer tabel dasar.

      Lihat contoh

      -- Kunci primer tunggal
CREATE MATERIALIZED VIEW demo_mv4
REFRESH FAST ON DEMAND
 NEXT now() + INTERVAL 3 minute
AS
SELECT
  sale_id,   -- kolom kunci primer tabel dasar sales
  quantity
FROM sales;

-- Kunci primer gabungan: PRIMARY KEY(sale_id, sale_date)
CREATE MATERIALIZED VIEW demo_mv5
REFRESH FAST ON DEMAND
 NEXT now() + INTERVAL 3 minute
AS
SELECT
  sale_id,    -- kolom kunci primer pertama
  sale_date,  -- kolom kunci primer kedua
  quantity
FROM sales1;

    • Kueri UNION ALL: Setiap cabang harus menghasilkan kolom bernama union_all_marker dengan nilai konstanta berbeda untuk setiap cabang. Sertakan semua kolom kunci primer tabel dasar. Kunci primer Tampilan yang di-materialisasi harus mencakup kolom kunci primer tabel dasar dan union_all_marker.

      CREATE MATERIALIZED VIEW demo_union_all_mv (PRIMARY KEY(id, union_all_marker))
REFRESH FAST NEXT now() + INTERVAL 5 minute
AS
SELECT customer_id AS id, "customer" AS union_all_marker
FROM customer
UNION ALL
SELECT sale_id AS id, "sales" AS union_all_marker
FROM sales;

    • Kolom ekspresi: Semua kolom ekspresi dalam daftar SELECT harus memiliki alias, misalnya SUM(price) AS total_price.

    Batasan lainnya

      Izin yang diperlukan

        Contoh

        Prasyarat

        Contoh dalam topik ini menggunakan tabel dasar yang dibuat pada bagian ini. Untuk menjalankan contoh, buat terlebih dahulu tabel dasar menggunakan pernyataan SQL berikut.

        /*+ RC_DDL_ENGINE_REWRITE_XUANWUV2=false */ -- Atur mesin tabel ke XUANWU.
CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    is_vip Boolean
);

/*+ RC_DDL_ENGINE_REWRITE_XUANWUV2=false */ -- Atur mesin tabel ke XUANWU.
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    price DECIMAL(10, 2),
    quantity INT,
    sale_date TIMESTAMP
);

        Tampilan yang di-materialisasi dengan refresh lengkap

        • Buat Tampilan yang di-materialisasi myview1 yang direfresh setiap 5 menit.

          CREATE MATERIALIZED VIEW myview1
REFRESH   -- Setara dengan REFRESH COMPLETE
 NEXT now() + INTERVAL 5 minute
AS
SELECT count(*) as cnt FROM customer;

        • Buat Tampilan yang di-materialisasi myview2 yang direfresh setiap hari pukul 02.00 pagi.

          CREATE MATERIALIZED VIEW myview2
REFRESH COMPLETE
 START WITH DATE_FORMAT(now() + INTERVAL 1 day, '%Y-%m-%d 02:00:00')
 NEXT DATE_FORMAT(now() + INTERVAL 1 day, '%Y-%m-%d 02:00:00')
AS
SELECT count(*) as cnt FROM customer;

        • Buat Tampilan yang di-materialisasi myview3 yang direfresh setiap hari Senin pukul 02.00 pagi.

          CREATE MATERIALIZED VIEW myview3
REFRESH COMPLETE ON DEMAND
 START WITH DATE_FORMAT(now() + INTERVAL 7 - weekday(now()) day, '%Y-%m-%d 02:00:00') 
 NEXT DATE_FORMAT(now() + INTERVAL 7 - weekday(now()) day, '%Y-%m-%d 02:00:00')
AS
SELECT count(*) as cnt FROM customer;

        • Buat Tampilan yang di-materialisasi myview4 yang direfresh pada pukul 02.00 pagi di hari pertama setiap bulan.

          CREATE MATERIALIZED VIEW myview4
REFRESH   -- Setara dengan REFRESH COMPLETE
 NEXT DATE_FORMAT(last_day(now()) + INTERVAL 1 day, '%Y-%m-%d 02:00:00')
AS
SELECT count(*) as cnt FROM customer;

        • Buat Tampilan yang di-materialisasi myview5 dan lakukan refresh hanya sekali.

          CREATE MATERIALIZED VIEW myview5
REFRESH   -- Setara dengan REFRESH COMPLETE
 START WITH now() + INTERVAL 1 day
AS 
SELECT count(*) as cnt FROM customer;

        • Buat Tampilan yang di-materialisasi myview6 yang tidak direfresh secara otomatis dan sepenuhnya bergantung pada refresh manual.

          CREATE MATERIALIZED VIEW myview6 (
  PRIMARY KEY (customer_id)
) DISTRIBUTED BY HASH (customer_id)
AS
SELECT customer_id FROM customer;

          Lakukan refresh Tampilan yang di-materialisasi secara manual:

          REFRESH MATERIALIZED VIEW myview6;

        • Buat Tampilan yang di-materialisasi myview7. Anda tidak perlu menentukan waktu refresh secara manual karena Tampilan yang di-materialisasi secara otomatis direfresh setelah tabel dasar ditimpa oleh operasi INSERT OVERWRITE.

          CREATE MATERIALIZED VIEW myview7
REFRESH COMPLETE ON OVERWRITE
AS
SELECT count(*) as cnt FROM customer;

        Tampilan yang di-materialisasi satu tabel dengan fast refresh

        Sebelum membuat Tampilan yang di-materialisasi yang menggunakan fast refresh, Anda harus mengaktifkan fitur binary logging untuk kluster dan tabel dasar.
        SET ADB_CONFIG BINLOG_ENABLE=true;
        ALTER TABLE customer binlog=true;
        ALTER TABLE sales binlog=true;

          Tampilan yang di-materialisasi multi-tabel dengan fast refresh

          • Buat Tampilan yang di-materialisasi multi-tabel bernama fast_mv4 dengan fast refresh dan tanpa agregasi, yang direfresh setiap 5 detik.

            CREATE MATERIALIZED VIEW fast_mv4
REFRESH FAST NEXT now() + INTERVAL 5 second
AS
SELECT 
    c.customer_id,
    c.customer_name,
    s.sale_id,
    (s.price * s.quantity) AS revenue
FROM 
    sales s
JOIN 
    customer c ON s.customer_id = c.customer_id;

          • Buat Tampilan yang di-materialisasi multi-tabel bernama fast_mv5 dengan fast refresh dan agregasi bergrup, yang direfresh setiap 10 detik.

            CREATE MATERIALIZED VIEW fast_mv5
REFRESH FAST NEXT now() + INTERVAL 10 second
AS
SELECT 
    s.sale_id,
    c.customer_name,
    COUNT(*) AS cnt,           
    SUM(s.price * s.quantity) AS revenue                
FROM 
    sales s
JOIN 
    (SELECT customer_id, customer_name FROM customer) c ON c.customer_id = s.customer_id
GROUP BY 
    s.sale_id, c.customer_name;

          Tampilan yang di-materialisasi berpartisi

          Buat Tampilan yang di-materialisasi myview8 dan tentukan kunci distribusi serta kunci partisi.

          CREATE MATERIALIZED VIEW myview8 (
  quantity INT,    -- Tampilan yang di-materialisasi mencakup semua kolom dari hasil kueri meskipun tidak secara eksplisit tercantum dalam definisi.
  price DECIMAL(10, 2),
  sale_date TIMESTAMP
) 
DISTRIBUTED BY HASH(sale_id)
PARTITION BY VALUE(date_format(sale_date, "%Y%m%d")) LIFECYCLE 30
AS 
SELECT * FROM sales;

          Menentukan kunci dan indeks

          • Buat Tampilan yang di-materialisasi myview9 dengan membuat indeks hanya pada kolom yang ditentukan customer_name, bukan pada semua kolom.

            CREATE MATERIALIZED VIEW myview9 (
  INDEX (sale_date),
  PRIMARY KEY (sale_id)
) DISTRIBUTED BY HASH (sale_id)
REFRESH
 NEXT now() + INTERVAL 1 DAY
AS
SELECT * FROM sales;

          • Buat Tampilan yang di-materialisasi bernama myview10 dengan kunci primer, kunci distribusi, indeks terklaster, indeks pada kolom tertentu, dan komentar.

            CREATE MATERIALIZED VIEW myview10 (
  quantity INT,    -- Tampilan yang di-materialisasi mencakup semua kolom dari hasil kueri meskipun tidak secara eksplisit tercantum dalam definisi.
  price DECIMAL(10, 2),
  KEY INDEX_ID(customer_id) COMMENT 'customer',
  CLUSTERED KEY INDEX(sale_id),
  PRIMARY KEY(sale_id,sale_date)
) 
DISTRIBUTED BY HASH(sale_id)
COMMENT 'MATERIALIZED VIEW c'
AS 
SELECT * FROM sales;

          Tampilan yang di-materialisasi elastis

          • Buat Tampilan yang di-materialisasi elastis myview11 menggunakan kelompok sumber daya tipe Job serverless untuk pembuatan dan refresh-nya, dengan frekuensi refresh sekali per hari.

            CREATE MATERIALIZED VIEW myview11
MV_PROPERTIES='{
  "mv_resource_group":"serverless"
}'
REFRESH COMPLETE ON DEMAND
 START WITH now()
 NEXT now() + INTERVAL 1 DAY
AS
SELECT * FROM sales;

          • Buat Tampilan yang di-materialisasi elastis myview12 yang menggunakan kelompok sumber daya tipe Job serverless untuk pembuatan dan refresh, serta dapat menggunakan 12 ACU sumber daya dari kelompok tersebut.

            CREATE MATERIALIZED VIEW myview12
MV_PROPERTIES='{
  "mv_resource_group":"serverless",
  "mv_refresh_hints":{"elastic_job_max_acu":"12"}
}'
REFRESH COMPLETE ON DEMAND
 START WITH now()
 NEXT now() + INTERVAL 1 DAY
AS
SELECT * FROM sales;

          Topik terkait