CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL)

  • 發行項

適用於：Azure Synapse Analytics

此文章說明 Azure Synapse Analytics 中用於開發解決方案的 CREATE MATERIALIZED VIEW AS SELECT T-SQL 陳述式。 此文章也提供程式碼範例。

具體化檢視會保存從檢視定義查詢傳回的資料，並在底層資料表中的資料變更時自動取得更新。 它可以改進複雜查詢 (通常是具有聯結與會總的查詢) 的效能，同時提供簡單的維護作業。 使用其執行計畫的自動比對功能，不需要在查詢中參考具體化檢視，最佳化工具就能考慮要替代的檢視。 這項功能可讓資料工程師將具體化檢視實作為改善查詢回應時間的機制，而不需要變更查詢。

Transact-SQL 語法慣例

Syntax

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

注意

Azure Synapse Analytics 的無伺服器 SQL 集區不支援此語法。

引數

schema_name

這是檢視所屬的結構描述名稱。

materialized_view_name

這是檢視的名稱。 檢視名稱必須遵照識別碼的規則。 指定檢視擁有者名稱則是選擇性的。

分佈選項

只支援 HASH 與 ROUND_ROBIN 分佈。 如需散發選項的詳細資訊，請參閱 CREATE TABLE 資料表散發選項。 如需根據實際使用量或範例查詢為資料表選擇散發的建議，請參閱 Azure Synapse SQL 中的散發建議程式。

DISTRIBUTION = HASH ( distribution_column_name )
根據單一資料行的值散發資料列。

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) 根據最多八個數據行的雜湊值來散發資料列，以便更平均地散發具體化檢視資料，減少一段時間的資料扭曲並改善查詢效能。

注意

  • 若要啟用多資料行散發功能，請使用此命令將資料庫的相容性層級變更為 50。 如需設定資料庫相容性層級的詳細資訊，請參閱 ALTER DATABASE SCOPED CONFIGURATION。 例如：ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
  • 若要停用 MCD，請執行此命令，將資料庫的相容性層級變更為 AUTO。 例如： ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; 現有的 MCD 具體化檢視會保留，但變成無法讀取。
    • 若要重新取得 MCD 具體化檢視的存取權，請再次啟用此功能。

select_statement

具體化檢視定義中的 SELECT 清單必須符合下列兩個條件的至少其中一個：

  • SELECT 清單包含彙總函式。
  • GROUP BY 用於具體化檢視定義中，而且 GROUP BY 中的所有資料行都包括在 SELECT 清單中。 GROUP BY 子句中最多可以使用 32 個資料行。

具體化檢視定義的 SELECT 清單中需要彙總函式。 支援的彙總包括 MAX、MIN、AVG、COUNT、COUNT_BIG、SUM、VAR、STDEV。

當在具體化檢視定義的 SELECT 清單中使用 MIN/MAX 彙總時，必須符合下列需求：

  • FOR_APPEND 是必要的。 例如：

    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

  • 當參考的基底資料表中發生 UPDATE 或 DELETE 時，將停用具體化檢視。  此限制不適用於 INSERT。  若要重新啟用具體化檢視，請搭配 REBUILD 執行 ALTER MATERIALIZED VIEW。

備註

Azure 資料倉儲中具體化檢視類似 SQL Server 中的索引檢視表。  它的限制幾乎與索引檢視表相同 (請參閱建立索引檢視表以取得詳資訊)，不過具體化檢視支援彙總函式。  

注意

雖然 CREATE MATERIALIZED VIEW 不支援 COUNT、DISTINCT、COUNT(DISTINCT 運算式) 或 COUNT_BIG (DISTINCT 運算式)，但使用這些函數的 SELECT 查詢仍可因具體化檢視獲得更快效能，因為 Synapse SQL 最佳化工具可在使用者查詢中自動重新寫入這些彙總，以符合現有的具體化檢視。 如需詳細資料，請參閱本文中的範例一節。

CREATE MATERIALIZED VIEW AS SELECT 中不支援 APPROX_COUNT_DISTINCT。

只有具體化檢視才支援 CLUSTERED COLUMNSTORE INDEX。

具體化檢視無法參考其他檢視。

您無法在具有動態資料遮罩 (DDM) 的資料表上建立具體化檢視，即使 DDM 資料行不是具體化檢視的一部分亦是如此。 如果資料表資料行是使用中具體化檢視或已停用具體化檢視的一部分，則無法將 DDM 新增至此資料行。

