CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL)

  • Articolo

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 di 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

Nota

Questa sintassi non è supportata da pool SQL serverless in Azure Synapse Analytics.

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.

Nota

  • Per abilitare la funzionalità Distribuzione a più colonne, impostare il livello di compatibilità del database su 50 con questo comando. Per altre informazioni sull'impostazione del livello di compatibilità del database, vedere ALTER DATABASE SCOPED CONFIGURATION. Ad esempio: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
  • Per disabilitare MCD, eseguire questo comando per modificare il livello di compatibilità del database su AUTO. Ad esempio: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; le viste materializzate MCD esistenti rimarranno ma diventano illeggibili.
    • Per ottenere nuovamente l'accesso alle visualizzazioni materializzate mcd, abilitare di nuovo la funzionalità.

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.  

Nota

Sebbene CREATE MATERIALIZED VIEW non supporti COUNT, DISTINCT, COUNT(espressione DISTINCT) o COUNT_BIG (espressione DISTINCT), le query SELECT con queste funzioni possono comunque trarre vantaggio dalle viste materializzate per ottenere prestazioni più veloci perché l'ottimizzatore Synapse SQL è in grado di riscrivere automaticamente le aggregazioni nella query utente in modo che corrispondano a viste materializzate esistenti. Per informazioni dettagliate, vedere la sezione di esempio di questo articolo.

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:

Scenario Nuove colonne da aggiungere alla vista materializzata Comment
COUNT_BIG() non è presente nell'elenco SELECT di una definizione di vista materializzata COUNT_BIG (*) Viene aggiunta automaticamente dalla creazione di una vista materializzata. Non è richiesta alcuna azione da parte dell'utente.
La funzione SUM(a) viene specificata dagli utenti nell'elenco SELECT della definizione di una vista materializzata e 'a' è un'espressione che ammette i valori Null COUNT_BIG (a) Gli utenti devono aggiungere l'espressione 'a' manualmente nella definizione della vista materializzata.
La funzione AVG(a) viene specificata dagli utenti nell'elenco SELECT della definizione di una vista materializzata e 'a' è un'espressione. SUM(a), COUNT_BIG(a) Viene aggiunta automaticamente dalla creazione di una vista materializzata. Non è richiesta alcuna azione da parte dell'utente.
La funzione STDEV(a) viene specificata dagli utenti nell'elenco SELECT della definizione di una vista materializzata e 'a' è un'espressione. SUM(a), COUNT_BIG(a), SUM(square(a)) Viene aggiunta automaticamente dalla creazione di una vista materializzata. Non è richiesta alcuna azione da parte dell'utente.

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

Passaggi successivi