CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL)

Si applica a: Azure Synapse Analytics

Questo articolo illustra l'istruzione T-SQL CREATE MATERIALIZED VIEW AS SELECT in Azure Synapse Analytics per lo sviluppo di soluzioni. L'articolo include anche esempi di codice.

Una vista materializzata rende persistenti i dati restituiti dalla query di definizione della vista e viene aggiornata automaticamente in caso di modifiche dei dati nelle tabelle sottostanti. Migliora le prestazioni delle query complesse, in genere le query con join e aggregazioni, offrendo allo stesso tempo operazioni di manutenzione semplici. Con la funzionalità di corrispondenza automatica del piano di esecuzione, non sarà necessario fare riferimento a una vista materializzata nella query per fare in modo che Query Optimizer prenda in considerazione la vista per la sostituzione. Questa funzionalità consente ai progettisti dei dati di implementare le viste materializzate come meccanismo per migliorare il tempo di risposta delle query, senza doverle modificare.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

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

Argomenti

schema_name

Nome dello schema a cui appartiene la vista.

materialized_view_name

Nome della vista. I nomi di vista devono essere conformi alle regole per gli identificatori. Il nome del proprietario della vista è facoltativo.

Opzione di distribuzione

Sono supportate solo le distribuzioni HASH e ROUND_ROBIN. Per altre informazioni sulle opzioni di distribuzione, vedere Opzioni di distribuzione delle tabelle CREATE TABLE. Per le raccomandazioni sul tipo di distribuzione da scegliere per una tabella in base all'utilizzo effettivo o alle query di esempio, vedere Advisor distribuzione in Azure Synapse SQL.

DISTRIBUTION = HASH ( distribution_column_name )
Distribuisce le righe in base ai valori di una singola colonna.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) Distribuisce le righe in base ai valori hash di un massimo di otto colonne, consentendo una distribuzione più uniforme dei dati delle viste materializzate, riducendo l'asimmetria dei dati nel tempo e migliorando le prestazioni delle query.

select_statement

L'elenco SELECT nella definizione della vista materializzata deve soddisfare almeno uno di questi due criteri:

• L'elenco SELECT contiene una funzione di aggregazione.
• Viene usata l'istruzione GROUP BY nella definizione della vista materializzata e tutte le colonne in GROUP BY vengono incluse nell'elenco SELECT. Nella clausola GROUP BY si possono usare fino a 32 colonne.

Sono necessarie funzioni di aggregazione nell'elenco SELECT della definizione della vista materializzata. Le aggregazioni supportate includono MAX, MIN, AVG, COUNT, COUNT_BIG, SUM, VAR, STDEV.

Se si usano le aggregazioni MIN/MAX nell'elenco SELECT della definizione della vista materializzata, si applicano i requisiti seguenti:

• FOR_APPEND è obbligatorio. Ad esempio:

  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
  

• La vista materializzata verrà disabilitata quando si verifica un'operazione UPDATE o DELETE nelle tabelle di base a cui viene fatto riferimento.  Questa restrizione non si applica alle operazioni INSERT.  Per abilitare nuovamente la vista materializzata, eseguire ALTER MATERIALIZED VIEW con REBUILD.

Osservazioni:

Una vista materializzata nel data warehouse di Azure è simile a una vista indicizzata in SQL Server.  Condivide quasi le stesse restrizioni della vista indicizzata (vedere Creare viste indicizzate per informazioni dettagliate) ad eccezione del fatto che una vista materializzata supporta le funzioni di aggregazione.  

La funzione APPROX_COUNT_DISTINCT non è supportata in CREATE MATERIALIZED VIEW AS SELECT.

La vista materializzata supporta solo CLUSTERED COLUMNSTORE INDEX.

Una vista materializzata non può fare riferimento ad altre viste.

Non è possibile creare una vista materializzata in una tabella con Maschera dati dinamica (DDM), anche se la colonna DDM non fa parte della vista materializzata. Se una colonna della tabella fa parte di una vista materializzata attiva o una vista materializzata disabilitata, non è possibile aggiungere DDM a questa colonna.

Non è possibile creare una vista materializzata in una tabella con sicurezza a livello di riga abilitata.

