数据库_vertica_查询数据库每个表所占的硬盘空间

红太狼 2022-05-23 10:58 464阅读 0赞

vertica查看每个表的多占磁盘空间  
转载自：[http://www.it610.com/article/588033.htm][http_www.it610.com_article_588033.htm]  
有许多的办法查询Vertica数据库所占的硬盘空间.

Vertica有两种测量方式，分别是原始数据和压缩数据。你需要知道哪种类型是你需要的。

比如Vertica的许可是基于原始数据的。

1，查询Schema为test的压缩数据大小和原始数据大小。

SELECT /*+(estimated_raw_size)*/ pj.anchor_table_schema, pj.used_compressed_gb, pj.used_compressed_gb * la.ratio AS raw_estimate_gb FROM (SELECT ps.anchor_table_schema, SUM(used_bytes) / ( 1024^3 ) AS used_compressed_gb FROM v_catalog.projections p JOIN v_monitor.projection_storage ps ON ps.projection_id = p.projection_id WHERE p.is_super_projection = 't' and anchor_table_schema = 'test' GROUP BY ps.anchor_table_schema) pj CROSS JOIN (SELECT (SELECT database_size_bytes FROM v_catalog.license_audits ORDER BY audit_start_timestamp DESC LIMIT 1) / (SELECT SUM(used_bytes) FROM V_MONITOR.projection_storage) AS ratio) la ORDER BY pj.used_compressed_gb DESC;

2，查询许可证的信息：

SELECT GET_COMPLIANCE_STATUS();

3，查询每个表的压缩大小, schema为test的，去掉红色则为整个数据库的

SELECT /*+(compressed_table_size)*/ anchor_table_schema, anchor_table_name, SUM(used_bytes) / ( 1024^3 ) AS used_compressed_gb FROM v_monitor.projection_storage Where anchor_table_schema = 'test' GROUP BY anchor_table_schema, anchor_table_name ORDER BY SUM(used_bytes) DESC;

[http_www.it610.com_article_588033.htm]: http://www.it610.com/article/588033.htm