BUAT TAMPILAN MATERIALISASI SEBAGAI PILIH (Transact-SQL)

Artikel
07/27/2022
8 menit untuk membaca

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 agar pengoptimal mempertimbangkan tampilan untuk substitusi. Kemampuan ini memungkinkan teknisi data untuk menerapkan tampilan materialisasi sebagai mekanisme untuk meningkatkan waktu respons kueri, tanpa harus mengubah kueri.

tautan topik Ikon 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

nama_skema

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]] ) (Saat ini dalam pratinjau) Mendistribusikan baris berdasarkan nilai hash hingga delapan kolom, memungkinkan distribusi data tabel dasar yang lebih merata, mengurangi kecondongan data dari waktu ke waktu dan meningkatkan performa kueri.

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.

Ketika 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 UPDATE 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 pembatasan 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), meskipun 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 materialisasi, 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 terwujud 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 SP_SPACEUSED dan PDW_SHOWSPACEUSED 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 Rencana Perkiraan Eksekusi grafis di SQL Server Management Studio dapat memperlihatkan apakah tampilan materialisasi dipertimbangkan oleh pengoptimal kueri untuk eksekusi kueri. dan Rencana Perkiraan Eksekusi grafis di SQL Server Management Studio dapat memperlihatkan 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:

Izin CREATE VIEW dalam database
Izin SELECT pada tabel dasar tampilan materialisasi
Izin REFERENSI pada skema yang berisi tabel dasar
IZIN ALTER pada skema yang berisi tampilan materialisasi

Contoh

J. Contoh ini memperlihatkan 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 User1.

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

Lihat juga

Langkah berikutnya

Penyetelan performa dengan Tampilan Materialisasi