SELECT OLARAK MADDELEŞTIRILMIŞ GÖRÜNÜM YARAT (Transact-SQL)

Şunlar için geçerlidir: Azure Synapse Analytics

Bu makale, Azure Synapse Analytics'te çözüm geliştirmek için CREATE MATERIALIZED VIEW AS SELECT T-SQL ifadesini açıklamaktadır. Makale ayrıca kod örnekleri de vermektedir.

Bir Maddeleştirilmiş Görünüm, görünüm tanımı sorgusundan geri dönen verileri sürdürür ve alttaki tablolarda veri değiştikçe otomatik olarak güncellenir. Karmaşık sorguların (genellikle birleştirme ve toplama içeren sorgular) performansını artırırken, basit bakım işlemleri sunar. Yürütme planı otomatik eşleme yeteneği sayesinde, optimizatorun bu görünümü yerine getirmek için sorguda referans bulunması gerekmez. Bu yetenek, veri mühendislerinin sorguları değiştirmek zorunda kalmadan, sorgu yanıt süresini iyileştirmek için bir mekanizma olarak somutlaştırılmış görünümleri uygulamalarını sağlar.

Transact-SQL söz dizimi kuralları

Sözdizimi

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

Uyarı

Bu söz dizimi, Azure Synapse Analytics'teki sunucusuz SQL havuzu tarafından desteklenmez.

Arguments

schema_name

Görünümün ait olduğu şemanın adıdır.

materialized_view_name

Görünümün adıdır. Görünüm adları, tanımlayıcılar için kurallara uymalıdır. Görünüm sahibi adını belirtmek isteğe bağlıdır.

Dağıtım seçeneği

Sadece HASH ve ROUND_ROBIN dağıtımları desteklenmektedir. Dağıtım seçenekleri hakkında daha fazla bilgi için bkz. TABLO OLUŞTUR Tablo dağıtım seçenekleri. Gerçek kullanım veya örnek sorgulara dayalı olarak bir tablo için hangi dağıtımı seçeceğinize dair öneriler için Azure Synapse SQL'deki Distribution Advisor'a bakınız.

DISTRIBUTION = HASH ( distribution_column_name )
Tek bir sütunun değerlerine göre satırlar dağıtılır.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) Sekiz sütuna kadar hash değerlerine göre satırlar dağıtılır, bu da maddeleştirilmiş görünüm verilerinin daha eşit dağılımı sağlar, zaman içinde veri çarpıklığını azaltır ve sorgu performansını artırır.

Uyarı

• Çok Sütunlu Dağıtım özelliğini etkinleştirmek için, bu komutla veritabanının uyumluluk seviyesini 50'ye değiştirin. Veritabanı uyumluluk seviyesinin ayarlanması hakkında daha fazla bilgi için bkz. ALTER DATABASE SCOPED CONFIGURATION'A BAKıNıZ. Örneğin: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
• MCD'yi devre dışı bırakmak için, veritabanının uyumluluk seviyesini AUTO olarak değiştirmek için bu komutu çalıştırın. Örneğin: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; Mevcut MCD materyalize edilmiş görünümler kalır ama okunamaz hale gelir.
  • MCD görüntülerine yeniden erişim sağlamak için özelliği tekrar etkinleştirin.

select_statement

Maddeleştirilmiş görünüm tanımındaki SELECT listesinin en az şu iki kriterden birini karşılaması gerekir:

• SELECT listesi bir toplu fonksiyon içerir.
• GROUP BY, Maddeleştirilmiş görünüm tanımında kullanılır ve GROUP BY'deki tüm sütunlar SELECT listesinde yer alır. GROUP BY maddesinde en fazla 32 sütun kullanılabilir.

Maddeleştirilmiş görünüm tanımının SELECT listesinde toplu fonksiyonlar gereklidir. Desteklenen toplamalar arasında MAX, MIN, AVG, COUNT, COUNT_BIG, SUM, VAR, STDEV YER ALIR.

MIN/MAX agregaları SELECT maddeleştirilmiş görünüm tanımı listesinde kullanıldığında, aşağıdaki gereksinimler geçerlidir:

• FOR_APPEND gereklidir. Örneğin:

  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
  

• Maddeleştirilmiş görünüm, referans edilen temel tablolarda UPDATE veya DELETE gerçekleştiğinde devre dışı bırakılır.  Bu kısıtlama INSERT'ler için geçerli değildir.  Maddeleştirilmiş görünümü yeniden etkinleştirmek için REBUILD ile ALTER MATERIALIZED VIEW çalıştırın.

