如何查询Azure Dedicated SQL Pool/Synapse中物化视图的存储大小（MB/GB）

Oracle #

适用Oracle 11g及以上版本，查询语句如下：

SELECT 
    owner AS 所属用户,
    mview_name AS 物化视图名称,
    ROUND((bytes / 1024 / 1024), 2) AS 占用空间_MB,
    CASE WHEN bytes >= 1024*1024*1024 THEN ROUND((bytes / 1024 / 1024 / 1024), 2) ELSE NULL END AS 占用空间_GB
FROM 
    dba_segments ds
JOIN 
    dba_mviews dm ON ds.segment_name = dm.mview_name AND ds.owner = dm.owner
WHERE 
    ds.segment_type = 'MATERIALIZED VIEW'
ORDER BY 
    bytes DESC;

如果是普通用户查询自己名下的物化视图，把dba_segments换成user_segments、dba_mviews换成user_mviews，去掉owner字段的筛选和展示即可。

PostgreSQL #

适用PostgreSQL 9.3及以上版本，查询语句如下：

SELECT 
    schemaname AS 所属模式,
    matviewname AS 物化视图名称,
    ROUND((pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(matviewname)) / 1024.0 / 1024.0), 2) AS 占用空间_MB,
    CASE WHEN pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(matviewname)) >= 1024*1024*1024 THEN ROUND((pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(matviewname)) / 1024.0 / 1024.0 / 1024.0), 2) ELSE NULL END AS 占用空间_GB
FROM 
    pg_matviews
ORDER BY 
    pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(matviewname)) DESC;

pg_total_relation_size会统计物化视图本身+关联索引的总占用空间，如果只需要统计物化视图本身的大小，换成pg_relation_size即可。

MySQL 8.0+ #

MySQL从8.0版本开始支持物化视图，查询语句如下：

SELECT 
    TABLE_SCHEMA AS 所属数据库,
    TABLE_NAME AS 物化视图名称,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 占用空间_MB,
    CASE WHEN (DATA_LENGTH + INDEX_LENGTH) >= 1024*1024*1024 THEN ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) ELSE NULL END AS 占用空间_GB
FROM 
    information_schema.TABLES
WHERE 
    TABLE_TYPE = 'MATERIALIZED VIEW'
ORDER BY 
    (DATA_LENGTH + INDEX_LENGTH) DESC;

SQL Server #

适用SQL Server 2016及以上版本，查询语句如下：

SELECT 
    s.name AS 所属模式,
    mv.name AS 物化视图名称,
    ROUND(SUM(p.reserved_page_count) * 8.0 / 1024, 2) AS 占用空间_MB,
    CASE WHEN SUM(p.reserved_page_count) * 8.0 >= 1024*1024 THEN ROUND(SUM(p.reserved_page_count) * 8.0 / 1024 / 1024, 2) ELSE NULL END AS 占用空间_GB
FROM 
    sys.views mv
JOIN 
    sys.schemas s ON mv.schema_id = s.schema_id
JOIN 
    sys.dm_db_partition_stats p ON mv.object_id = p.object_id
WHERE 
    mv.is_ms_shipped = 0
    AND EXISTS (SELECT 1 FROM sys.indexes i WHERE i.object_id = mv.object_id AND i.type = 1) -- 筛选已构建聚集索引的物化视图
GROUP BY 
    s.name, mv.name
ORDER BY 
    SUM(p.reserved_page_count) DESC;

内容的提问来源于stack exchange，提问作者question.it