BUAT TAMPILAN MATERIALISASI SEBAGAI PILIH (Transact-SQL)

  • Artikel

Berlaku untuk:Azure Synapse Analytics

Artikel ini menjelaskan pernyataan CREATE MATERIALIZED VIEW AS SELECT T-SQL di Azure Synapse Analytics untuk mengembangkan solusi. Artikel ini juga menyediakan contoh kode.

Tampilan Materialisasi mempertahankan data yang dikembalikan dari kueri definisi tampilan dan secara otomatis diperbarui saat data berubah dalam tabel yang mendasarinya. Hal ini meningkatkan performa kueri yang kompleks (biasanya kueri dengan gabungan dan agregasi) sambil menawarkan operasi pemeliharaan sederhana. Dengan kemampuan automatching rencana eksekusinya, tampilan materialisasi tidak harus dirujuk dalam kueri bagi pengoptimal untuk mempertimbangkan tampilan untuk substitusi. Kemampuan ini memungkinkan teknisi data untuk menerapkan tampilan materialisasi sebagai mekanisme untuk meningkatkan waktu respons kueri, tanpa harus mengubah kueri.

Konvensi sintaks transact-SQL

Sintaks

CREATE MATERIALIZED VIEW [ schema_name. ] materialized_view_name
    WITH (  
      <distribution_option>
    )
    AS <select_statement>
[;]

<distribution_option> ::=
    {  
        DISTRIBUTION = HASH ( distribution_column_name )  
      | DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) 
      | DISTRIBUTION = ROUND_ROBIN  
    }

<select_statement> ::=
    SELECT select_criteria

Catatan

Sintaks ini tidak didukung oleh kumpulan SQL tanpa server di Azure Synapse Analytics.

Argumen

schema_name

Adalah nama skema tempat tampilan berada.

materialized_view_name

Adalah nama tampilan. Nama tampilan harus mengikuti aturan untuk pengidentifikasi. Menentukan nama pemilik tampilan bersifat opsional.

opsi distribusi

Hanya distribusi HASH dan ROUND_ROBIN yang didukung. Untuk informasi selengkapnya tentang opsi distribusi, lihat opsi distribusi CREATE TABLE Table. Untuk rekomendasi tentang distribusi mana yang akan dipilih untuk tabel berdasarkan penggunaan aktual atau kueri sampel, lihat Distribution Advisor di Azure Synapse SQL.

DISTRIBUTION = HASH ( distribution_column_name )
Mendistribusikan baris berdasarkan nilai kolom tunggal.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) Mendistribusikan baris berdasarkan nilai hash hingga delapan kolom, memungkinkan distribusi data tampilan materialisasi yang lebih merata, mengurangi ke condong data dari waktu ke waktu dan meningkatkan performa kueri.

Catatan

  • Untuk mengaktifkan fitur Distribusi Multi-Kolom, ubah tingkat kompatibilitas database menjadi 50 dengan perintah ini. Untuk informasi selengkapnya tentang mengatur tingkat kompatibilitas database, lihat MENGUBAH KONFIGURASI CAKUPAN DATABASE. Misalnya: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
  • Untuk menonaktifkan MCD, jalankan perintah ini untuk mengubah tingkat kompatibilitas database menjadi AUTO. Misalnya: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; Tampilan materialisasi MCD yang ada akan tetap ada tetapi menjadi tidak dapat dibaca.
    • Untuk mendapatkan kembali akses ke tampilan materialisasi MCD, aktifkan fitur lagi.

select_statement

Daftar SELECT dalam definisi tampilan terwujud perlu memenuhi setidaknya satu dari dua kriteria ini:

  • Daftar SELECT berisi fungsi agregat.
  • GROUP BY digunakan dalam definisi tampilan Terwujud dan semua kolom dalam GROUP BY disertakan dalam daftar SELECT. Klausul GROUP BY dapat digunakan hingga 32 kolom.