Açıklamalar

Azure veri deposunda maddeleştirilmiş bir görünüm, SQL Server'daki indekslenmiş görünüme benzer.  Indekslenmiş görünümle neredeyse aynı kısıtlamaları paylaşır (ayrıntılar için Indeksli Görünümler Oluştur bölümüne bakınız), ancak maddeleştirilmiş görünüm toplu fonksiyonları destekler.  

Uyarı

CREATE MATERIALIZED VIEW COUNT, DISTINCT, COUNT(DISTINCT ifadesi) veya COUNT_BIG (DISTINCT ifadesi) desteklemese de, bu fonksiyonlara sahip SELECT sorguları, Synapse SQL optimizatıcısının kullanıcı sorgusunda bu toplamaları mevcut materyalize edilmiş görünümlerle uyumlu olarak otomatik olarak yeniden yazabildiği için daha hızlı performans için SELECT sorgularından fayda sağlayabilir. Detaylar için bu makalenin örnek bölümüne göz atın.

APPROX_COUNT_DISTINCT, CREATE MATERIALIZED VIEW AS SELECT'te desteklenmez.

Yalnızca CLUSTERED COLUMNSTORE INDEX materyalize edilmiş görünümle desteklenir.

Maddeleşmiş bir görünüm diğer görüşlere referans veremez.

Dinamik veri maskelemesi (DDM) olan bir tabloda maddeleştirilmiş bir görünüm, DDM sütunu maddeleştirilmiş görünümün parçası olmasa bile oluşturulamaz. Bir tablo sütunu aktif maddeleştirilmiş görünümün parçası veya devre dışı bırakılmış maddeleştirilmiş görünümün parçasa DDM bu sütuna eklenemez.

Satır seviyesinde güvenlik etkinleştirilmiş bir tabloda bir tabloda maddeleştirilmiş bir görünüm oluşturulamaz.

Maddeleştirilmiş Görünümler bölümlenmiş tablolar üzerinde oluşturulabilir.  Partition SPLIT/MERGE materyalize görünüm temel tablolarında destekleniyor, partition SWITCH desteklenmiyor.

ALTER TABLE SWITCH, materyalize edilmiş görünümlerde referans verilen tablolarda desteklenmez. ALTER TABLE SWITCH'i kullanmadan önce maddeleştirilmiş görünümleri devre dışı bırakın veya kaldırın. Aşağıdaki senaryolarda, maddeleştirilmiş görünüm oluşturma için yeni sütunların maddeleştirilmiş görünüme eklenmesi gerekir:

Scenario	Maddeleştirilmiş görünüme eklenecek yeni sütunlar	Comment
COUNT_BIG() maddeleştirilmiş görünüm tanımının SELECT listesinde eksiktir	COUNT_BIG (*)	Maddeleştirilmiş görünüm oluşturulmasıyla otomatik olarak ekleniyor. Kullanıcı müdahalesi gerekli değildir.
SUM(a), kullanıcılar tarafından maddeleştirilmiş görünüm tanımının SELECT listesinde belirtilir VE 'a' nullable bir ifadedir	COUNT_BIG (a)	Kullanıcılar, maddeleştirilmiş görünüm tanımında 'a' ifadesini manuel olarak eklemeleri gerekir.
AVG(a), kullanıcılar tarafından maddeleştirilmiş görünüm tanımının SELECT listesinde belirtilir; burada 'a' bir ifadedir.	SUM(a), COUNT_BIG(a)	Maddeleştirilmiş görünüm oluşturulmasıyla otomatik olarak ekleniyor. Kullanıcı müdahalesi gerekli değildir.
STDEV(a), kullanıcılar tarafından maddeleştirilmiş görünüm tanımının SELECT listesinde belirtilir; burada 'a' bir ifadedir.	SUM(a), COUNT_BIG(a), SUM(kare(a))	Maddeleştirilmiş görünüm oluşturulmasıyla otomatik olarak ekleniyor. Kullanıcı müdahalesi gerekli değildir.

Oluşturulduktan sonra, gerçekleşmiş görünümler SQL Server Management Studio içinde Azure Synapse Analytics örneğinin görünümler klasörü altında görünür.

Kullanıcılar, maddeleştirilmiş bir görünümün ne kadar alanı tükettiğini belirlemek için SP_SPACEUSED ve DBCC PDW_SHOWSPACEUSED çalıştırabilirler. Ayrıca, kullanılan alan ve satır sayısını belirlemek için daha özelleştirilebilir sorgular sunan DMV'ler de mevcuttur. Daha fazla bilgi için bkz. Tablo boyutu sorguları.

