有许多的办法查询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://dba.stackexchange.com/questions/65114/how-do-i-get-the-size-of-a-vertica-database