Fungsi agregat diperlukan dalam daftar SELECT dari definisi tampilan materialisasi. Agregasi yang didukung termasuk MAX, MIN, AVG, COUNT, COUNT_BIG, SUM, VAR, STDEV.

Saat agregat MIN/MAX digunakan dalam daftar SELECT definisi tampilan materialisasi, persyaratan berikut berlaku:

  • FOR_APPEND diperlukan. Contohnya:

    CREATE MATERIALIZED VIEW mv_test2  
WITH (distribution = hash(i_category_id), FOR_APPEND)  
AS
SELECT MAX(i.i_rec_start_date) as max_i_rec_start_date, MIN(i.i_rec_end_date) as min_i_rec_end_date, i.i_item_sk, i.i_item_id, i.i_category_id
FROM syntheticworkload.item i  
GROUP BY i.i_item_sk, i.i_item_id, i.i_category_id

  • Tampilan materialisasi akan dinonaktifkan saat PEMBARUAN atau DELETE terjadi dalam tabel dasar yang dirujuk.  Pembatasan ini tidak berlaku untuk INSERT.  Untuk mengaktifkan kembali tampilan materialisasi, jalankan ALTER MATERIALIZED VIEW dengan REBUILD.

Keterangan

Tampilan materialisasi di gudang data Azure mirip dengan tampilan terindeks di SQL Server.  Ini berbagi batasan yang hampir sama dengan tampilan terindeks (lihat Membuat Tampilan Terindeks untuk detailnya) kecuali bahwa tampilan materialisasi mendukung fungsi agregat.  

Catatan

Meskipun CREATE MATERIALIZED VIEW tidak mendukung COUNT, DISTINCT, COUNT(EKSPRESI DISTINCT), atau COUNT_BIG (ekspresi DISTINCT), kueri SELECT dengan fungsi-fungsi ini masih dapat memperoleh manfaat dari tampilan materialisasi untuk performa yang lebih cepat karena pengoptimal Synapse SQL dapat secara otomatis menulis ulang agregasi tersebut dalam kueri pengguna agar sesuai dengan tampilan materialisasi yang ada. Untuk detailnya, periksa bagian contoh artikel ini.

APPROX_COUNT_DISTINCT tidak didukung dalam CREATE MATERIALIZED VIEW AS SELECT.

Hanya CLUSTERED COLUMNSTORE INDEX yang didukung oleh tampilan terwujud.

Tampilan materialisasi tidak dapat mereferensikan tampilan lain.

Tampilan materialisasi tidak dapat dibuat pada tabel dengan masking data dinamis (DDM), bahkan jika kolom DDM bukan bagian dari tampilan materialisasi. Jika kolom tabel adalah bagian dari tampilan materialisasi aktif atau tampilan materialisasi yang dinonaktifkan, DDM tidak dapat ditambahkan ke kolom ini.

Tampilan materialisasi tidak dapat dibuat pada tabel dengan keamanan tingkat baris diaktifkan.

Tampilan Materialisasi dapat dibuat pada tabel yang dipartisi.  PARTISI SPLIT/MERGE didukung pada tabel dasar tampilan terwujud, SWITCH partisi tidak didukung.

ALTER TABLE SWITCH tidak didukung pada tabel yang dirujuk dalam tampilan materialisasi. Nonaktifkan atau letakkan tampilan materialisasi sebelum menggunakan ALTER TABLE SWITCH. Dalam skenario berikut, pembuatan tampilan materialisasi mengharuskan kolom baru ditambahkan ke tampilan materialisasi:

Skenario Kolom baru untuk ditambahkan ke tampilan materialisasi Komentar
COUNT_BIG() hilang dalam daftar SELECT dari definisi tampilan materialisasi COUNT_BIG (*) Ditambahkan secara otomatis oleh pembuatan tampilan materialisasi. Tidak diperlukan tindakan pengguna.
SUM(a) ditentukan oleh pengguna dalam daftar SELECT dari definisi tampilan materialisasi DAN 'a' adalah ekspresi nullable COUNT_BIG (a) Pengguna perlu menambahkan ekspresi 'a' secara manual dalam definisi tampilan materialisasi.
AVG(a) ditentukan oleh pengguna dalam daftar SELECT dari definisi tampilan materialisasi di mana 'a' adalah ekspresi. SUM(a), COUNT_BIG(a) Ditambahkan secara otomatis oleh pembuatan tampilan materialisasi. Tidak diperlukan tindakan pengguna.
STDEV(a) ditentukan oleh pengguna dalam daftar SELECT dari definisi tampilan materialisasi di mana 'a' adalah ekspresi. SUM(a), COUNT_BIG(a), SUM(square(a)) Ditambahkan secara otomatis oleh pembuatan tampilan materialisasi. Tidak diperlukan tindakan pengguna.

Setelah dibuat, tampilan materialisasi terlihat dalam SQL Server Management Studio di bawah folder tampilan instans Azure Synapse Analytics.

Pengguna dapat menjalankan PDW_SHOWSPACEUSED SP_SPACEUSED dan DBCC untuk menentukan ruang yang digunakan oleh tampilan materialisasi. Ada juga DMV untuk menyediakan kueri yang lebih dapat disesuaikan untuk mengidentifikasi ruang dan baris yang digunakan. Untuk informasi selengkapnya, lihat Kueri ukuran tabel.

Tampilan materialisasi dapat dihilangkan melalui DROP VIEW. Anda dapat menggunakan ALTER MATERIALIZED VIEW untuk menonaktifkan atau membangun kembali tampilan materialisasi.

Tampilan materialisasi adalah mekanisme pengoptimalan kueri otomatis. Pengguna tidak perlu mengkueri tampilan materialisasi secara langsung. Saat kueri pengguna dikirimkan, mesin memeriksa izin pengguna ke objek kueri dan gagal kueri tanpa eksekusi jika pengguna tidak memiliki akses ke tabel atau tampilan reguler dalam kueri. Jika izin pengguna telah diverifikasi, pengoptimal secara otomatis menggunakan tampilan materialisasi yang cocok untuk menjalankan kueri untuk performa yang lebih cepat. Pengguna mendapatkan kembali data yang sama terlepas dari apakah kueri dilayani dengan mengkueri tabel dasar atau tampilan materialisasi.

Rencana EXPLAIN dan Perkiraan Rencana Eksekusi grafis di SQL Server Management Studio dapat menunjukkan apakah tampilan materialisasi dipertimbangkan oleh pengoptimal kueri untuk eksekusi kueri, dan Perkiraan Rencana Eksekusi grafis di SQL Server Management Studio dapat menunjukkan apakah tampilan materialisasi dipertimbangkan oleh pengoptimal kueri untuk eksekusi kueri.

Untuk mengetahui apakah pernyataan SQL dapat memperoleh manfaat dari tampilan materialisasi baru, jalankan EXPLAIN perintah dengan WITH_RECOMMENDATIONS. Untuk detailnya, lihat EXPLAIN (Transact-SQL).

Kepemilikan

  • Tampilan materialisasi tidak dapat dibuat jika pemilik tabel dasar dan tampilan materialisasi yang akan dibuat tidak sama.
  • Tampilan materialisasi dan tabel dasarnya dapat berada dalam skema yang berbeda. Ketika tampilan materialisasi dibuat, pemilik skema tampilan secara otomatis menjadi pemilik tampilan materialisasi dan kepemilikan tampilan ini tidak dapat diubah.

Izin

Pengguna memerlukan izin berikut untuk membuat tampilan materialisasi selain memenuhi persyaratan kepemilikan objek:

  1. Izin CREATE VIEW dalam database
  2. Izin SELECT pada tabel dasar tampilan materialisasi
  3. Izin REFERENSI pada skema yang berisi tabel dasar
  4. IZIN ALTER pada skema yang berisi tampilan materialisasi

Contoh