無法在已啟用資料列層級安全性的資料表上建立具體化檢視。

具體化檢視可在資料分割資料表上建立。  具體化檢視的基底資料表支援分割區 SPLIT/MERGE，不支援分割區 SWITCH。

具體化檢視中參考的資料表上不支援 ALTER TABLE SWITCH。 在使用 ALTER TABLE SWITCH 之前停用或捨棄具體化檢視。 在下列案例中，具體化檢視建立要求必須將新資料行新增到具體化檢視：

狀況 新資料行必須新增到具體化檢視 註解
在具體化檢視定義的 SELECT 清單中，遺漏 COUNT_BIG() COUNT_BIG (*) 已由具體化檢視建立自動新增。 使用者不必採取任何動作。
SUM(a) 是由使用者在具體化檢視定義的 SELECT 清單中所指定，且 'a' 是可為 Null 的運算式 COUNT_BIG (a) 使用者必須手動在具體化檢視定義中新增運算式 'a'。
AVG(a) 是由使用者在具體化檢視定義的 SELECT 清單中所指定，其中 'a' 是運算式。 SUM(a)、COUNT_BIG(a) 已由具體化檢視建立自動新增。 使用者不必採取任何動作。
STDEV(a) 是由使用者在具體化檢視定義的 SELECT 清單中所指定，其中 'a' 是運算式。 SUM(a)、COUNT_BIG(a)、SUM(square(a)) 已由具體化檢視建立自動新增。 使用者不必採取任何動作。

一旦建立，具體化檢視在 SQL Server Management Studio 內 Azure Synapse Analytics 執行個體的檢視資料夾下就是可見的。

使用者可以執行 SP_SPACEUSED 與 DBCC PDW_SHOWSPACEUSED 來判斷具體化檢視取用的空間。 另外還有 DMV，可提供更多可自訂的查詢，以識別所耗用的空間和資料列。 如需詳細資訊，請參閱資料表大小查詢。

具體化檢視可透過 DROP VIEW 來捨棄。 您可以使用 ALTER MATERIALIZED VIEW 來停用或重建具體化檢視。

具體化檢視是自動查詢最佳化機制。 使用者不需要直接查詢具體化檢視。 提交使用者查詢時，引擎會檢查使用者對查詢物件的權限，如果使用者沒有查詢中資料表或一般檢視的存取權，查詢就會失敗且不會執行。 如果使用者的權限已經過驗證，最佳化工具會自動使用相符具體化檢視來執行查詢，以加快效能。 無論是透過查詢基底資料表或具體化檢視來為查詢提供服務，使用者都會取得相同的資料。

EXPLAIN 計畫和 SQL Server Management Studio 中的圖形化估計執行計畫可以顯示查詢執行查詢最佳化工具是否考慮具體化檢視，而 SQL Server Management Studio 中的圖形化估計執行計畫可以顯示查詢執行查詢最佳化工具是否考慮具體化檢視。

若要判斷 SQL 陳述式是否可從新的具體化檢視獲益，請搭配 WITH_RECOMMENDATIONS 執行 EXPLAIN 命令。 如需詳細資訊，請參閱 EXPLAIN (Transact-SQL)。

擁有權

  • 如果基底資料表的擁有者和要建立具體化檢視的擁有者不相同，便無法建立具體化檢視。
  • 具體化檢視及其基底資料表可以位於不同的結構描述中。 建立具體化檢視時，檢視的結構描述擁有者會自動成為具體化檢視的擁有者，而且無法變更此檢視擁有權。

權限

除了符合物件擁有權需求之外，使用者還需要下列權限才能建立具體化檢視：

  1. 資料庫中的 CREATE VIEW 權限
  2. 具體化檢視的基底資料表 SELECT 權限
  3. 結構描述 (包含基底資料表) 的 REFERENCES 權限
  4. 結構描述 (包含具體化檢視) 的 ALTER 權限

範例

A. 此範例示範儘管查詢使用 CREATE MATERIALIZED VIEW 中不支援的函數 (例如 COUNT(DISTINCT expression))，Synapse SQL 最佳化工具也能自動使用具體化檢視來執行查詢，以提高效能。 原本需要數秒鐘才能完成的查詢，現在只需要不到一秒即可完成，且無須進行任何使用者查詢變更。


-- 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. 在此範例中，User2 會在 User1 所擁有的資料表上建立具體化檢視。 具體化檢視是由 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

另請參閱

下一步