È possibile creare viste materializzate su tabelle partizionate.  Le operazioni SPLIT/MERGE sulle partizioni sono supportate per le tabelle di base delle viste materializzate. L'operazione SWITCH per una partizione non è supportata.

Le operazioni ALTER TABLE SWITCH non sono supportate sulle tabelle a cui fanno riferimento le viste materializzate. Disabilitare o eliminare le viste materializzate prima di usare ALTER TABLE SWITCH. Negli scenari seguenti la creazione della vista materializzata richiede l'aggiunta di nuove colonne alla vista materializzata:

Dopo la creazione, le viste materializzate sono visibili all'interno di SQL Server Management Studio nella cartella views dell'istanza di Azure Synapse Analytics.

Gli utenti possono eseguire SP_SPACEUSED e DBCC PDW_SHOWSPACEUSED per determinare lo spazio usato da una vista materializzata. Sono inoltre disponibili DMV per fornire query più personalizzabili per identificare lo spazio e le righe utilizzate. Per altre informazioni, vedere Query di dimensioni della tabella.

È possibile eliminare una vista materializzata tramite DROP VIEW. È possibile usare ALTER MATERIALIZED VIEW per disabilitare o ricostruire una vista materializzata.

La vista materializzata è un meccanismo di ottimizzazione automatica delle query. Gli utenti non devono eseguire direttamente una query su una vista materializzata. Quando viene inviata una query utente, il motore controlla le autorizzazioni dell'utente per gli oggetti query e restituisce un errore della query senza eseguirla se l'utente non ha accesso alle tabelle o alle viste normali nella query. Se l'autorizzazione dell'utente è stata verificata, l'utilità di ottimizzazione usa automaticamente una vista materializzata corrispondente per eseguire la query con prestazioni di velocità maggiori. Gli utenti ottengono gli stessi dati indipendentemente dal fatto che la query venga eseguita sulle tabelle di base o sulla vista materializzata.

Il piano EXPLAIN e il piano di esecuzione stimato grafico in SQL Server Management Studio possono indicare se una vista materializzata viene considerata da Query Optimizer per l'esecuzione di query e il piano di esecuzione grafico stimato in SQL Server Management Studio può indicare se una vista materializzata viene considerata da Query Optimizer per l'esecuzione di query.

Per scoprire se un'istruzione SQL può trarre vantaggio da una nuova vista materializzata, eseguire il comando EXPLAIN con WITH_RECOMMENDATIONS. Per informazioni dettagliate, vedere EXPLAIN (Transact-SQL).

Proprietà

• Non è possibile creare una vista materializzata se i proprietari delle tabelle di base e la vista materializzata da creare non sono uguali.
• Una vista materializzata e le relative tabelle di base possono risiedere in schemi diversi. Quando viene creata la vista materializzata, il proprietario dello schema della vista diventa automaticamente il proprietario della vista materializzata e questa proprietà della vista non può essere modificata.

Autorizzazioni

Un utente deve disporre delle autorizzazioni seguenti per creare una vista materializzata oltre a soddisfare i requisiti di proprietà dell'oggetto:

1. Autorizzazione CREATE VIEW nel database
2. Autorizzazione SELECT per le tabelle di base della vista materializzata
3. Autorizzazione REFERENCES per lo schema contenente le tabelle di base
4. Autorizzazione ALTER per lo schema contenente la vista materializzata

Esempio

R. Questo esempio illustra come l'ottimizzatore di Synapse SQL usa automaticamente le viste materializzate per eseguire una query per ottenere prestazioni migliori anche quando la query usa funzioni non supportate in CREATE MATERIALIZED VIEW, come COUNT(DISTINCT expression). Una query che richiedeva vari secondi per il completamento viene ora completata in meno di un secondo senza alcuna modifica.

-- 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. In questo esempio User2 crea una vista materializzata nelle tabelle di proprietà di User1. La vista materializzata è di proprietà di 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

Vedi anche

• ALTER MATERIALIZED VIEW (Transact-SQL)
• DROP VIEW
• EXPLAIN (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)
• Viste del catalogo di Azure Synapse Analytics e Piattaforma di strumenti analitici (PDW)
• Viste di sistema supportate in Azure Synapse Analytics
• Istruzioni T-SQL supportate in Azure Synapse Analytics

Passaggi successivi

• Ottimizzazione delle prestazioni con vista materializzata