J. Contoh ini menunjukkan bagaimana pengoptimal Synapse SQL secara otomatis menggunakan tampilan materialisasi untuk menjalankan kueri untuk performa yang lebih baik bahkan ketika kueri menggunakan fungsi yang tidak didukung dalam CREATE MATERIALIZED VIEW, seperti COUNT(DISTINCT expression). Kueri yang digunakan untuk mengambil beberapa detik untuk diselesaikan sekarang selesai dalam sub-detik tanpa perubahan apa pun dalam kueri pengguna.


-- Create a table with ~536 million rows
create table t(a int not null, b int not null, c int not null) with (distribution=hash(a), clustered columnstore index);

insert into t values(1,1,1);

declare @p int =1;
while (@P < 30)
    begin
    insert into t select a+1,b+2,c+3 from t;  
    select @p +=1;
end

-- A SELECT query with COUNT_BIG (DISTINCT expression) took multiple seconds to complete and it reads data directly from the base table a. 
select a, count_big(distinct b) from t group by a;

-- Create two materialized views, not using COUNT_BIG(DISTINCT expression).
create materialized view V1 with(distribution=hash(a)) as select a, b from dbo.t group by a, b;

-- Clear all cache.

DBCC DROPCLEANBUFFERS;
DBCC freeproccache;

-- Check the estimated execution plan in SQL Server Management Studio.  It shows the SELECT query is first step (GET operator) is to read data from the materialized view V1, not from base table a.
select a, count_big(distinct b) from t group by a;

-- Now execute this SELECT query.  This time it took sub-second to complete because Synapse SQL engine automatically matches the query with materialized view V1 and uses it for faster query execution.  There was no change in the user query.

DECLARE @timerstart datetime2, @timerend datetime2;
SET @timerstart = sysdatetime();

select a, count_big(distinct b) from t group by a;

SET @timerend = sysdatetime()
select DATEDIFF(ms,@timerstart,@timerend);

B. Dalam contoh ini, User2 membuat tampilan materialisasi pada tabel yang dimiliki oleh User1. Tampilan materialisasi dimiliki oleh Pengguna1.

/****************************************************************
Setup:
SchemaX owner = DBO
SchemaX.T1 owner = User1
SchemaX.T2 owner = User1
SchemaY owner = User1
*****************************************************************/
CREATE USER User1 WITHOUT LOGIN ;
CREATE USER User2 WITHOUT LOGIN ;
GO
CREATE SCHEMA SchemaX;
GO
CREATE SCHEMA SchemaY AUTHORIZATION User1;
GO
CREATE TABLE [SchemaX].[T1] (    [vendorID] [varchar](255) Not NULL, [totalAmount] [float] Not NULL,    [puYear] [int] NULL );
CREATE TABLE [SchemaX].[T2] (    [vendorID] [varchar](255) Not NULL,    [totalAmount] [float] Not NULL,    [puYear] [int] NULL);
GO
ALTER AUTHORIZATION ON OBJECT::SchemaX.[T1] TO User1;
ALTER AUTHORIZATION ON OBJECT::SchemaX.[T2] TO User1;

/*****************************************************************************
For user2 to create a MV in SchemaY on SchemaX.T1 and SchemaX.T2, user2 needs:
1. CREATE VIEW permission in the database
2. REFERENCES permission on the schema1
3. SELECT permission on base table T1, T2  
4. ALTER permission on SchemaY
******************************************************************************/
GRANT CREATE VIEW to User2;
GRANT REFERENCES ON SCHEMA::SchemaX to User2;  
GRANT SELECT ON OBJECT::SchemaX.T1 to User2; 
GRANT SELECT ON OBJECT::SchemaX.T2 to User2;
GRANT ALTER ON SCHEMA::SchemaY to User2; 
GO
EXECUTE AS USER = 'User2';  
GO
CREATE materialized VIEW [SchemaY].MV_by_User2 with(distribution=round_robin) 
as 
        select A.vendorID, sum(A.totalamount) as S, Count_Big(*) as T 
        from [SchemaX].[T1] A
        inner join [SchemaX].[T2] B on A.vendorID = B.vendorID group by A.vendorID ;
GO
revert;
GO

Baca juga

Langkah berikutnya