Maddeleştirilmiş bir görünüm, DROP VIEW ile bırakılabilir. ALTER MATERIALIZED VIEW kullanarak maddeleştirilmiş görünümü devre dışı bırakabilir veya yeniden oluşturabilirsiniz.

Maddeleştirilmiş görünüm, otomatik bir sorgu optimizasyon mekanizmasıdır. Kullanıcıların doğrudan maddeleştirilmiş bir görünümü sorgulamasına gerek yoktur. Bir kullanıcı sorgusu gönderildiğinde, motor kullanıcının sorgu nesnelerine olan izinlerini kontrol eder ve kullanıcı tablolara veya sorgudaki normal görünümlere erişimi yoksa sorgu yürütülmeden başarısız olur. Kullanıcının izni doğrulandıysa, optimizer otomatik olarak eşleşen bir maddeleştirilmiş görünüm kullanarak sorguyu daha hızlı performans için yürütür. Kullanıcılar, sorgu temel tablolar sorgulaması veya maddeleştirilmiş görünüm ile hizmet edilse de aynı veriyi geri alır.

EXPLAIN planı ve SQL Server Management Studio'daki grafiksel Tahmini Yürütme Planı, sorgu optimizatoru tarafından sorgu yürütülmesi için maddeleştirilmiş bir görünümün dikkate alınıp alınmadığını gösterebilir; SQL Server Management Studio'daki grafiksel Tahmini Yürütme Planı, sorgu optimizatoru tarafından sorgu yürütülmesi için maddeleştirilmiş bir görünümün dikkate alınıp alınmadığını gösterebilir.

Bir SQL ifadesinin yeni bir maddeleştirilmiş görünümden fayda sağlayıp sağlamayacağını öğrenmek için, komutu EXPLAIN çalıştırın.WITH_RECOMMENDATIONS Detaylar için bkz. AÇIKLAMA (Transact-SQL).

Sahiplik

• Temel tabloların sahipleri ile -oluşturulan to-bemaddeleştirilmiş görünüm aynı değilse, maddeleştirilmiş görünüm oluşturulamaz.
• Maddeleştirilmiş bir görünüm ve temel tabloları farklı şemalarda bulunabilir. Maddeleştirilmiş görünüm oluşturulduğunda, görüntünün şema sahibi otomatik olarak maddeleştirilmiş görünümün sahibi olur ve bu görünüm sahipliği değiştirilemez.

Permissions

Bir kullanıcının, nesne sahiplik gereksinimlerini karşılamasının yanı sıra maddeleştirilmiş bir görünüm oluşturmak için aşağıdaki izinlere sahip olması gerekir:

1. Veritabanında GÖRÜNTÜLEME izni oluştur
2. Maddeleştirilmiş görünümün temel tablolarında SELECT izni
3. Temel tabloları içeren şema için REFERENCES izni
4. Maddeleştirilmiş görünümü içeren şema üzerinde ALTER izni

Example

A. Bu örnek, Synapse SQL optimizer'ın, sorgu CREATE MATERIALIZED VIEW'da COUNT(DISTINCT expression)desteklenmeyen fonksiyonları kullansa bile, örneğin daha iyi performans için otomatik olarak bir sorgu yürütmek için nasıl kullanıldığını göstermektedir. Eskiden tamamlanması birkaç saniye süren bir sorgu, şimdi kullanıcı sorgusunda herhangi bir değişiklik olmadan saniyenin altında tamamlanıyor.

-- 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. Bu örnekte, User2, User1'e ait tablolarda maddeleştirilmiş bir görünüm oluşturur. Maddeleştirilmiş görünüm User1'e aittir.

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

Ayrıca bakınız

• MADDI GÖRÜNÜMÜ DEĞIŞTIR (Transact-SQL)
• AÇIK GÖRÜNÜM
• AÇIKLAMA (Transact-SQL)
• sys.pdw_materialized_view_column_distribution_properties (Transact-SQL)
• sys.pdw_materialized_view_distribution_properties (Transact-SQL)
• sys.pdw_materialized_view_mappings (Transact-SQL)
• DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD (Transact-SQL)
• Azure Synapse Analytics and Analytics Platform System (PDW) Catalog Views
• System views supported in Azure Azure Synapse Analytics
• T-SQL statements supported in Azure Azure Synapse Analytics

Sonraki Adımlar

• Maddeleştirilmiş Görünüm ile performans ayarı