JELASKAN (Transact-SQL)
Berlaku untuk:
Azure Synapse Analytics (khusus kumpulan SQL khusus)
Mengembalikan rencana kueri untuk pernyataan SQL Azure Synapse Analytics tanpa menjalankan pernyataan . Gunakan EXPLAIN untuk mempratinjau operasi mana yang memerlukan pergerakan data dan untuk melihat perkiraan biaya operasi kueri. WITH RECOMMENDATIONSberlaku untuk Azure Synapse Analytics.
Sintaks
EXPLAIN [WITH_RECOMMENDATIONS] SQL_statement
[;]
Catatan
Sintaks ini tidak didukung oleh kumpulan SQL tanpa server di Azure Synapse Analytics.
Argumen
SQL_statement
Pernyataan SQL tempat EXPLAIN akan berjalan. SQL_statement dapat berupa salah satu perintah ini: SELECT, , INSERT, UPDATEDELETE, CREATE TABLE AS SELECT, CREATE REMOTE TABLE.
WITH_RECOMMENDATIONS
Kembalikan rencana kueri dengan rekomendasi untuk mengoptimalkan performa pernyataan SQL.
Izin
Memerlukan izin SHOWPLAN , dan izin untuk menjalankan SQL_statement. Lihat Izin: GRANT, DENY, REVOKE (Azure Synapse Analytics, Parallel Data Warehouse).
Tampilkan Nilai
Nilai yang dikembalikan dari perintah EXPLAIN adalah dokumen XML. Dokumen XML ini mencantumkan semua operasi dalam rencana kueri untuk kueri yang diberikan, masing-masing diapit oleh <dsql_operation> tag. Nilai yang dikembalikan berjenis nvarchar(max).
Rencana kueri yang dikembalikan menggambarkan pernyataan SQL berurutan; saat kueri berjalan, kueri mungkin melibatkan operasi paralel, sehingga beberapa pernyataan berurutan yang ditampilkan dapat berjalan pada saat yang sama.
Struktur XML nilai yang dikembalikan terlihat seperti ini:
\<?xml version="1.0" encoding="utf-8"?>
<dsql_query>
<sql>. . .</sql>
<params />
<dsql_operations>
<dsql_operation>
. . .
</dsql_operation>
[ . . . n ]
<dsql_operations>
</dsql_query>
Tag XML berisi informasi ini:
| XML Tag | Ringkasan, Atribut, dan Konten |
|---|---|
| <dsql_query> | Elemen tingkat atas/dokumen. |
| <Sql> | Menggemakan SQL_statement. |
| <params> | Tag ini tidak digunakan saat ini. |
| <> materialized_view_candidates (pratinjau) | Berisi pernyataan CREATE dari tampilan materialisasi yang direkomendasikan untuk performa pernyataan SQL yang lebih baik. |
| <dsql_operations> | Meringkas dan berisi langkah-langkah kueri, dan menyertakan informasi biaya untuk kueri. Juga berisi semua <dsql_operation> blok. Tag ini berisi informasi hitungan untuk seluruh kueri:<dsql_operations total_cost=total_cost total_number_operations=total_number_operations>total_cost adalah perkiraan total waktu kueri untuk dijalankan, dalam md. total_number_operations adalah jumlah total operasi untuk kueri. Operasi yang akan diparalelkan dan dijalankan pada beberapa simpul dihitung sebagai satu operasi. |
| <dsql_operation> | Menjelaskan satu operasi dalam rencana kueri. Tag <dsql_operation> berisi jenis operasi sebagai atribut:<dsql_operation operation_type=operation_type>operation_type adalah salah satu nilai yang ditemukan dalam sys.dm_pdw_request_steps (Transact-SQL). Konten dalam \<dsql_operation> blok tergantung pada jenis operasi.Lihat tabel berikut. |
| Jenis Operasi | Konten | Contoh |
|---|---|---|
| BROADCAST_MOVE, DISTRIBUTE_REPLICATED_TABLE_MOVE, MASTER_TABLE_MOVE, PARTITION_MOVE, SHUFFLE_MOVE, dan TRIM_MOVE | <operation_cost> elemen , dengan atribut ini. Nilai hanya mencerminkan operasi lokal:- biaya adalah biaya operator lokal dan menunjukkan perkiraan waktu operasi berjalan, dalam ms. - accumulative_cost adalah jumlah semua operasi yang terlihat dalam rencana termasuk nilai yang dijumlahkan untuk operasi paralel, dalam ms. - average_rowsize adalah perkiraan ukuran baris rata-rata (dalam byte) baris yang diambil dan diteruskan selama operasi. - output_rows adalah kardinalitas output (simpul) dan menunjukkan jumlah baris output. <location>: Simpul atau distribusi tempat operasi akan terjadi. Opsinya adalah: "Control", "ComputeNode", "AllComputeNodes", "AllDistributions", "SubsetDistributions", "Distribution", dan "SubsetNodes".<source_statement>: Data sumber untuk pemindahan acak.<destination_table>: Tabel sementara internal tempat data akan dipindahkan.<shuffle_columns>: (Hanya berlaku untuk operasi SHUFFLE_MOVE). Satu atau beberapa kolom yang akan digunakan sebagai kolom distribusi untuk tabel sementara. |
<operation_cost cost="40" accumulative_cost="40" average_rowsize = "50" output_rows="100"/><location distribution="AllDistributions" /><source_statement type="statement">SELECT [TableAlias_3b77ee1d8ccf4a94ba644118b355db9d].[dist_date] FROM [qatest].[dbo].[flyers] [TableAlias_3b77ee1d8ccf4a94ba644118b355db9d] </source_statement><destination_table>Q_[TEMP_ID_259]_[PARTITION_ID]</destination_table><shuffle_columns>dist_date;</shuffle_columns><shuffle_columns>Email;Date;</shuffle_columns> |
| MetaDataCreate_Operation | <source_table>: Tabel sumber untuk operasi.<destination_table>: Tabel tujuan untuk operasi. |
<source_table>databases</source_table><destination_table>MetaDataCreateLandingTempTable</destination_table> |
| AKTIF | <location>: Lihat <location>.<sql_operation>: Mengidentifikasi perintah SQL yang akan dilakukan pada node. |
<location permanent="false" distribution="AllDistributions">Compute</location><sql_operation type="statement">CREATE TABLE [tempdb].[dbo]. [Q_[TEMP_ID_259]]_ [PARTITION_ID]]]([dist_date] DATE) WITH (DISTRIBUTION = HASH([dist_date]),) </sql_operation> |
| RemoteOnOperation | <DestinationCatalog>: Katalog tujuan.<DestinationSchema>: Skema tujuan di DestinationCatalog.<DestinationTableName>: Nama tabel tujuan atau "TableName".<DestinationDatasource>: Nama sumber data tujuan.<Username> dan <Password>: Bidang-bidang ini menunjukkan bahwa nama pengguna dan kata sandi untuk tujuan mungkin diperlukan.<CreateStatement>: Pernyataan pembuatan tabel untuk database tujuan. |
<DestinationCatalog>master</DestinationCatalog><DestinationSchema>dbo</DestinationSchema><DestinationTableName>TableName</DestinationTableName><DestinationDatasource>DestDataSource</DestinationDatasource><Username>...</Username><Password>...</Password><CreateStatement>CREATE TABLE [master].[dbo].[TableName] ([col1] BIGINT) ON [PRIMARY] WITH(DATA_COMPRESSION=PAGE);</CreateStatement> |
| KEMBALI | <resultset>: Pengidentifikasi untuk kumpulan hasil. |
<resultset>RS_19</resultset> |
| RND_ID | <identifier>: Pengidentifikasi untuk objek yang dibuat. |
<identifier>TEMP_ID_260</identifier> |
Batasan dan Pembatasan
EXPLAIN hanya dapat diterapkan ke kueri yang dapat dioptimalkan , yang merupakan kueri yang dapat ditingkatkan atau dimodifikasi berdasarkan hasil perintah EXPLAIN . Perintah EXPLAIN yang didukung disediakan di bagian sebelumnya. Mencoba menggunakan EXPLAIN dengan jenis kueri yang tidak didukung akan mengembalikan kesalahan atau menggemakan kueri.
EXPLAIN tidak didukung dalam transaksi pengguna.
Contoh
Contoh berikut menunjukkan perintah EXPLAIN yang dijalankan pada pernyataan SELECT , dan hasil XML.
Mengirimkan pernyataan EXPLAIN
Perintah yang dikirimkan untuk contoh ini adalah:
-- Uses AdventureWorks
EXPLAIN
SELECT CAST (AVG(YearlyIncome) AS int) AS AverageIncome,
CAST(AVG(FIS.SalesAmount) AS int) AS AverageSales,
G.StateProvinceName, T.SalesTerritoryGroup
FROM dbo.DimGeography AS G
JOIN dbo.DimSalesTerritory AS T
ON G.SalesTerritoryKey = T.SalesTerritoryKey
JOIN dbo.DimCustomer AS C
ON G.GeographyKey = C.GeographyKey
JOIN dbo.FactInternetSales AS FIS
ON C.CustomerKey = FIS.CustomerKey
WHERE T.SalesTerritoryGroup IN ('North America', 'Pacific')
AND Gender = 'F'
GROUP BY G.StateProvinceName, T.SalesTerritoryGroup
ORDER BY AVG(YearlyIncome) DESC;
GO
Setelah menjalankan pernyataan menggunakan opsi EXPLAIN , tab pesan menyajikan satu baris berjudul menjelaskan, dan dimulai dengan teks \<?xml version="1.0" encoding="utf-8"?> XML Pilih XML untuk membuka seluruh teks di jendela XML. Untuk lebih memahami komentar berikut, Anda harus mengaktifkan tampilan nomor baris di SSDT.
Mengaktifkan nomor baris
Dengan output muncul di tab penjelasan SSDT, pada menu TOOLS , pilih Opsi.
Perluas bagian Editor Teks , perluas XML, lalu pilih Umum.
Di area Tampilan , centang Nomor baris.
PilihOK.
Contoh output EXPLAIN
Hasil XML dari perintah EXPLAIN dengan nomor baris diaktifkan adalah:
1 \<?xml version="1.0" encoding="utf-8"?>
2 <dsql_query>
3 <sql>SELECT CAST (AVG(YearlyIncome) AS int) AS AverageIncome,
4 CAST(AVG(FIS.SalesAmount) AS int) AS AverageSales,
5 G.StateProvinceName, T.SalesTerritoryGroup
6 FROM dbo.DimGeography AS G
7 JOIN dbo.DimSalesTerritory AS T
8 ON G.SalesTerritoryKey = T.SalesTerritoryKey
9 JOIN dbo.DimCustomer AS C
10 ON G.GeographyKey = C.GeographyKey
11 JOIN dbo.FactInternetSales AS FIS
12 ON C.CustomerKey = FIS.CustomerKey
13 WHERE T.SalesTerritoryGroup IN ('North America', 'Pacific')
14 AND Gender = 'F'
15 GROUP BY G.StateProvinceName, T.SalesTerritoryGroup
16 ORDER BY AVG(YearlyIncome) DESC</sql>
17 <dsql_operations total_cost="0.926237696" total_number_operations="9">
18 <dsql_operation operation_type="RND_ID">
19 <identifier>TEMP_ID_16893</identifier>
20 </dsql_operation>
21 <dsql_operation operation_type="ON">
22 <location permanent="false" distribution="AllComputeNodes" />
23 <sql_operations>
24 <sql_operation type="statement">CREATE TABLE [tempdb].[dbo].[TEMP_ID_16893] ([CustomerKey] INT NOT NULL, [GeographyKey] INT, [YearlyIncome] MONEY ) WITH(DATA_COMPRESSION=PAGE);</sql_operation>
25 </sql_operations>
26 </dsql_operation>
27 <dsql_operation operation_type="BROADCAST_MOVE">
28 <operation_cost cost="0.121431552" accumulative_cost="0.121431552" average_rowsize="16" output_rows="31.6228" />
29 <source_statement>SELECT [T1_1].[CustomerKey] AS [CustomerKey],
30 [T1_1].[GeographyKey] AS [GeographyKey],
31 [T1_1].[YearlyIncome] AS [YearlyIncome]
32 FROM (SELECT [T2_1].[CustomerKey] AS [CustomerKey],
33 [T2_1].[GeographyKey] AS [GeographyKey],
34 [T2_1].[YearlyIncome] AS [YearlyIncome]
35 FROM [AdventureWorksPDW2012].[dbo].[DimCustomer] AS T2_1
36 WHERE ([T2_1].[Gender] = CAST (N'F' COLLATE Latin1_General_100_CI_AS_KS_WS AS NVARCHAR (1)) COLLATE Latin1_General_100_CI_AS_KS_WS)) AS T1_1</source_statement>
37 <destination_table>[TEMP_ID_16893]</destination_table>
38 </dsql_operation>
39 <dsql_operation operation_type="RND_ID">
40 <identifier>TEMP_ID_16894</identifier>
41 </dsql_operation>
42 <dsql_operation operation_type="ON">
43 <location permanent="false" distribution="AllDistributions" />
44 <sql_operations>
45 <sql_operation type="statement">CREATE TABLE [tempdb].[dbo].[TEMP_ID_16894] ([StateProvinceName] NVARCHAR(50) COLLATE Latin1_General_100_CI_AS_KS_WS, [SalesTerritoryGroup] NVARCHAR(50) COLLATE Latin1_General_100_CI_AS_KS_WS NOT NULL, [col] BIGINT, [col1] MONEY NOT NULL, [col2] BIGINT, [col3] MONEY NOT NULL ) WITH(DATA_COMPRESSION=PAGE);</sql_operation>
46 </sql_operations>
47 </dsql_operation>
48 <dsql_operation operation_type="SHUFFLE_MOVE">
49 <operation_cost cost="0.804806144" accumulative_cost="0.926237696" average_rowsize="232" output_rows="108.406" />
50 <source_statement>SELECT [T1_1].[StateProvinceName] AS [StateProvinceName],
51 [T1_1].[SalesTerritoryGroup] AS [SalesTerritoryGroup],
52 [T1_1].[col2] AS [col],
53 [T1_1].[col] AS [col1],
54 [T1_1].[col3] AS [col2],
55 [T1_1].[col1] AS [col3]
56 FROM (SELECT ISNULL([T2_1].[col1], CONVERT (MONEY, 0.00, 0)) AS [col],
57 ISNULL([T2_1].[col3], CONVERT (MONEY, 0.00, 0)) AS [col1],
58 [T2_1].[StateProvinceName] AS [StateProvinceName],
59 [T2_1].[SalesTerritoryGroup] AS [SalesTerritoryGroup],
60 [T2_1].[col] AS [col2],
61 [T2_1].[col2] AS [col3]
62 FROM (SELECT COUNT_BIG([T3_2].[YearlyIncome]) AS [col],
63 SUM([T3_2].[YearlyIncome]) AS [col1],
64 COUNT_BIG(CAST ((0) AS INT)) AS [col2],
65 SUM([T3_2].[SalesAmount]) AS [col3],
66 [T3_2].[StateProvinceName] AS [StateProvinceName],
67 [T3_1].[SalesTerritoryGroup] AS [SalesTerritoryGroup]
68 FROM (SELECT [T4_1].[SalesTerritoryKey] AS [SalesTerritoryKey],
69 [T4_1].[SalesTerritoryGroup] AS [SalesTerritoryGroup]
70 FROM [AdventureWorksPDW2012].[dbo].[DimSalesTerritory] AS T4_1
71 WHERE (([T4_1].[SalesTerritoryGroup] = CAST (N'North America' COLLATE Latin1_General_100_CI_AS_KS_WS AS NVARCHAR (13)) COLLATE Latin1_General_100_CI_AS_KS_WS)
72 OR ([T4_1].[SalesTerritoryGroup] = CAST (N'Pacific' COLLATE Latin1_General_100_CI_AS_KS_WS AS NVARCHAR (7)) COLLATE Latin1_General_100_CI_AS_KS_WS))) AS T3_1
73 INNER JOIN
74 (SELECT [T4_1].[SalesTerritoryKey] AS [SalesTerritoryKey],
75 [T4_2].[YearlyIncome] AS [YearlyIncome],
76 [T4_2].[SalesAmount] AS [SalesAmount],
77 [T4_1].[StateProvinceName] AS [StateProvinceName]
78 FROM [AdventureWorksPDW2012].[dbo].[DimGeography] AS T4_1
79 INNER JOIN
80 (SELECT [T5_2].[GeographyKey] AS [GeographyKey],
81 [T5_2].[YearlyIncome] AS [YearlyIncome],
82 [T5_1].[SalesAmount] AS [SalesAmount]
83 FROM [AdventureWorksPDW2012].[dbo].[FactInternetSales] AS T5_1
84 INNER JOIN
85 [tempdb].[dbo].[TEMP_ID_16893] AS T5_2
86 ON ([T5_1].[CustomerKey] = [T5_2].[CustomerKey])) AS T4_2
87 ON ([T4_2].[GeographyKey] = [T4_1].[GeographyKey])) AS T3_2
88 ON ([T3_1].[SalesTerritoryKey] = [T3_2].[SalesTerritoryKey])
89 GROUP BY [T3_2].[StateProvinceName], [T3_1].[SalesTerritoryGroup]) AS T2_1) AS T1_1</source_statement>
90 <destination_table>[TEMP_ID_16894]</destination_table>
91 <shuffle_columns>StateProvinceName;</shuffle_columns>
92 </dsql_operation>
93 <dsql_operation operation_type="ON">
94 <location permanent="false" distribution="AllComputeNodes" />
95 <sql_operations>
96 <sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_16893]</sql_operation>
97 </sql_operations>
98 </dsql_operation>
99 <dsql_operation operation_type="RETURN">
100 <location distribution="AllDistributions" />
101 <select>SELECT [T1_1].[col] AS [col],
102 [T1_1].[col1] AS [col1],
103 [T1_1].[StateProvinceName] AS [StateProvinceName],
104 [T1_1].[SalesTerritoryGroup] AS [SalesTerritoryGroup],
105 [T1_1].[col2] AS [col2]
106 FROM (SELECT CONVERT (INT, [T2_1].[col], 0) AS [col],
107 CONVERT (INT, [T2_1].[col1], 0) AS [col1],
108 [T2_1].[StateProvinceName] AS [StateProvinceName],
109 [T2_1].[SalesTerritoryGroup] AS [SalesTerritoryGroup],
110 [T2_1].[col] AS [col2]
111 FROM (SELECT CASE
112 WHEN ([T3_1].[col] = CAST ((0) AS BIGINT)) THEN CAST (NULL AS MONEY)
113 ELSE ([T3_1].[col1] / CONVERT (MONEY, [T3_1].[col], 0))
114 END AS [col],
115 CASE
116 WHEN ([T3_1].[col2] = CAST ((0) AS BIGINT)) THEN CAST (NULL AS MONEY)
117 ELSE ([T3_1].[col3] / CONVERT (MONEY, [T3_1].[col2], 0))
118 END AS [col1],
119 [T3_1].[StateProvinceName] AS [StateProvinceName],
120 [T3_1].[SalesTerritoryGroup] AS [SalesTerritoryGroup]
121 FROM (SELECT ISNULL([T4_1].[col], CONVERT (BIGINT, 0, 0)) AS [col],
122 ISNULL([T4_1].[col1], CONVERT (MONEY, 0.00, 0)) AS [col1],
123 ISNULL([T4_1].[col2], CONVERT (BIGINT, 0, 0)) AS [col2],
124 ISNULL([T4_1].[col3], CONVERT (MONEY, 0.00, 0)) AS [col3],
125 [T4_1].[StateProvinceName] AS [StateProvinceName],
126 [T4_1].[SalesTerritoryGroup] AS [SalesTerritoryGroup]
127 FROM (SELECT SUM([T5_1].[col]) AS [col],
128 SUM([T5_1].[col1]) AS [col1],
129 SUM([T5_1].[col2]) AS [col2],
130 SUM([T5_1].[col3]) AS [col3],
131 [T5_1].[StateProvinceName] AS [StateProvinceName],
132 [T5_1].[SalesTerritoryGroup] AS [SalesTerritoryGroup]
133 FROM [tempdb].[dbo].[TEMP_ID_16894] AS T5_1
134 GROUP BY [T5_1].[StateProvinceName], [T5_1].[SalesTerritoryGroup]) AS T4_1) AS T3_1) AS T2_1) AS T1_1
135 ORDER BY [T1_1].[col2] DESC</select>
136 </dsql_operation>
137 <dsql_operation operation_type="ON">
138 <location permanent="false" distribution="AllDistributions" />
139 <sql_operations>
140 <sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_16894]</sql_operation>
141 </sql_operations>
142 </dsql_operation>
143 </dsql_operations>
144 </dsql_query>
Arti dari output EXPLAIN
Output dalam blok kode sebelumnya berisi 144 baris bernomor. Output Anda dari kueri ini mungkin sedikit berbeda. Daftar berikut ini menjelaskan bagian yang signifikan.
Baris 3 hingga 16 memberikan deskripsi kueri yang sedang dianalisis.
Baris 17, menentukan bahwa jumlah total operasi adalah 9. Anda dapat menemukan awal setiap operasi, dengan mencari kata-kata dsql_operation.
Baris 18 memulai operasi 1. Baris 18 dan 19 menunjukkan bahwa operasi RND_ID akan membuat nomor ID acak yang akan digunakan untuk deskripsi objek. Objek yang dijelaskan dalam output sampel TEMP_ID_16893. Nomor Anda akan berbeda.
Baris 20 memulai operasi 2. Baris 21 hingga 25: Pada semua simpul komputasi, buat tabel sementara bernama
TEMP_ID_16893.Baris 26 memulai operasi 3. Garis 27 hingga 37: Memindahkan data ke
TEMP_ID_16893dengan menggunakan pemindahan siaran. Kueri yang dikirim ke setiap simpul komputasi disediakan. Baris 37 menentukan tabel tujuan adalahTEMP_ID_16893.Baris 38 memulai operasi 4. Garis 39 hingga 40: Buat ID acak untuk tabel. TEMP_ID_16894 adalah nomor ID dalam contoh. Nomor Anda akan berbeda.
Baris 41 memulai operasi 5. Baris 42 hingga 46: Pada semua simpul, buat tabel sementara bernama
TEMP_ID_16894.Baris 47 memulai operasi 6. Garis 48 hingga 91: Memindahkan data dari berbagai tabel (termasuk
TEMP_ID_16893) ke tabelTEMP_ID_16893, dengan menggunakan operasi pemindahan acak. Kueri yang dikirim ke setiap simpul komputasi disediakan. Baris 90 menentukan tabel tujuan sebagaiTEMP_ID_16894. Baris 91 menentukan kolom.Baris 92 memulai operasi 7. Garis 93 hingga 97: Pada semua simpul komputasi, hilangkan tabel
TEMP_ID_16893sementara .Baris 98 memulai operasi 8. Baris 99 hingga 135: Mengembalikan hasil ke klien. Menggunakan kueri yang disediakan untuk mendapatkan hasilnya.
Baris 136 memulai operasi 9. Garis 137 hingga 140: Pada semua simpul, hilangkan tabel
TEMP_ID_16894sementara .
Mengirimkan pernyataan EXPLAIN WITH_RECOMMENDATIONS
EXPLAIN WITH_RECOMMENDATIONS
select count(*)
from ((select distinct c_last_name, c_first_name, d_date
from store_sales, date_dim, customer
where store_sales.ss_sold_date_sk = date_dim.d_date_sk
and store_sales.ss_customer_sk = customer.c_customer_sk
and d_month_seq between 1194 and 1194+11)
except
(select distinct c_last_name, c_first_name, d_date
from catalog_sales, date_dim, customer
where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk
and catalog_sales.cs_bill_customer_sk = customer.c_customer_sk
and d_month_seq between 1194 and 1194+11)
) top_customers
Contoh output untuk WITH_RECOMMENDATIONS EXPLAIN
Contoh output berikut mencakup pembuatan tampilan materialisasi yang direkomendasikan yang disebut View1.
<?xml version="1.0" encoding="utf-8"?>
<dsql_query number_nodes="1" number_distributions="8" number_distributions_per_node="8">
<sql>select count(*)
from ((select distinct c_last_name, c_first_name, d_date
from store_sales, date_dim, customer
where store_sales.ss_sold_date_sk = date_dim.d_date_sk
and store_sales.ss_customer_sk = customer.c_customer_sk
and d_month_seq between 1194 and 1194+11)
except
(select distinct c_last_name, c_first_name, d_date
from catalog_sales, date_dim, customer
where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk
and catalog_sales.cs_bill_customer_sk = customer.c_customer_sk
and d_month_seq between 1194 and 1194+11)
) top_customers</sql>
<materialized_view_candidates>
<materialized_view_candidates with_constants="False">CREATE MATERIALIZED VIEW View1 WITH (DISTRIBUTION = HASH([Expr0])) AS
SELECT [tpcds10].[dbo].[customer].[c_last_name] AS [Expr0],
[tpcds10].[dbo].[customer].[c_first_name] AS [Expr1],
[tpcds10].[dbo].[date_dim].[d_date] AS [Expr2],
[tpcds10].[dbo].[date_dim].[d_month_seq] AS [Expr3]
FROM [dbo].[store_sales],
[dbo].[date_dim],
[dbo].[customer]
WHERE ([tpcds10].[dbo].[store_sales].[ss_customer_sk]=[tpcds10].[dbo].[customer].[c_customer_sk])
AND ([tpcds10].[dbo].[store_sales].[ss_sold_date_sk]=[tpcds10].[dbo].[date_dim].[d_date_sk])
GROUP BY [tpcds10].[dbo].[customer].[c_last_name],
[tpcds10].[dbo].[customer].[c_first_name],
[tpcds10].[dbo].[date_dim].[d_date],
[tpcds10].[dbo].[date_dim].[d_month_seq]</materialized_view_candidates>
<materialized_view_candidates with_constants="False">CREATE MATERIALIZED VIEW View2 WITH (DISTRIBUTION = HASH([Expr0])) AS
SELECT [tpcds10].[dbo].[customer].[c_last_name] AS [Expr0],
[tpcds10].[dbo].[customer].[c_first_name] AS [Expr1],
[tpcds10].[dbo].[date_dim].[d_date] AS [Expr2],
[tpcds10].[dbo].[date_dim].[d_month_seq] AS [Expr3]
FROM [dbo].[catalog_sales],
[dbo].[date_dim],
[dbo].[customer]
WHERE ([tpcds10].[dbo].[catalog_sales].[cs_bill_customer_sk]=[tpcds10].[dbo].[customer].[c_customer_sk])
AND ([tpcds10].[dbo].[catalog_sales].[cs_sold_date_sk]=[tpcds10].[dbo].[date_dim].[d_date_sk])
GROUP BY [tpcds10].[dbo].[customer].[c_last_name],
[tpcds10].[dbo].[customer].[c_first_name],
[tpcds10].[dbo].[date_dim].[d_date],
[tpcds10].[dbo].[date_dim].[d_month_seq]</materialized_view_candidates>
<materialized_view_candidates with_constants="True">CREATE MATERIALIZED VIEW View3 WITH (DISTRIBUTION = HASH([Expr0])) AS
SELECT [tpcds10].[dbo].[customer].[c_last_name] AS [Expr0],
[tpcds10].[dbo].[customer].[c_first_name] AS [Expr1],
[tpcds10].[dbo].[date_dim].[d_date] AS [Expr2]
FROM [dbo].[store_sales],
[dbo].[date_dim],
[dbo].[customer]
WHERE ([tpcds10].[dbo].[store_sales].[ss_customer_sk]=[tpcds10].[dbo].[customer].[c_customer_sk])
AND ([tpcds10].[dbo].[store_sales].[ss_sold_date_sk]=[tpcds10].[dbo].[date_dim].[d_date_sk])
AND ([tpcds10].[dbo].[date_dim].[d_month_seq]>=(1194))
AND ([tpcds10].[dbo].[date_dim].[d_month_seq]<=(1205))
GROUP BY [tpcds10].[dbo].[customer].[c_last_name],
[tpcds10].[dbo].[customer].[c_first_name],
[tpcds10].[dbo].[date_dim].[d_date]</materialized_view_candidates>
<materialized_view_candidates with_constants="True">CREATE MATERIALIZED VIEW View4 WITH (DISTRIBUTION = HASH([Expr0])) AS
SELECT [tpcds10].[dbo].[customer].[c_last_name] AS [Expr0],
[tpcds10].[dbo].[customer].[c_first_name] AS [Expr1],
[tpcds10].[dbo].[date_dim].[d_date] AS [Expr2]
FROM [dbo].[catalog_sales],
[dbo].[date_dim],
[dbo].[customer]
WHERE ([tpcds10].[dbo].[catalog_sales].[cs_bill_customer_sk]=[tpcds10].[dbo].[customer].[c_customer_sk])
AND ([tpcds10].[dbo].[catalog_sales].[cs_sold_date_sk]=[tpcds10].[dbo].[date_dim].[d_date_sk])
AND ([tpcds10].[dbo].[date_dim].[d_month_seq]>=(1194))
AND ([tpcds10].[dbo].[date_dim].[d_month_seq]<=(1205))
GROUP BY [tpcds10].[dbo].[customer].[c_last_name],
[tpcds10].[dbo].[customer].[c_first_name],
[tpcds10].[dbo].[date_dim].[d_date]</materialized_view_candidates>
</materialized_view_candidates>
<dsql_operations total_cost="3472197.35650704" total_number_operations="28">
<dsql_operation operation_type="RND_ID">
<identifier>TEMP_ID_1</identifier>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllComputeNodes" />
<sql_operations>
<sql_operation type="statement">CREATE TABLE [tempdb].[dbo].[TEMP_ID_1] ([c_customer_sk] INT NOT NULL, [c_first_name] CHAR(20) COLLATE SQL_Latin1_General_CP1_CI_AS, [c_last_name] CHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS ) WITH(DATA_COMPRESSION=PAGE);</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="BROADCAST_MOVE">
<operation_cost cost="842400" accumulative_cost="842400" average_rowsize="54" output_rows="65000000" GroupNumber="44" />
<source_statement>SELECT [T1_1].[c_customer_sk] AS [c_customer_sk],
[T1_1].[c_first_name] AS [c_first_name],
[T1_1].[c_last_name] AS [c_last_name]
FROM [tpcds10].[dbo].[customer] AS T1_1</source_statement>
<destination_table>[TEMP_ID_1]</destination_table>
</dsql_operation>
<dsql_operation operation_type="RND_ID">
<identifier>TEMP_ID_2</identifier>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllComputeNodes" />
<sql_operations>
<sql_operation type="statement">CREATE TABLE [tempdb].[dbo].[TEMP_ID_2] ([d_date_sk] INT NOT NULL, [d_date] DATE ) WITH(DATA_COMPRESSION=PAGE);</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="BROADCAST_MOVE">
<operation_cost cost="0.62729352" accumulative_cost="842400.62729352" average_rowsize="7" output_rows="373.389" GroupNumber="43" />
<source_statement>SELECT [T1_1].[d_date_sk] AS [d_date_sk],
[T1_1].[d_date] AS [d_date]
FROM (SELECT [T2_1].[d_date_sk] AS [d_date_sk],
[T2_1].[d_date] AS [d_date]
FROM [tpcds10].[dbo].[date_dim] AS T2_1
WHERE (([T2_1].[d_month_seq] >= CAST ((1194) AS INT))
AND ([T2_1].[d_month_seq] <= CAST ((1205) AS INT)))) AS T1_1</source_statement>
<destination_table>[TEMP_ID_2]</destination_table>
</dsql_operation>
<dsql_operation operation_type="RND_ID">
<identifier>TEMP_ID_3</identifier>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllDistributions" />
<sql_operations>
<sql_operation type="statement">CREATE TABLE [tempdb].[dbo].[TEMP_ID_3] ([cs_bill_customer_sk] INT, [d_date] DATE ) WITH(DATA_COMPRESSION=PAGE);</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="SHUFFLE_MOVE">
<operation_cost cost="610362.9" accumulative_cost="1452763.52729352" average_rowsize="7" output_rows="2906490000" GroupNumber="57" />
<source_statement>SELECT [T1_1].[cs_bill_customer_sk] AS [cs_bill_customer_sk],
[T1_1].[d_date] AS [d_date]
FROM (SELECT [T2_2].[cs_bill_customer_sk] AS [cs_bill_customer_sk],
[T2_1].[d_date] AS [d_date]
FROM [tempdb].[dbo].[TEMP_ID_2] AS T2_1
INNER JOIN
[tpcds10].[dbo].[catalog_sales] AS T2_2
ON ([T2_2].[cs_sold_date_sk] = [T2_1].[d_date_sk])) AS T1_1</source_statement>
<destination_table>[TEMP_ID_3]</destination_table>
<shuffle_columns>d_date;</shuffle_columns>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllComputeNodes" />
<sql_operations>
<sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_2]</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="RND_ID">
<identifier>TEMP_ID_4</identifier>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllComputeNodes" />
<sql_operations>
<sql_operation type="statement">CREATE TABLE [tempdb].[dbo].[TEMP_ID_4] ([c_customer_sk] INT NOT NULL, [c_first_name] CHAR(20) COLLATE SQL_Latin1_General_CP1_CI_AS, [c_last_name] CHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS ) WITH(DATA_COMPRESSION=PAGE);</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="BROADCAST_MOVE">
<operation_cost cost="842400" accumulative_cost="2295163.52729352" average_rowsize="54" output_rows="65000000" GroupNumber="36" />
<source_statement>SELECT [T1_1].[c_customer_sk] AS [c_customer_sk],
[T1_1].[c_first_name] AS [c_first_name],
[T1_1].[c_last_name] AS [c_last_name]
FROM [tpcds10].[dbo].[customer] AS T1_1</source_statement>
<destination_table>[TEMP_ID_4]</destination_table>
</dsql_operation>
<dsql_operation operation_type="RND_ID">
<identifier>TEMP_ID_5</identifier>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllComputeNodes" />
<sql_operations>
<sql_operation type="statement">CREATE TABLE [tempdb].[dbo].[TEMP_ID_5] ([d_date_sk] INT NOT NULL, [d_date] DATE ) WITH(DATA_COMPRESSION=PAGE);</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="BROADCAST_MOVE">
<operation_cost cost="0.62729352" accumulative_cost="2295164.15458704" average_rowsize="7" output_rows="373.389" GroupNumber="35" />
<source_statement>SELECT [T1_1].[d_date_sk] AS [d_date_sk],
[T1_1].[d_date] AS [d_date]
FROM (SELECT [T2_1].[d_date_sk] AS [d_date_sk],
[T2_1].[d_date] AS [d_date]
FROM [tpcds10].[dbo].[date_dim] AS T2_1
WHERE (([T2_1].[d_month_seq] >= CAST ((1194) AS INT))
AND ([T2_1].[d_month_seq] <= CAST ((1205) AS INT)))) AS T1_1</source_statement>
<destination_table>[TEMP_ID_5]</destination_table>
</dsql_operation>
<dsql_operation operation_type="RND_ID">
<identifier>TEMP_ID_6</identifier>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllDistributions" />
<sql_operations>
<sql_operation type="statement">CREATE TABLE [tempdb].[dbo].[TEMP_ID_6] ([ss_customer_sk] INT, [d_date] DATE ) WITH(DATA_COMPRESSION=PAGE);</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="SHUFFLE_MOVE">
<operation_cost cost="1177033.2" accumulative_cost="3472197.35458704" average_rowsize="7" output_rows="5604920000" GroupNumber="54" />
<source_statement>SELECT [T1_1].[ss_customer_sk] AS [ss_customer_sk],
[T1_1].[d_date] AS [d_date]
FROM (SELECT [T2_2].[ss_customer_sk] AS [ss_customer_sk],
[T2_1].[d_date] AS [d_date]
FROM [tempdb].[dbo].[TEMP_ID_5] AS T2_1
INNER JOIN
[tpcds10].[dbo].[store_sales] AS T2_2
ON ([T2_2].[ss_sold_date_sk] = [T2_1].[d_date_sk])) AS T1_1</source_statement>
<destination_table>[TEMP_ID_6]</destination_table>
<shuffle_columns>d_date;</shuffle_columns>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllComputeNodes" />
<sql_operations>
<sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_5]</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="Control" />
<sql_operations>
<sql_operation type="statement">CREATE TABLE [tempdb].[QTables].[QTable_87367172aa554f06b73cf3ed97e5b985] ([col] BIGINT ) WITH(DATA_COMPRESSION=PAGE);</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="PARTITION_MOVE">
<operation_cost cost="0.00192" accumulative_cost="3472197.35650704" average_rowsize="8" output_rows="1" GroupNumber="66" />
<location distribution="AllDistributions" />
<source_statement>SELECT [T1_1].[col] AS [col]
FROM (SELECT COUNT_BIG(CAST ((0) AS INT)) AS [col]
FROM (SELECT 0 AS [col]
FROM [tempdb].[dbo].[TEMP_ID_4] AS T3_1
INNER JOIN
[tempdb].[dbo].[TEMP_ID_6] AS T3_2
ON ([T3_2].[ss_customer_sk] = [T3_1].[c_customer_sk])
GROUP BY [T3_1].[c_last_name], [T3_1].[c_first_name], [T3_2].[d_date]
HAVING NOT EXISTS (SELECT 1 AS C1
FROM [tempdb].[dbo].[TEMP_ID_1] AS T4_1
INNER JOIN
[tempdb].[dbo].[TEMP_ID_3] AS T4_2
ON ([T4_2].[cs_bill_customer_sk] = [T4_1].[c_customer_sk])
GROUP BY [T4_1].[c_last_name], [T4_1].[c_first_name], [T4_2].[d_date]
HAVING (([T3_1].[c_last_name] = [T4_1].[c_last_name]
OR ([T3_1].[c_last_name] IS NULL
AND [T4_1].[c_last_name] IS NULL))
AND ([T3_1].[c_first_name] = [T4_1].[c_first_name]
OR ([T3_1].[c_first_name] IS NULL
AND [T4_1].[c_first_name] IS NULL))
AND ([T3_2].[d_date] = [T4_2].[d_date]
OR ([T3_2].[d_date] IS NULL
AND [T4_2].[d_date] IS NULL))))) AS T2_1
GROUP BY [T2_1].[col]) AS T1_1</source_statement>
<destination>Control</destination>
<destination_table>[QTable_87367172aa554f06b73cf3ed97e5b985]</destination_table>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllDistributions" />
<sql_operations>
<sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_6]</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllComputeNodes" />
<sql_operations>
<sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_4]</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllDistributions" />
<sql_operations>
<sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_3]</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllComputeNodes" />
<sql_operations>
<sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_1]</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="RETURN">
<location distribution="Control" />
<select>SELECT [T1_1].[col] AS [col]
FROM (SELECT CONVERT (INT, [T2_1].[col], 0) AS [col]
FROM (SELECT ISNULL([T3_1].[col], CONVERT (BIGINT, 0, 0)) AS [col]
FROM (SELECT SUM([T4_1].[col]) AS [col]
FROM [tempdb].[QTables].[QTable_87367172aa554f06b73cf3ed97e5b985] AS T4_1) AS T3_1) AS T2_1) AS T1_1</select>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="Control" />
<sql_operations>
<sql_operation type="statement">DROP TABLE [tempdb].[QTables].[QTable_87367172aa554f06b73cf3ed97e5b985]</sql_operation>
</sql_operations>
</dsql_operation>
</dsql_operations>
</dsql_query>
Lihat juga
- sys.pdw_materialized_view_column_distribution_properties (T-SQL)
- sys.pdw_materialized_view_distribution_properties (T-SQL)
- sys.pdw_materialized_view_mappings (T-SQL)
- Azure Synapse Analytics dan Tampilan Katalog Gudang Data Paralel
- Tampilan sistem yang didukung di Azure Synapse Analytics
- Pernyataan T-SQL yang didukung di Azure Synapse Analytics
Langkah berikutnya
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk